带计数的 EF Core 左连接

我在 MySql 数据库上有 3 个表。我想在这 3 个表之间进行左连接并使用 group by 进行计数。

城市表
Id
Name

学校桌
Id
CityId
Name

学生桌
Id
SchoolId
Name

/* MySql raw query like this: */
select Count(tstudent.id) as StudentCount, tcity.Id, tcity.Name
from City tcity
left join School tschool on tcity.Id = tschool.CityId
left join Student tstudent on tschool.Id = tstudent.SchoolId
group by tcity.Id;

使用 EF Core 我尝试这样：

class CityWithStudentCount {
    public int Id { get;set; }
    public string CityName { get;set; }
    public int StudentCount { get;set; }
}

EF核心：

var db = new MyDbContext();

var result = (from city in db.City
             join school in db.School on city.Id equals school.CityId into tcity
             from r1 in tcity.DefaultIfEmpty()

             join student in db.Student on school.Id equals student.SchoolId into tschool
             from r2 in tschool.DefaultIfEmpty()
             
             select new CityWithStudentCount
             {
                 Id = city.Id,
                 CityName = city.Name,
                 StudentCount = tschool.count()
             } into s1
             
             group s1 by s1.Id)
             .Select(s=>s.ToList())
             .ToList();

结果一定是这样的：

1 City1 10
2 City2 3
3 City3 0
4 City4 0
5 City5 12

我该如何使用 Entity Framework Core 来查询此结果。谢谢。

你的查询是错误的。

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

另外，我强烈建议您使用导航属性而不是手动构建连接。