Why are these optional LINQ WHERE parameters not working?
When MesAno is "" //string.Empty
DateTime dateAux;
int ano = 0;
int mes = 0;
if (MesAno.Trim() != "" && DateTime.TryParse("01/" + MesAno, out dateAux))
{
ano = dateAux.Year;
mes = dateAux.Month;
}
lista = session.Query<Evidencias>().Timeout(30)
.Where(m => m.Produto.Distribuidora.Id == myUser.Terceiro.Distribuidora.Id)
//.Where(m => MesAno != "" ? (m.DataInclusao.Year == ano && m.DataInclusao.Month == mes) : true)
.Where(m => Produto != "" ? m.Produto.CodigoProduto.Contains(Produto) : true)
.Where(m => Titular != "" ? m.NomeTitular.Contains(Titular) : true)
.Where(m => Instalacao != "" ? m.CodigoInstalacao.Contains(Instalacao) : true)
.Where(m => ano != 0 ? m.DataInclusao.Year == ano : true)
.Where(m => mes != 0 ? m.DataInclusao.Month == mes : true)
.OrderByDescending(m => m.DataInclusao).Take(3000).ToList();
Then the generated SQL WHERE is:
where produto1_.IdDistribuidora=@P2 and @P3=1 and @P4=1 and @P5=1 and datepart(year, evidencias0_.DataInclusao)=@P6 and datepart(month, evidencias0_.DataInclusao)=@P7 order by evidencias0_.DataInclusao desc',N'@P1 int,@P2 int,@P3 bit,@P4 bit,@P5 bit,@P6 int,@P7 int',3000,1,1,1,1,0,0
The unfamiliar part:
datepart(year, evidencias0_.DataInclusao)=@P6 and datepart(month, evidencias0_.DataInclusao)=@P7
Why not this:
@P6=1 and @P7=1
source to share
Strange, of course, but no one is perfect - translating a LINQ expression tree to SQL is quite a complex subject, has many things to consider, and therefore might overlook some that are "obvious" to people. Thus, the translation is not always perfect, but as soon as it does not throw exceptions and does not give the correct result, it should be acceptable.
However, you can easily help the translator avoid redundant parameters and conditions (at least for the top level IQueryable<T>
like yours) by writing a special conditional extension method Where
like this:
public static class QueryableExtensions
{
public static IQueryable<T> WhereIf<T>(this IQueryable<T> source, bool condition, Expression<Func<T, bool>> predicate)
{
return condition ? source.Where(predicate) : source;
}
}
so that you can use:
lista = session.Query<Evidencias>().Timeout(30)
.Where(m => m.Produto.Distribuidora.Id == myUser.Terceiro.Distribuidora.Id)
.WhereIf(Produto != "", m => m.Produto.CodigoProduto.Contains(Produto))
.WhereIf(Titular != "", m => m.NomeTitular.Contains(Titular))
.WhereIf(Instalacao != "", m => m.CodigoInstalacao.Contains(Instalacao))
.WhereIf(ano != 0, m => m.DataInclusao.Year == ano)
.WhereIf(mes != 0, m => m.DataInclusao.Month == mes)
.OrderByDescending(m => m.DataInclusao).Take(3000).ToList();
and the generated SQL will not have parameters @ P4, @ P5, @ P6, @ P7 and weird conditions.
source to share