如何用LINQ获取各学员最高Attempt值的唯一记录

原始数据 #

Learner     | AssesmentId | Attempt 
------------------------------------
Parker      |     1       |    1    
Parker      |     1       |    2    
Stark       |     1       |    1    
Rogers      |     1       |    1    
Rogers      |     1       |    2    
Parker      |     1       |    3    

需求 #

基于上述数据，需要获取每个学员对应最高Attempt值的唯一记录，期望结果如下：

Learner     | AssesmentId | Attempt 
------------------------------------
Parker      |     1       |    3    
Stark       |     1       |    1    
Rogers      |     1       |    2    

现有不完整LINQ查询片段 #

from la in _context.LearnerAssessments
where la.AssessmentId == assessmentId
  && learnerIds.Contains(la.LearnerId)
  && la.Attempt == {highest attempt}

解决方案 #

核心思路是先按LearnerId和AssessmentId分组，定位每组的最高尝试值，再关联回原数据集提取完整记录，以下是两种可行的单条LINQ实现方式：

方式一：分组关联法 #

from la in _context.LearnerAssessments
where la.AssessmentId == assessmentId && learnerIds.Contains(la.LearnerId)
join maxAttempt in (
    from la2 in _context.LearnerAssessments
    where la2.AssessmentId == assessmentId && learnerIds.Contains(la2.LearnerId)
    group la2 by new { la2.LearnerId, la2.AssessmentId } into g
    select new { g.Key.LearnerId, g.Key.AssessmentId, MaxAttempt = g.Max(x => x.Attempt) }
) on new { la.LearnerId, la.AssessmentId, la.Attempt } equals new { maxAttempt.LearnerId, maxAttempt.AssessmentId, la.Attempt = maxAttempt.MaxAttempt }
select la;

方式二：GroupBy结合SelectMany #

_context.LearnerAssessments
    .Where(la => la.AssessmentId == assessmentId && learnerIds.Contains(la.LearnerId))
    .GroupBy(la => new { la.LearnerId, la.AssessmentId })
    .SelectMany(g => g.Where(la => la.Attempt == g.Max(x => x.Attempt)))
    .ToList();

说明 #

两种写法都会先筛选出符合指定AssessmentId和LearnerId集合的数据，再按学员+评估维度分组，最终定位到每组内尝试次数最高的记录。如果基于EF Core使用，该逻辑会被翻译成高效的SQL查询，无需在内存中处理全量数据。

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