.NET Core EF Core实现left join查询

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

City Table 
Id 
Name 
School Table 
Id 
CityId 
Name 
Student Table 
Id 
SchoolId 
Name

2、EF Core实现left join查询

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();