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?