Reputation: 51
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);
Upvotes: 0
Views: 119
Reputation: 27282
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.
Upvotes: 1