.NET Core EF Core实现left join查询

1、介绍EF Core实现left join的3张表

1）City Table 

Id 
Name 

2）School Table 

Id 
CityId 
Name 

3）Student Table 

Id 
SchoolId 
Name

2、EF Core实现left join查询

EF Core中，可以使用LINQ的 join 语句和 DefaultIfEmpty() 方法结合来实现 LEFT JOIN 的效果。DefaultIfEmpty() 方法在没有找到匹配项时会返回一个默认值，从而实现左连接。

var db = new MyDbContext();
var result = (from city in db.City
         join school in db.School on city.Id equals school.CityId into t1
         from school in t1.DefaultIfEmpty()
         join student in db.Student on school.Id equals student.SchoolId into t2
         from student in t2.DefaultIfEmpty()
         group student by new { city.Id,city.Name } into cityGrouped
         select new CityWithStudentCount
         {
             Id = cityGrouped.Key.Id,
             CityName = cityGrouped.Key.Name,
             StudentCount = cityGrouped.Count(x => x.student != null)
         })
         .ToList();