Remove "OR" condition from LINQ query

3

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" looks 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 the code snippet: OR (([Extent5].[CdExecutor] IS NULL) AND ([Extent6].[CdExecutor] IS NULL)) to be removed from the output of this SQL by changing the method code.

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

Any ideas how to do this?

    
asked by anonymous 09.06.2017 / 21:05

1 answer

-1

It would be possible to do Where Clause after this IQueryable < & gt ;? Just do the joins and select your needs, then do the where with Lambda clauses in the .Where (q => ...)

    
23.11.2018 / 14:11