Search in lambda query with logical operators

Question

I have a query for a report, where some search fields are optional, except the date ranges.

However, when performing the query, the function always returns me values that do not match the parameters I passed at the time of execution of query

           public List<IQueryable> ObterNotaDetalhada(DateTime compInicial, DateTime compFinal, PorteContribuinte? codigoPorte, string query, int? itemServico, bool? simplesNacional)
           {
            var res = this.Servicos
            .Where(a => ((a.Competencia >= compInicial && a.Competencia <= compFinal) ||
                       (a.Prestador.Porte.Value == codigoPorte) || (a.Prestador.IsSimplesNacional == simplesNacional) ||
                         (a.ItemListaServico.Id == itemServico) ||
                         (a.Prestador.RazaoSocial.Contains(query) ||
                         a.Prestador.CpfCnpj.Contains(query))))
             .OrderByDescending(a => (a.id))
            .GroupBy(a => a.Prestador.CpfCnpj)
            .Select(g => new
            {
                CNPJ = g.Key,
                RazaoSocial = g.Select(x => x.Prestador.RazaoSocial),
                SimplesNacional = g.Select(x => x.Prestador.IsSimplesNacional),
                Porte = g.Select(x => x.Prestador.Porte),
                QtdNotasEmitidas = g.Count(),
                BaseDeCalculo = g.Select(x => x.BaseCalculo),
                ValorDeducoes = g.Select(x => x.ValorDeducoes),
                ValorServicos = g.Select(x => x.ValorServicos),
                ValorIss = g.Select(x => x.ValorInss),
                TotalBaseDeCalculo = g.Sum(x => x.BaseCalculo),
                TotalValorDeducoes = g.Sum(x => x.ValorDeducoes),
                TotalValorServicos = g.Sum(x => x.ValorServicos),
                TotalValorIss = g.Sum(x => x.ValorIss),
                IssRetido = g.Select(x => x.IssRetido > decimal.Zero),
                ItemDeServico = g.Select(x => x.ItemListaServico.Descricao),
                CodItemServico = g.Select(x=>x.ItemListaServico.Codigo)
            });
           return new List<IQueryable> { res };
            }

The sending parameter for the method:

Note: When I search only with the date ranges, returns everything correctly.

An example of the search result:

Answer 1

This is due to the logic applied in the grouping of conditions. At the moment you add the OR (||), has just returned the result that "marry" one or the other.

Translating into the following "story" will become easier to understand:

Filter data when competence is:

a.Competencia >= compInicial && a.Competencia <= compFinal

or the Porte:

a.Prestador.Porte.Value == codigoPorte

or is Simple:

a.Prestador.IsSimplesNacional == simplesNacional

or the Service Id:

a.ItemListaServico.Id == itemServico

or Social Reason to Contain:

a.Prestador.RazaoSocial.Contains(query)

or the CPF/CNPJ contains:

a.Prestador.CpfCnpj.Contains(query)

In this way, a true condition nullifies the other conditions, which may be false or true.

If filters should be used together, the operator AND (&&) should be used. If any clause is used only at times, you can use it separately using if:

var res = this.Servicos.Where(a => (a.Competencia >= compInicial && a.Competencia <= compFinal));

if (!String.IsNullOrWhiteSpace(codigoPorte)) res = res.Where(a => (a.Prestador.Porte.Value == codigoPorte);
if (simplesNacional) res = res.Where(a.Prestador.IsSimplesNacional == simplesNacional);

/** demais modificações na lista **/

return new List<IQueryable> { res };

Ps.: I will not consider performance issues.

Answer 2

Other option:

As Gabriel said, you’d have to use the operator && instead of ||

You can in the query lambda check if the parameter is null and if so, returns true which would be the equivalent of "say" in SQL that the field is equal to itself. To shorten the answer, I will put here only the part of the Where

public List<IQueryable> ObterNotaDetalhada(DateTime compInicial, DateTime compFinal, PorteContribuinte? codigoPorte, string query, int? itemServico, bool? simplesNacional)
{
    var res = this.Servicos
    .Where(a =>     (a.Competencia >= compInicial && a.Competencia <= compFinal) &&
                    (codigoPorte == null ? true : a.Prestador.Porte.Value == codigoPorte) && 
                    (simplesNacional == null ? true : a.Prestador.IsSimplesNacional == simplesNacional) &&
                    (itemServico == null ? true : a.ItemListaServico.Id == itemServico) &&
                    (query == null ? true : a.Prestador.RazaoSocial.Contains(query)) &&
                    (query == null ? true : a.Prestador.CpfCnpj.Contains(query)))
    return res;
}