How to use Left Join in My LINQ to EF query?

Question

My LINQ to EF query is,

var query = (from r in DB.region
             join g in DB.governorate on r.region_id equals g.region_id
             join s in DB.IndividualReports on g.governorate_id equals s.governorate_id
             join t in DB.AgencyReports on g.governorate_id equals t.governorate_id
             group new { r, s, t } by new { r.region_id, r.region_name }
             into grp
             select new
                {
                    RId= grp.Key.region_id,
                    RName=grp.Key.region_name,
                    IndividualCount = grp.Select(s => s.s.Id).Distinct().Count(),
                    AgencyCount = grp.Select(t => t.t.Id).Distinct().Count()
                }
            ).ToList().Select(s => new CitysWithComplaintsall
            {
                CityId = s.RId,
                City_NM = s.RName,
                Num_Complaints_Agency = s.IndividualCount,
                Num_Complaints_Ind = s.AgencyCount
            }).ToList();

I want to add left join everywhere instead of simple join.

Same query in SQL Server with left join is like this:

select 
    R.region_id, R.region_name, 
    count(IR.Id) as Individual, 
    count(AR.Id) as Agency 
from 
    region R 
left join 
    governorate G on r.region_id = g.region_id
left join 
    IndividualReports IR on g.governorate_id = IR.governorate_id
left join 
    AgencyReports AR on g.governorate_id = AR.governorate_id
group by 
    R.region_id, R.region_name

One more thing is that this raw SQL query returns some different result if I try to change this query for checking count make changing it to:

select  
    R.region_id, R.region_name,
    count(AR.Id) as Agency 
from 
    region R 
left join 
    governorate G on r.region_id = g.region_id
left join 
    AgencyReports AR on g.governorate_id = AR.governorate_id
group by 
    R.region_id, R.region_name

SQL queries return slightly different result.

You're almost repeating [this question](https://stackoverflow.com/q/54017835/861716). What was not clear in the suggested duplicate there? It perfectly indicates how to do left outer join in LINQ. I also recommended you use navigation properties instead of joins. You don't seem to pay much attention to input from the developer community. — Gert Arnold, Jan 03 '19 at 12:27
Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Tetsuya Yamamoto, Jan 04 '19 at 02:05

How to use Left Join in My LINQ to EF query?

0 Answers0