Remove "OR" condition from LINQ query

Asked

Viewed 227 times

4

I have the following method:

bool naoUsarNomeCliente = String.IsNullOrWhiteSpace(filtro.NomeCliente);
        long codigoExterno;
        bool naoUsarCodigoExterno = !long.TryParse(filtro.CodigoExterno, out codigoExterno);
        bool naoUsarFiltroStatus = filtro.Status == null || !filtro.Status.HasValue;
        bool naoUsarFiltroStatusTele = filtro.StatusTeleservice == null || !filtro.StatusTeleservice.HasValue;

        var query = (from os in Context.OrdensServico
                join c in Context.TodosClientes on os.CodigoCliente equals c.Codigo

                join pos in Context.PacoteOrdensServico on os.Codigo equals pos.CodigoOrdemServico into posleft
                from pos in posleft.DefaultIfEmpty()

                join p in Context.Pacotes on pos.CodigoPacote equals p.Codigo into pleft
                from p in pleft.DefaultIfEmpty()

                join r in Context.Roteiros on p.CodigoRoteiro equals r.Codigo into rleft
                from r in rleft.DefaultIfEmpty()

                join u in Context.TodosUsuarios on r.CodigoExecutor equals u.CodigoExecutor into uleft
                from u in uleft.DefaultIfEmpty()

                where
                    os.DataOrdemServicoOriginal >= filtro.DataAtividadeInicio
                    && os.DataOrdemServicoOriginal <= filtro.DataAtividadeFim
                    && (naoUsarNomeCliente || c.Nome.Equals(filtro.NomeCliente))
                    && (naoUsarCodigoExterno || os.CodigoExterno.Equals(codigoExterno))
                    && (naoUsarFiltroStatus || ((filtro.Status == 1 && os.CodigoExecucaoOrdemServico.HasValue) 
                                                    && (filtro.Status == 2 && os.CodigoExecucaoOrdemServico == null)))
                    && (naoUsarFiltroStatusTele || ((filtro.StatusTeleservice == 1 && os.ExecutadaTeleservice)
                                                    && (filtro.StatusTeleservice == 2 && os.ExecutadaTeleservice == false)))
                select new ConsultaOrdemServico()
                {
                    Codigo = os.CodigoExterno,
                    DataOriginal = os.DataOrdemServicoOriginal,
                    CodigoCliente = c.CodigoNegocio,
                    NomeCliente = c.Nome,
                    NomeExecutor = u != null ? u.Nome : "",
                    DataRoteiro = r != null ? r.DataRoteiro : DateTime.MinValue,
                    Atividade = os.ExecutadaTeleservice ? "Enviada" : "Não enviada",
                    Status = os.CodigoExecucaoOrdemServico.HasValue ? "Executada" : "Não executada",
                    EnvioTeleService = os.ExecutadaTeleservice ? "Enviada" : "Não enviada"
                });

        return query;

That returns a IQueryable<>.

The generated "SQL" is like this:

SELECT 
1 AS [C1], 
[Extent1].[CdCodigoExterno] AS [CdCodigoExterno], 
[Extent1].[DtOrdemServicoOriginal] AS [DtOrdemServicoOriginal], 
[Extent2].[CdClienteNegocio] AS [CdClienteNegocio], 
[Extent2].[NmCliente] AS [NmCliente], 
CASE WHEN ([Extent6].[CdUsuario] IS NOT NULL) THEN [Extent6].[NmUsuario]     ELSE N'' END AS [C2], 
CASE WHEN ([Extent5].[CdRoteiro] IS NOT NULL) THEN [Extent5].[DtRoteiro]     ELSE @p__linq__12 END AS [C3], 
CASE WHEN ([Extent1].[IdExecutadaTeleservice] = 1) THEN N'Enviada' ELSE     N'Não enviada' END AS [C4], 
CASE WHEN ([Extent1].[CdExecucaoOrdemServico] IS NOT NULL) THEN N'Executada'     ELSE N'Não executada' END AS [C5], 
CASE WHEN ([Extent1].[IdExecutadaTeleservice] = 1) THEN N'Enviada' ELSE     N'Não enviada' END AS [C6]
FROM      [OPMDM].[TB_ORDEM_SERVICO] AS [Extent1]
INNER JOIN [OPMDM].[TB_CLIENTE] AS [Extent2] ON [Extent1].[CdCliente] =     [Extent2].[CdCliente]
LEFT OUTER JOIN [OPMDM].[TB_PACOTE_ORDEM_SERVICO] AS [Extent3] ON [Extent1].    [CdOrdemServico] = [Extent3].[CdOrdemServico]
LEFT OUTER JOIN [OPMDM].[TB_PACOTE] AS [Extent4] ON [Extent3].[CdPacote] =     [Extent4].[CdPacote]
LEFT OUTER JOIN [OPMDM].[TB_ROTEIRO] AS [Extent5] ON [Extent4].[CdRoteiro] =     [Extent5].[CdRoteiro]
LEFT OUTER JOIN [OPMDM].[TB_USUARIO] AS [Extent6] ON ([Extent5].[CdExecutor]     = [Extent6].[CdExecutor]) OR (([Extent5].[CdExecutor] IS NULL) AND ([Extent6].    [CdExecutor] IS NULL))
WHERE ([Extent1].[DtOrdemServicoOriginal] >= @p__linq__0) AND ([Extent1].    [DtOrdemServicoOriginal] <= @p__linq__1) AND (@p__linq__2 = 1 OR [Extent2].    [NmCliente] = @p__linq__3) AND (@p__linq__4 = 1 OR [Extent1].[CdCodigoExterno] =     @p__linq__5) AND ((@p__linq__6 = 1) OR ((1 = @p__linq__7) AND ([Extent1].    [CdExecucaoOrdemServico] IS NOT NULL) AND (2 = @p__linq__8) AND ([Extent1].    [CdExecucaoOrdemServico] IS NULL))) AND ((@p__linq__9 = 1) OR ((1 =     @p__linq__10) AND ([Extent1].[IdExecutadaTeleservice] = 1) AND (2 =     @p__linq__11) AND (0 = [Extent1].[IdExecutadaTeleservice])))

