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.