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).
– Oralista de Sistemas
how about using regex?
– Paz
@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.
– Guilherme Kuhnen
@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?
– Oralista de Sistemas
could post your mapping .... All your Routes and Itineraries
– Marco Souza