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;
Which returns an 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)) be removed from the output of this SQL by changing the method code.
The method excerpt that inserts this check: 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:
According to this source here , you can make 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"
});