如何将分组SQL查询转换为Entity Framework的LINQ查询

首先，假设你已经定义了与数据库表对应的实体类，并配置了正确的导航属性（EF Core可通过Fluent API或数据注解建立表间关联）：

public class Registration
{
    public int RegID { get; set; }
    public int UserID { get; set; }
    public int CourseID { get; set; }
    public int NumOfClass { get; set; }

    public ICollection<TeacherStudent> TeacherStudents { get; set; } = new List<TeacherStudent>();
}

public class TeacherStudent
{
    public int TsID { get; set; }
    public int RegID { get; set; }

    public Registration Registration { get; set; }
    public ICollection<Schedule> Schedules { get; set; } = new List<Schedule>();
}

public class Schedule
{
    public int ScheduleID { get; set; }
    public int TsID { get; set; }
    public DateTime StartDT { get; set; }
    public DateTime EndDT { get; set; }
    public string StatusID { get; set; }

    public TeacherStudent TeacherStudent { get; set; }
}

查询语法风格（接近原生SQL，适合新手理解） #

var userId = 100;

var courseStats = from r in context.Registrations
                  where r.UserID == userId
                  join ts in context.TeacherStudents on r.RegID equals ts.RegID into tsGroup
                  from ts in tsGroup.DefaultIfEmpty() // 对应SQL的LEFT OUTER JOIN TeacherStudent
                  join s in context.Schedules on ts.TsID equals s.TsID into sGroup
                  from s in sGroup.DefaultIfEmpty() // 对应SQL的LEFT OUTER JOIN Schedule
                  group new { r, s } by new { r.RegID, r.CourseID } into g
                  select new
                  {
                      RegID = g.Key.RegID,
                      CourseID = g.Key.CourseID,
                      StartDT = g.Min(x => x.s != null ? x.s.StartDT : (DateTime?)null),
                      EndDT = g.Max(x => x.s != null ? x.s.EndDT : (DateTime?)null),
                      AllCourseHours = (int)g.Average(x => x.r.NumOfClass),
                      NewCourseHours = g.Sum(x => x.s != null && x.s.StatusID == "2200" ? 1 : 0),
                      DoneCourseHours = g.Sum(x => x.s != null && x.s.StatusID == "2210" ? 1 : 0),
                      StudentAbsentCourseHours = g.Sum(x => x.s != null && x.s.StatusID == "2260" ? 1 : 0)
                  };

方法语法风格（链式调用，EF中更常用） #

var userId = 100;

var courseStats = context.Registrations
    .Where(r => r.UserID == userId)
    .GroupJoin(context.TeacherStudents,
               r => r.RegID,
               ts => ts.RegID,
               (r, tsGroup) => new { r, tsGroup })
    .SelectMany(x => x.tsGroup.DefaultIfEmpty(),
                (x, ts) => new { x.r, ts })
    .GroupJoin(context.Schedules,
               x => x.ts.TsID,
               s => s.TsID,
               (x, sGroup) => new { x.r, x.ts, sGroup })
    .SelectMany(x => x.sGroup.DefaultIfEmpty(),
                (x, s) => new { x.r, s })
    .GroupBy(x => new { x.r.RegID, x.r.CourseID })
    .Select(g => new
    {
        RegID = g.Key.RegID,
        CourseID = g.Key.CourseID,
        StartDT = g.Min(x => x.s != null ? x.s.StartDT : (DateTime?)null),
        EndDT = g.Max(x => x.s != null ? x.s.EndDT : (DateTime?)null),
        AllCourseHours = (int)g.Average(x => x.r.NumOfClass),
        NewCourseHours = g.Count(x => x.s != null && x.s.StatusID == "2200"),
        DoneCourseHours = g.Count(x => x.s != null && x.s.StatusID == "2210"),
        StudentAbsentCourseHours = g.Count(x => x.s != null && x.s.StatusID == "2260")
    });

关键细节说明 #

1. LEFT JOIN 处理：

   • 通过GroupJoin + SelectMany + DefaultIfEmpty()模拟SQL的LEFT OUTER JOIN，确保即使没有对应TeacherStudent或Schedule记录，仍能保留Registration的数据。

2. 聚合函数映射：

   • MIN(S.startDT)/MAX(S.endDT)：需判断s是否为null，返回可空DateTime匹配SQL的NULL结果，避免空引用异常。
   • AVG(R.numOfClass)：同一分组内NumOfClass值固定，Average结果等价于直接取该值，也可用g.First().r.NumOfClass优化，效果一致。
   • 条件求和（SUM(CASE...)）：可使用Sum(条件?1:0)或Count(条件)，EF Core会自动转换为对应SQL条件聚合。

3. 分组逻辑：

   • 按照RegID和CourseID分组，与原生SQL的GROUP BY完全对应。

如果需要将结果绑定到业务逻辑或前端，建议创建DTO类替代匿名类型，示例如下：

public class CourseStatisticsDto
{
    public int RegID { get; set; }
    public int CourseID { get; set; }
    public DateTime? StartDT { get; set; }
    public DateTime? EndDT { get; set; }
    public int AllCourseHours { get; set; }
    public int NewCourseHours { get; set; }
    public int DoneCourseHours { get; set; }
    public int StudentAbsentCourseHours { get; set; }
}

只需将LINQ中的select new替换为select new CourseStatisticsDto { ... }即可。

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