Why this optional parameters LINQ WHERE is not working?

Question

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 stranger part is:

datepart(year, evidencias0_.DataInclusao)=@P6 and datepart(month, evidencias0_.DataInclusao)=@P7

Why not this:

@P6=1 and @P7=1

Answer 1

It's strange of course, but nobody is perfect - the translation of LINQ expression tree to SQL is quite complicated subject, has to account many things and thus can miss some that are "obvious" for humans. So the translation is not always perfect, but as soon as it does not generate exceptions and does produce the correct result, it should be acceptable.

However you can easily help the translator to avoid redundant parameters and conditions (at least for top level IQueryable<T> like yours) by writing a custom conditional Where extension method 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 you could 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 @P4, @P5, @P6, @P7 parameters and strange conditions.