C# LINQ translation for a SQL query with RIGHT JOIN

I've got a query in ACCESS and i have to translate this query in C# LINQ.

SELECT DETAIL.*, 
       "" AS DBC_ART, 
       A_ENS.ENS_CODE, 
       A_LEA.LEA_ART, 
       A_ART_1.ART_CHA, 
       A_ART_1.ART_ANA, 
       A_ANA_REPART.ANA_CLA2 
FROM (
       (A_LEA RIGHT JOIN 
        ((DETAIL LEFT JOIN A_ART ON DETAIL.DFA_ART = A_ART.ART_CODE)
         LEFT JOIN A_ENS ON DETAIL.DFA_ART = A_ENS.ENS_CODE) ON A_LEA.LEA_ENS = A_ENS.ENS_CODE) 
         LEFT JOIN A_ART AS A_ART_1 ON A_LEA.LEA_ART = A_ART_1.ART_CODE) 
         LEFT JOIN A_ANA_REPART ON A_ART_1.ART_ANA = A_ANA_REPART.ANA_CODE 
WHERE (((A_ENS.ENS_CODE) Is Not Null) AND ((A_ART.ART_CODE) Is Null)) 
ORDER BY DETAIL.BCI, DETAIL.FACTURE;

I don't need exactly the detail fields as it's detail.* in the query (I'll found the necessary by myself)

It's for the multiple JOIN that i don't know how to translate them. I've got object lists for each 5 tables

I.E.

Detail -> LstDetail

A_ART -> LstA_ART

A_ENS -> LstA_ENS

A_LEA -> LstA_LEA

A_ART_1 -> LstA_ART (A_ART1 is an alias for another join than with A_ART)

A_ANAREPART -> LstA_ANAREPART

So I begin to write something like :

MyList = (from itemDetail in LstDetail 
          join itemART in LstART 
            on itemDetail.DFA_ART equals itemART.ART_CODE 
          join itemANA_REPART in LstANA_REPART 
            on itemART.ART_ANA equals itemANA_REPART.ANA_CODE 
          select (...)).ToList();

But I don't know how to handle the right join in the query. Should I make multiple linq queries? What kind?

Thanks in advance for answers

Answers


Something like this might translate, you may need to adjust case as I've used all lower case. Additionally you need to choose how to deal with detail.*, either replace the d... with a comma separated list of fields from detail d.field1, d.field2 or something like detail = d to allow the detail record to be included as an object.

var query = from d in detail
join a1 in a_art on d.dfa_art equals a1.art_code into art
from a in art.DefaultIfEmpty()
join ae1 in a_ens on d.dfa_art equals ae1.ens_code into ens
from ae in ens.DefaultIfEmpty()
join al1 in a_lea on ae.ens_code equals al1.lea_ens into lea
from al in lea.DefaultIfEmpty()
join a2 in a_art on al.art_code equals a2.lea_art into art1
from a1 in art1.DefaultIfEmpty()
join ar1 in a_ana_repart on a1.art_ana equals ar1.ana_code into ana
from ar in ana.DefaultIfEmpty()
where (ae.ens_code != null && a.art_code == null)
select new {
    d...,
    ae.ens_code,
    al.lea_art,
    a1.art_cha,
    a1.art_ana,
    ar.ana_cla2
};

Need Your Help

php merge multidimensional array ordering by key

php arrays sorting multidimensional-array merge

I'm trying to sort a multidimensional array down to a sorted, one-dimensional array where values on the same level are merged together ordered by key alternating between it's parents.

Inconsistent formula returns in Excel

microsoft-excel worksheet-function

I've been having problems using the following formula: