Convert SQL Request to linq query c# (left outer join and inner join)

Question

I am a new developer and I am used to doing my linq c # queries. I have to convert an sql query to linq and I have a problem with a left outer join and an inner join right after. Here is the query below, can you help me?

select ETAB_NOM, ETAB_CODE, count(etab_nom) as nbFiches from tbl_offreSec OS 
left outer join tbl_ecoleSec ES on ES.code = OS.FK_ecole 
inner join etablissement on etab_code = ES.FK_etablissement 
where OS.archive = 0 and OS.descriptionOrtho = 0 and len(OS.description) > 1 
group by etab_nom, etab_code
order by ETAB_NOM

My class(condensed):

 public class tbl_offreSec
 {
     public int id { get; set; }
     public int FK_ecole { get; set; }
     public string description { get; set; }
     public Nullable<int> descriptionOrtho { get; set; }
     public int archive { get; set; }
 }

publi class tbl_ecoleSec
{
    public int id { get; set; }
    public int code { get; set; }
    public int FK_etablissement { get; set; }
}

public partial class ETABLISSEMENT
{
    public int ETAB_ID { get; set; }
    public int ETAB_CODE { get; set; }
    public string ETAB_NOM { get; set; }
}

UPDATE

In connection with the answer of Svyatoslav:

   IQueryable<ValidOrthoSecList> query = (from OS in db.tbl_offreSec
                                                   join ES in db.tbl_ecoleSec on OS.FK_ecole equals ES.code into gj
                                                   from ES in gj.DefaultIfEmpty()
                                                   join ET in db.ETABLISSEMENTs on ES.FK_etablissement equals ET.ETAB_CODE
                                                   where OS.archive == 0 && OS.descriptionOrtho == 0 && OS.description.Length > 1
                                                   group ET by new { ET.ETAB_NOM, ET.ETAB_CODE } into g
                                                   select new ValidOrthoSecList
                                                   {
                                                       ETAB_NOM = g.Key.ETAB_NOM,
                                                       ETAB_CODE = g.Key.ETAB_CODE,
                                                       nbFiche = g.Sum(x => x.ETAB_NOM == null ? 0 : 1)
                                                   }).OrderBy(e => e.ETAB_NOM);

question from:https://stackoverflow.com/questions/65939512/convert-sql-request-to-linq-query-c-sharp-left-outer-join-and-inner-join

Answer 1

So there is your query:

var query = 
   from os in ctx.tbl_offreSec
   join es in ctx.tbl_offreSec on os.FK_ecole equals es.code into gj
   from es in gj.DefaultIfEmpty()
   join et in ctx.ETABLISSEMENT on es.FK_etablissement equals et.ETAB_CODE
   where os.archive == 0 && os.descriptionOrtho == 0 and os.description.Length > 1 
   group et by new { et.ETAB_NOM, et.ETAB_CODE } into g
   select new 
   {
      g.Key.ETAB_NOM, 
      g.Key.ETAB_CODE,
      nbFiches = g.Sum(x => x.ETAB_NOM == null ? 0 : 1)
   }

var query = query.OrderBy(e => e.ETAB_NOM);

Also consider to do not use LEFT JOIN, since following INNER JOIN will filter out empty records.