I need that code snippet: OR (([Extent5].[CdExecutor] IS NULL) AND ([Extent6].[CdExecutor] IS NULL)) is removed from this SQL output by changing the method code.

The excerpt from the method that inserts this verification: join u in Context.TodosUsuarios on r.CodigoExecutor equals u.CodigoExecutor into uleft from u in uleft.DefaultIfEmpty().

Any idea how to do this?

  • Is that really necessary? Because the condition you want to remove is redundant if you look at the condition immediately to the left of it. Join is done where the value of two fields is equal, or if both fields have the same arbitrary value (in this case null).

  • how about using regex?

  • @Renan yes it is necessary, this code was updated to improve the performance but happened to add this "OR" and we really can not proceed with it there.

  • @Guilhermekuhnen I again question whether this is necessary. Allow me to modify the point of view: by executing the same query with and without the condition you want to remove, you notice some difference in the results obtained or in the performance of the query?

  • could post your mapping .... All your Routes and Itineraries

2 answers

0

According to this source here, you can do LEFT OUTER Joins as follows:

var ctx = new Context();

var query = from os  in ctx.OrdensServico
            from c   in ctx.TodosClientes.Where(w => os.CodigoCliente == c.Codigo)
            from pos in ctx.PacoteOrdensServico.Where(w => os.Codigo == pos.CodigoOrdemServico).DefaultIfEmpty()
            from p   in ctx.Pacotes.Where(w => pos.CodigoPacote == p.Codigo).DefaultIfEmpty()
            from r   in ctx.Roteiros.Where(w => p.CodigoRoteiro == r.Codigo).DefaultIfEmpty()
            from u   in ctx.TodosUsuarios.Where(w => r.CodigoExecutor == u.CodigoExecutor).DefaultIfEmpty()
            where os.DataOrdemServicoOriginal >= filtro.DataAtividadeInicio
            where os.DataOrdemServicoOriginal <= filtro.DataAtividadeFim
            where (naoUsarNomeCliente || c.Nome.Equals(filtro.NomeCliente))
            where (naoUsarCodigoExterno || os.CodigoExterno.Equals(codigoExterno))
            where (naoUsarFiltroStatus || ((filtro.Status == 1 && os.CodigoExecucaoOrdemServico.HasValue) 
            && (filtro.Status == 2 && os.CodigoExecucaoOrdemServico == null)))
            where (naoUsarFiltroStatusTele || ((filtro.StatusTeleservice == 1 && os.ExecutadaTeleservice)
            && (filtro.StatusTeleservice == 2 && os.ExecutadaTeleservice == false)))
            select new ConsultaOrdemServico()
            {
                Codigo = os.CodigoExterno,
                DataOriginal = os.DataOrdemServicoOriginal,
                CodigoCliente = c.CodigoNegocio,
                NomeCliente = c.Nome,
                NomeExecutor = u?.Nome ?? "",
                DataRoteiro = r?.DataRoteiro ?? DateTime.MinValue,
                Atividade = os.ExecutadaTeleservice ? "Enviada" : "Não enviada",
                Status = os.CodigoExecucaoOrdemServico.HasValue ? "Executada" : "Não executada",
                EnvioTeleService = os.ExecutadaTeleservice ? "Enviada" : "Não enviada"
            });

-1

Would it be possible to do the Where Clause after this Iqueryable<>? Just do the joins and select your needs, then do the Where with Lambda clauses on . Where(q=>q...)

Browser other questions tagged

You are not signed in. Login or sign up in order to post.