How can I convert this SQL to Linq?

user_13524589 0
Viewed 42

I have a query : 

select siniflar.id,siniflar.sinif,count(ogrenciId) 
  from siniflar
  left join oda
  on siniflar.id=oda.sinifId
  where siniflar.ogretmenid=2
  group by siniflar.id,siniflar.sinif

And this is my conversion to Linq:

  from snf in db.siniflar
     join oda in db.oda
     on snf.id equals oda.sinifId
     where snf.ogretmenid==oID
     group oda by new
     {
     snf.id,snf.sinif
     } into g 
     select new
     {
     sinifIDsi=g.Key.id,
     sinifADi=g.Key.sinif,
     Column1 = g.Count()
     })

I want the result of empty count's too but I can't adapt left or right join to linq with group by. DefaultIfEmpty() method is not working. Can anyone help me?

sql linq c#
1 Answers

you could add DefaultIfEmpty() to calculate also the empty count's for left table, like the following query:

var result = (from snf in db.siniflar
             join oda in db.oda on snf.id equals oda.sinifId into leftedOdatas
             from leftedOdata in leftedOdatas.DefaultIfEmpty()
             where snf.ogretmenid == 2
             group leftedOdata by new {snf.id, snf.sinif } into g 
             select new
             {
                 sinifIDsi = g.Key.id,
                 sinifADi = g.Key.sinif,
                 Column1 = g.Count(x => x?.sinifId != null)
             }).ToList();

I hope you find this helpful.

user_17457950
Related