Remove "OR" condition from LINQ query

Question

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?

Answer 1

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

Answer 2

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...)