0
I need to write this SQL
in Linq
, but in that condition JOIN
is what gets me.
SELECT * FROM
Boleto_Retorno AS BR
LEFT JOIN Recebimentos AS R ON SUBSTRING(BR.CH_NossoNumero,1,11) =
CASE WHEN LEN(BR.CH_NossoNumero) = 12
THEN SUBSTRING(R.CH_NossoNumero,1,11)
ELSE NULL END
OR BR.CH_NossoNumero =
CASE WHEN LEN(BR.CH_NossoNumero) > 12
THEN R.CH_NossoNumero
ELSE NULL END
LEFT JOIN Clientes AS C ON C.CD_Cliente = R.CD_CodigoCliente
WHERE (BR.VF_Processado = 'true')
AND (BR.DT_DataRecebimento BETWEEN @DataInicial AND @DataFinal)
I’ve tried it this way
from Boleto_Retorno in db.Boleto_Retorno.AsNoTracking()
where Boleto_Retorno.VF_Processado == true
&& Boleto_Retorno.DT_DataRecebimento >= dataInicial
&& Boleto_Retorno.DT_DataRecebimento <= dataFinal
from Recebimentos in db.Recebimentos
where Boleto_Retorno.CH_NossoNumero.Substring(1, 11) ==
(
Boleto_Retorno.CH_NossoNumero.Length == 12 ? Recebimentos.CH_NossoNumero.Substring(1, 11) : null)
|| Boleto_Retorno.CH_NossoNumero == (Boleto_Retorno.CH_NossoNumero.Length > 12 ? Recebimentos.CH_NossoNumero : null
)
select new ModelBoletoRetorno{ //campos da tabela}).ToList();
but not return equal to instruction SQL
o returns a 9 lines and the expression Linq
returns about 420,000 lines.
Observing: ignore the select *
This SQL is not translated correctly to Linq, the ideal, make a
View
and call for persistence layer.– novic
that second
WHERE
that you added should actually be aON
, however, I do not know if it will be possible to assemble this expression, so I suggest you create aprocedure
in the database that executes the original SQL query from a receiving date filter that you will inform. The idea of theView
suggested above is also a good option, however, as I see that there is filter in the query (mainly of date), maybe the Procedure is more performative.– phduarte
this sql is already a precedent, and we are taking them and "converting" into Latin. I have put together another way, a foreach of the Boleto_retorno list, and a method that makes a search (firstOrDefault) in the receipts with the conditions made of the process. Although I had to be forced to go this way as it is not possible via Lilli. But it worked.
– Mauricio Furtado