EF Core实现MySQL三表左连接分组统计学生数量

我来帮你搞定这个EF Core的查询实现，让它和你写的原生MySQL语句逻辑完全匹配，得到你想要的统计结果。先看看你原来的代码里存在的几个问题：

• 第二个左连接时错误引用了原school变量，应该用左连接后得到的匹配实体（可能为null）
• 统计学生数的时机不对，不能在初始select里直接用集合计数，应该在分组后对每组的学生记录进行聚合计数
• 最后的分组和Select处理逻辑有误，没有正确实现按城市分组并聚合的需求

正确的LINQ查询语法实现 #

下面是修正后的代码，完全对应你的原生SQL逻辑：

var db = new MyDbContext(); 
var result = (from city in db.City
              // 左连接School表，得到当前城市对应的所有学校（无学校则为null）
              join school in db.School on city.Id equals school.CityId into schoolGroup
              from school in schoolGroup.DefaultIfEmpty()
              // 左连接Student表，得到当前学校对应的所有学生（无学生则为null）
              join student in db.Student on school?.Id equals student.SchoolId into studentGroup
              from student in studentGroup.DefaultIfEmpty()
              // 按城市的Id和名称分组（EF Core要求SELECT中的非聚合字段需包含在分组键中）
              group student by new { city.Id, city.Name } into cityGroup
              select new CityWithStudentCount
              {
                  Id = cityGroup.Key.Id,
                  CityName = cityGroup.Key.Name,
                  // 统计非null的学生记录，对应原生SQL的COUNT(tstudent.id)（自动忽略null）
                  StudentCount = cityGroup.Count(s => s != null)
              }).ToList();

代码说明 #

1. 使用school?.Id处理左连接后school为null的情况，避免空引用异常，这对应原生SQL中左连接允许School表无匹配记录的逻辑
2. 按city.Id和city.Name分组：因为EF Core遵循严格的分组规则，SELECT中出现的非聚合字段必须包含在分组键里（如果你的MySQL关闭了ONLY_FULL_GROUP_BY，也可以只按Id分组，但按Id+Name分组更通用）
3. cityGroup.Count(s => s != null)：左连接后，没有学生的城市会得到一系列null的student记录，这个计数会忽略这些null，和原生SQL中COUNT(tstudent.id)的行为完全一致（COUNT(字段)会自动忽略null值）

可选：方法链风格实现 #

如果你更喜欢方法链的写法，也可以用下面的代码：

var result = db.City
    // 左连接School表
    .GroupJoin(db.School, 
        city => city.Id, 
        school => school.CityId, 
        (city, schools) => new { city, schools })
    .SelectMany(x => x.schools.DefaultIfEmpty(), 
        (x, school) => new { x.city, school })
    // 左连接Student表
    .GroupJoin(db.Student, 
        xs => xs.school?.Id, 
        student => student.SchoolId, 
        (xs, students) => new { xs.city, students })
    .SelectMany(x => x.students.DefaultIfEmpty(), 
        (x, student) => new { x.city, student })
    // 按城市分组
    .GroupBy(x => new { x.city.Id, x.city.Name })
    // 映射到结果实体
    .Select(g => new CityWithStudentCount
    {
        Id = g.Key.Id,
        CityName = g.Key.Name,
        StudentCount = g.Count(s => s.student != null)
    })
    .ToList();

这段代码执行后，会得到和你预期完全一致的结果：有学生的城市显示对应数量，没有学生的城市显示0。

内容的提问来源于stack exchange，提问作者ASPMaker