Linq to Objects - Performance

0

When looking for good practice content when doing LINQ I came across the following filter situation:

// Filtros
int idBairro = 8626;

1st way:

IQueryable<TesteConsulta> queryCidade = (from c in db.ERPCEPCidade
                                         select new TesteConsulta
                                         {
                                             idCidade = c.idCidade
                                         });

if (idBairro != 0)
{
    var queryBairro = (from b in db.ERPCEPBairro
                       where b.idBairro == idBairro
                       select new
                       {
                           idBairro = b.idBairro,
                           idCidade = b.idCidade
                       });

    queryCidade = (from c in queryCidade
                   join b in queryBairro on c.idCidade equals b.idCidade
                   select new TesteConsulta
                   {
                       idCidade = c.idCidade
                   });
}

2nd way:

IQueryable<TesteConsulta> queryCidade2 = (from c in db.ERPCEPCidade
                                          join b in db.ERPCEPBairro on c.idCidade equals b.idCidade
                                          select new TesteConsulta
                                          {
                                              idCidade = c.idCidade,
                                              idBairro = b.idBairro
                                          });

if (idBairro != 0)
{
    queryCidade2 = queryCidade2.Where(x => x.idBairro == idBairro);
}

In the first way I have the instance of 2 Iqueryable objects, in the 2nd way I have an instance only, however I have more data in the query, consequently in the return object and worse, it is possible that the filter is empty and I do not even use information.

The database looks like this:

1st Way:

SELECT 
    1 AS [C1], 
    [Extent1].[idCidade] AS [idCidade]
FROM [dbo].[ERPCEPBairro] AS [Extent1]
WHERE ([Extent1].[idCidade] IS NOT NULL) AND 
    ([Extent1].[idBairro] = 8626)

2nd Way:

SELECT 
    1 AS [C1], 
    [Extent1].[idCidade] AS [idCidade], 
    [Extent1].[idBairro] AS [idBairro]
FROM [dbo].[ERPCEPBairro] AS [Extent1]
WHERE ([Extent1].[idCidade] IS NOT NULL) AND 
    ([Extent1].[idBairro] = 8626)

With this information, I imagine that the first way is the most performative, but how can I be sure? and what other good practices could I apply to the query?.

Remembering: this is an example, I know that in this example the difference may be small, but when I have queries that bring 40,000 records, the cost is greater.

    
asked by anonymous 04.04.2018 / 20:32

1 answer

1

The two queries are not equivalent, but the difference between them is infima, the second one is only bringing one more column (resulting in a larger traffic).

However, both queries must have the same execution plan, and except for the amount of traffic data, they must have the same cost.

No longer EF is optimizing your query, since it understands that LEFT JOIN is unnecessary and can be summarized as:

from b in db.ERPCEPBairro
where b.idBairro == idBairro
select new TesteConsulta
{
    idCidade = b.idBairro
})
    
04.04.2018 / 20:50