Search in lambda query with logical operators

Question

Search in lambda query with logical operators

Navigation

#1 by (2 votes)
#2 by (2 votes)

1

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

However, when performing the query, the function always returns values that do not match the parameters I passed at the moment of executing the 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 parameters that I submit to the method:

Note:WhenIsearchonlythedaterange,itreturnseverythingcorrectly.

Anexamplesearchresult:

c# query

asked by anonymous 19.06.2018 / 21:34

2 answers

2

Another option:

As stated by Gabriel, you would have to use the && operator instead of ||

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

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

19.06.2018 / 22:11

SQL Query, Always show first record of a repeating field How to remove add / remove panel according to checkbox selection?

score 2 · Accepted Answer

This is due to the logic applied in the condition grouping. When you add OR ( || ), it ends up returning the result that "marries" one or the other.

Translating to the following "story" will be easier to understand:

Filter the data when the skill is:

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

or the Porte:

a.Prestador.Porte.Value == codigoPorte

or is SimpleNational:

a.Prestador.IsSimplesNacional == simplesNacional

or the Service Id:

a.ItemListaServico.Id == itemServico

or the Social Reason:

a.Prestador.RazaoSocial.Contains(query)

or the CPF / CNPJ contain:

a.Prestador.CpfCnpj.Contains(query)

In this way, a true condition overrides all other conditions, which may be false or true.

If the filters are to be used together, the AND ( && ) operator should be used. If any clause is only used in a few moments, 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.