EF Core中SelectMany后接GroupBy的查询报错及可行方案咨询

需求为查询学校列表及其对应每月入学学生的统计数据，编写的EF Core查询代码如下：

_dbContext.Schools
 .Select(p => new MySchoolView
 {
   School= p,
   MonthlyStatistics = p.Teacher
     .SelectMany(d => d.Students)
     .GroupBy(d => new { d.StartDate.Month, d.StartDate.Year })
     .Select(g => new MonthlyStatisticsView
       {
         Year = g.Key.Year,
         Month = g.Key.Month,
         Count = g.Count(),
         Dollars = g.Sum(d => d.Tuition)
       }).ToList()
 })

执行该查询触发报错：

System.InvalidOperationException: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.

但基于教师表统计每月数据的相似查询可正常运行：

_dbContext.Schools
 .Select(p => new MySchoolView
 {
   School= p,
   MonthlyStatistics = p.Teacher
     .GroupBy(d => new { d.EmploymentDate.Month, d.EmploymentDate.Year })
     .Select(g => new MonthlyStatisticsView
       {
         Year = g.Key.Year,
         Month = g.Key.Month,
         Count = g.Count(),
         Dollars = g.Sum(d => d.Salary)
       }).ToList()
 })

推测问题源于EF Core无法处理SelectMany后接GroupBy的场景，现排除以下不可行方案：

• 使用客户端评估
• 拆分为多个查询
• 重构数据库

咨询是否有可行改写方式，例如直接从School导航到Student或重写SelectMany操作。

方案1：利用School到Student的直接导航属性（如果已配置） #

若School实体有直接关联Student的导航属性（如p.Students），可跳过Teacher层级直接统计，EF Core能正常翻译该查询：

_dbContext.Schools
 .Select(p => new MySchoolView
 {
   School = p,
   MonthlyStatistics = p.Students
     .GroupBy(d => new { d.StartDate.Month, d.StartDate.Year })
     .Select(g => new MonthlyStatisticsView
       {
         Year = g.Key.Year,
         Month = g.Key.Month,
         Count = g.Count(),
         Dollars = g.Sum(d => d.Tuition)
       }).ToList()
 })

方案2：改用LINQ查询语法重构子查询 #

使用查询语法展开关联逻辑，替代方法链中的SelectMany+GroupBy组合，能让EF Core正确解析关联关系：

_dbContext.Schools
.Select(p => new MySchoolView
{
  School = p,
  MonthlyStatistics = (from teacher in p.Teacher
                       from student in teacher.Students
                       group student by new { student.StartDate.Month, student.StartDate.Year } into g
                       select new MonthlyStatisticsView
                       {
                         Year = g.Key.Year,
                         Month = g.Key.Month,
                         Count = g.Count(),
                         Dollars = g.Sum(s => s.Tuition)
                       }).ToList()
})

方案3：显式通过外键关联学生与学校 #

直接从Students表出发，通过外键（如Teacher.SchoolId）关联当前学校，再分组统计，避免多层导航后的翻译问题：

_dbContext.Schools
.Select(p => new MySchoolView
{
  School = p,
  MonthlyStatistics = _dbContext.Students
   .Where(s => s.Teacher.SchoolId == p.Id)
   .GroupBy(s => new { s.StartDate.Month, s.StartDate.Year })
   .Select(g => new MonthlyStatisticsView
     {
       Year = g.Key.Year,
       Month = g.Key.Month,
       Count = g.Count(),
       Dollars = g.Sum(s => s.Tuition)
     }).ToList()
})

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