C# LINQ Join with OR condition?

Asked

Viewed 61 times

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 *

c# sql database linq lambda-expressions

  • This SQL is not translated correctly to Linq, the ideal, make a View and call for persistence layer.

  • that second WHERE that you added should actually be a ON, however, I do not know if it will be possible to assemble this expression, so I suggest you create a procedure in the database that executes the original SQL query from a receiving date filter that you will inform. The idea of the View 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.

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

1 answer

0

The Join is missing

In that case the structure should be close to that

var query = (from ta in ctx.TableA
             join tb in ctx.TableB
               on ta.Id equals tb.Id into TJoin
             from Tables tbs in TJoin.DefaultIfEmpty()
             where tbs.Id != 2
             select new TablesJoinViewMode
             {
                 Id1 = ta.ID,
                 Id2 - tb.Id
                 /* set other properties*/
             });

Browser other questions tagged c# sql database linq lambda-expressions

You are not signed in. Login or sign up in order to post.