EF Core LINQ查询无法转换：用户考试统计查询重构求助

需求说明 #

需统计每个用户的考试次数、单场考试最低分、最高分及平均分，目标生成的SQL如下：

with
agg as (
    select
        s.UserId,
        count(*) cnt,
        min(s.sump) minPoints,
        max(s.sump) maxPoints,
        avg(s.sump) avgPoints
    from (
        select
            e.UserId,
            sum(a.Points) sump
        from Examinations e 
        join UserAnswers ua on ua.ExamId = e.id
        join Answers a on a.Id = ua.AnswerId
        group by e.UserId, e.Id
    ) s
    group by s.UserId
)
select
    u.UserId,
    u.Name,
    u.Surname,
    u.Patronimic,
    agg.cnt,
    agg.minPoints,
    agg.maxPoints,
    agg.avgPoints
from AppUsers u
join agg on u.UserId = agg.UserId

当前问题 #

编写的LINQ代码逻辑嵌套复杂，EF Core无法将其翻译为SQL，报错「could not be translated」。原代码如下：

_db.AppUsers
.GroupJoin(
    _db.Examinations
    .Include(x => x.UserAnswers)
    .Where(x => x.FinishDateTime != null && x.ExamPassed != null),
    user => user.UserId,
    exam => exam.UserId,
    (user, exam) => new
    {
        user.UserId,
        user.Name,
        user.Surname,
        user.Patronimic,
        aggregates = exam
            .Select(g => new
            {
                g.UserId,
                UserPoints = g.UserAnswers
                    .Join(
                        _db.Answers,
                        uAnswer => uAnswer.AnswerId,
                        answer => answer.Id,
                        (uAnswer, answer) => new { answer.Points }
                    ).Sum(x => x.Points)
            })
            .GroupBy(g => g.UserId)
            .Select(x => new
            {
                min = x.Min(a => a.UserPoints),
                max = x.Max(a => a.UserPoints),
                avg = x.Average(a => a.UserPoints),
                cnt = x.Count()
            })
            .FirstOrDefault()
    }
)
.Select(a => new UserExamsTotalModel
{
    UserId = a.UserId,
    UserName = a.Name,
    UserSurname = a.Surname,
    UserPatronimic = a.Patronimic,
    UserTookeTestsTotal = a.aggregates != null ? a.aggregates.cnt : 0,
    MinPointsInTest = a.aggregates != null ? a.aggregates.min : 0,
    MaxPointsInTest = a.aggregates != null ? a.aggregates.max : 0,
    AvgPointsInTest = a.aggregates != null ? a.aggregates.avg : 0
})
.ToListAsync();

实体模型参考 #

public class ExaminationModel
{
    public int Id { get; set; }
    public int IdentityUserId { get; set; }
    public int TestId { get; set; }
    public DateTime StartDateTime { get; set; }
    public DateTime? FinishDateTime { get; set; }
    public bool? ExamPassed { get; set; }
}
public class UserAnswer
{
    public int Id { get; set; }
    public int ExamId { get; set; }
    public int QuestionId { get; set; }
    public int AnswerId { get; set; }
}
public class AnswerModel
{
    public int Id { get; set; }
    public string Text { get; set; } = string.Empty;
    public int QuestionId { get; set; }
    public int Points { get; set; }
}
public class UserModel
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Surname { get; set; } = string.Empty;
    public string? Patronimic { get; set; }
    public DateTime BirthDate { get; set; }
    public string Email { get; set; } = string.Empty;
}

重构后的查询方案 #

按照目标SQL的CTE结构拆分查询，让EF Core能正确翻译：

步骤1：计算每场考试的用户得分 #

关联三张表，按用户ID和考试ID分组计算单场总分：

var examScores = _db.Examinations
    .Where(e => e.FinishDateTime != null && e.ExamPassed != null)
    .Join(_db.UserAnswers, e => e.Id, ua => ua.ExamId, (e, ua) => new { e.UserId, e.Id, ua.AnswerId })
    .Join(_db.Answers, x => x.AnswerId, a => a.Id, (x, a) => new { x.UserId, x.Id, a.Points })
    .GroupBy(x => new { x.UserId, x.Id })
    .Select(g => new
    {
        g.Key.UserId,
        ExamScore = g.Sum(x => x.Points)
    });

步骤2：聚合用户的考试统计数据 #

基于单场得分，按用户ID聚合统计核心指标：

var userStats = examScores
    .GroupBy(s => s.UserId)
    .Select(g => new
    {
        UserId = g.Key,
        ExamCount = g.Count(),
        MinScore = g.Min(s => s.ExamScore),
        MaxScore = g.Max(s => s.ExamScore),
        AvgScore = g.Average(s => s.ExamScore)
    });

步骤3：关联用户表获取完整信息 #

将统计数据与用户表关联，映射到目标模型：

var result = _db.AppUsers
    .Join(userStats, u => u.UserId, stats => stats.UserId, (u, stats) => new UserExamsTotalModel
    {
        UserId = u.UserId,
        UserName = u.Name,
        UserSurname = u.Surname,
        UserPatronimic = u.Patronimic,
        UserTookeTestsTotal = stats.ExamCount,
        MinPointsInTest = stats.MinScore,
        MaxPointsInTest = stats.MaxScore,
        AvgPointsInTest = stats.AvgScore
    })
    .ToListAsync();

完整链式调用代码 #

如果需要合并为单链式查询：

var result = _db.AppUsers
    .Join(
        _db.Examinations
            .Where(e => e.FinishDateTime != null && e.ExamPassed != null)
            .Join(_db.UserAnswers, e => e.Id, ua => ua.ExamId, (e, ua) => new { e.UserId, e.Id, ua.AnswerId })
            .Join(_db.Answers, x => x.AnswerId, a => a.Id, (x, a) => new { x.UserId, x.Id, a.Points })
            .GroupBy(x => new { x.UserId, x.Id })
            .Select(g => new { g.Key.UserId, ExamScore = g.Sum(x => x.Points) })
            .GroupBy(s => s.UserId)
            .Select(g => new
            {
                UserId = g.Key,
                ExamCount = g.Count(),
                MinScore = g.Min(s => s.ExamScore),
                MaxScore = g.Max(s => s.ExamScore),
                AvgScore = g.Average(s => s.ExamScore)
            }),
        u => u.UserId,
        stats => stats.UserId,
        (u, stats) => new UserExamsTotalModel
        {
            UserId = u.UserId,
            UserName = u.Name,
            UserSurname = u.Surname,
            UserPatronimic = u.Patronimic,
            UserTookeTestsTotal = stats.ExamCount,
            MinPointsInTest = stats.MinScore,
            MaxPointsInTest = stats.MaxScore,
            AvgPointsInTest = stats.AvgScore
        }
    )
    .ToListAsync();

关键优化点 #

• 移除Include，改用直接Join关联表，降低EF Core翻译难度
• 拆分嵌套查询为分步聚合，逻辑与目标SQL的CTE结构对齐
• 简化中间对象结构，避免不必要的嵌套层级

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