将ASP.NET迁移至.NET Core 6：如何把统计SQL转为EF LINQ查询

表结构说明 #

Registration:
   regID,
   userID,
   courseID,
   numOfClass,     (注册的课程课时数) 
(学生（userID）可注册多门不同课程)

TeacherStudent:
   tsID,
   regID,
(已注册的学生会被分配给讲师)

Schedule:
   scheduleID,
   tsID,
   startDT,        (课程开始日期/时间)
   endDT,          (课程结束日期/时间)
   statusID,       (课程状态：2200为新排课，2210为已完成，2260为学生缺勤)
(讲师为学生在该表中排课，每门注册课程最多可排numOfClass次课。例如，学生注册30课时的课程，该表最多可生成30条对应记录。)

查询需求 #

针对指定学生（userId），获取每门课程的统计数据，包含以下字段：

• courseId
• 课程开始日期/时间（取该课程最早排课时间）
• 课程结束日期/时间（进行中课程取最新课程时间）
• 注册课时数
• 已排课次数（总排课记录数）
• 已完成次数（statusID=2210的记录数）
• 学生缺勤次数（statusID=2260的记录数）

原SQL Server查询语句 #

SELECT R.regID, 
       R.courseID, 
       MIN(S.startDT) AS startDT,  
       MAX(S.endDT) AS endDT, 
       AVG(R.numOfClass) AS allCourseHours, 
       SUM(CASE WHEN S.statusID = '2200' THEN 1 ELSE 0 END) AS newCourseHours, 
       SUM(CASE WHEN S.statusID = '2210' THEN 1 ELSE 0 END) AS doneCourseHours, 
       SUM(CASE WHEN S.statusID = '2260' THEN 1 ELSE 0 END) AS studentAbsentCourseHours
FROM Registration AS R 
JOIN TeacherStudent AS T ON R.regID = T.regID 
JOIN Schedule AS S ON S.tsID = T.tsID
WHERE R.UserId = 100
GROUP BY R.regId, R.courseId

EF Core LINQ实现方案 #

假设你的EF Core实体类已正确配置导航属性：

• Registration包含ICollection<TeacherStudent>类型的TeacherStudents属性
• TeacherStudent包含ICollection<Schedule>类型的Schedules属性

方法语法写法 #

var userId = 100;

var stats = await _context.Registrations
    .Where(r => r.UserId == userId)
    .SelectMany(r => r.TeacherStudents, (r, ts) => new { r, ts })
    .SelectMany(rt => rt.ts.Schedules, (rt, s) => new { rt.r, s })
    .GroupBy(rs => new { rs.r.RegId, rs.r.CourseId })
    .Select(g => new 
    {
        g.Key.RegId,
        g.Key.CourseId,
        StartDT = g.Min(rs => rs.s.StartDT),
        EndDT = g.Max(rs => rs.s.EndDT),
        AllCourseHours = g.Average(rs => rs.r.NumOfClass),
        NewCourseHours = g.Count(rs => rs.s.StatusID == "2200"),
        DoneCourseHours = g.Count(rs => rs.s.StatusID == "2210"),
        StudentAbsentCourseHours = g.Count(rs => rs.s.StatusID == "2260"),
        TotalScheduled = g.Count() // 已排课总次数
    })
    .ToListAsync();

查询语法写法 #

var userId = 100;

var stats = await (
    from r in _context.Registrations
    where r.UserId == userId
    from ts in r.TeacherStudents
    from s in ts.Schedules
    group new { r, s } by new { r.RegId, r.CourseId } into g
    select new 
    {
        g.Key.RegId,
        g.Key.CourseId,
        StartDT = g.Min(x => x.s.StartDT),
        EndDT = g.Max(x => x.s.EndDT),
        AllCourseHours = g.Average(x => x.r.NumOfClass),
        NewCourseHours = g.Count(x => x.s.StatusID == "2200"),
        DoneCourseHours = g.Count(x => x.s.StatusID == "2210"),
        StudentAbsentCourseHours = g.Count(x => x.s.StatusID == "2260"),
        TotalScheduled = g.Count()
    }
).ToListAsync();

关键说明 #

1. 关联逻辑：通过SelectMany（或查询语法的嵌套from）实现原SQL的JOIN操作，依次遍历Registration关联的TeacherStudent、TeacherStudent关联的Schedule。
2. 分组规则：按regId和courseId分组，完全匹配原SQL的GROUP BY逻辑。
3. 聚合转换：
   • Min/Max直接对应原SQL的MIN(S.startDT)和MAX(S.endDT)
   • Average等价于AVG(R.numOfClass)
   • 带条件的Count替代原SQL的SUM(CASE...)，EF Core会自动将其转换为等价的SQL CASE语句
4. 异步支持：使用ToListAsync()实现异步查询，符合.NET Core异步编程规范。

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