How do I write this SQL query using Linq/LAMBDA (with inner and left outer join)?

Question

 SELECT * FROM paquet_esc PE
    INNER JOIN depot D                     
        ON PE.id_paquet_esc = D.paquet_esc_id
    INNER JOIN fichier_esc FE              
        ON D.fichier_id = FE.id_fichier
    INNER JOIN engin EN                    
        ON FE.engin_id = EN.id_engin
    LEFT OUTER JOIN reception R                 
        ON FE.id_fichier = R.fichier_id
    LEFT OUTER JOIN traitement T                
        ON FE.id_fichier = T.fichier_id
    LEFT OUTER JOIN code_erreur_traitement CET  
        ON T.code_erreur_id = CET.id_code_erreur
    LEFT OUTER JOIN integration I               
        ON FE.id_fichier = I.fichier_id
    LEFT OUTER JOIN envoi EI                    
        ON FE.id_fichier = EI.fichier_id 

Answer 1

Maybe something like this:

var result= (
        from PE in db.paquet_esc
        join D in db.depot
            on PE.id_paquet_esc equals D.paquet_esc_id
        join FE in db.fichier_esc
            on D.fichier_id equals FE.id_fichier
        join EN in db.engin 
            on FE.engin_id equals EN.id_engin
        from R in db.reception
            .Where(w=>FE.id_fichier == w.fichier_id).DefaultIfEmpty()
        from T in db.traitement
            .Where(w=>FE.id_fichier == w.fichier_id).DefaultIfEmpty()
        from CET in db.code_erreur_traitement
            .Where(w=>T.code_erreur_id == w.id_code_erreur).DefaultIfEmpty()
        from I in db.integration
            .Where(w=>FE.id_fichier == w.fichier_id).DefaultIfEmpty()
        from EI in db.envoi
            .Where(w=>FE.id_fichier == w.fichier_id).DefaultIfEmpty()
        select new
        {
            PE.id_paquet_esc,
            D.paquet_esc_id,
            D.fichier_id
            /*And some other columns*/
        }
    ).ToList();

Where db is the linq data context

Answer 2

here is the demo for your inner join and left join:

var q= from PE in paquet_esc 
       join D in depot on PE.id_paquet_esc equals D.paquet_esc_id
       join FE in fichier_esc on  D.fichier_id equals  FE.id_fichier
       join EN in engin on FE.engin_id equals EN.id_engin
       join R in reception ON FE.id_fichier equals R.fichier_id into lg1
       from R in lg1.DefaultIfEmpty()
       join T in traitement ON FE.id_fichier equals T.fichier_id into lg2
       from T in lg2.DefaultIfEmpty()
       /*bala bala bala */ 
       select PE;