Linq to Objects - Performance

Question

When searching for best practice content when using LINQ I came across the following filter situation:

// Filtros
int idBairro = 8626;

first 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
                   });
}

2° 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 second way I have only one instance, but 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 use this information.

In the database was this way:

1° Way:

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

2° 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 the 1°way is the most performatic, but how can I make sure? and what other good practices I could apply in consultation?.

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

Answer 1

The two queries are not equivalent, but the difference between them is unfaithful, the second is just bringing an extra column (resulting in a bigger traffic).

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

In the most the EF is optimizing your query, as it understood that the LEFT JOIN is unnecessary and can be summarised as.:

from b in db.ERPCEPBairro
where b.idBairro == idBairro
select new TesteConsulta
{
    idCidade = b.idBairro
})