时间戳

首页 > 解决方案 > C# Linq - EF，选择具有最大值的分组数据

c# - C# Linq - EF，选择具有最大值的分组数据

问题描述

我有一个多对多表，其中存储了 UserId、SectionId、Attempt、Qualification 和时间戳。因此，用户可以按部分进行 N 次尝试，但是当我评估每个部分时，只需要在尝试是最大值的地方取值。我尝试通过 Attempt 使用键 UserId 和 SectionId ordering desc 进行连接

 var result = await (from exam in db.exams
                            join section in db.sections on exam.SectionId equals section.Id
                            join groupedTable in (from exam2 in db.exams
                                                  group exam2 by new { UserId = exam2.UserId, SectionId = exam2.SectionId, Attempt = exam2.Attempt } into grouped
                                                  select new { UserId = grouped.Key.UserId, SectionId = grouped.Key.SectionId, LastAttempt = grouped.Max(x => x.Attempt) })
                                                  .OrderByDescending(x => x.LastAttempt)
                                                  .Select(x => new
                                                  {
                                                     UserId = x.UserId,
                                                     SectionId = x.SectionId,
                                                     LastAttempt = x.LastAttempt
                                                  })

                            on new { UserId = exam.UserId, SectionId = section.Id }
                            equals new { UserId = groupedTable.UserId, SectionId = groupedTable.SectionId }
                            select exam)
                            .Distinct()
                            .ToListAsync();

也试过这个

   var result = await (from exam in db.exams
                            join section in db.sections on exam.SectionId equals section.Id
                            select new
                            {
                                UserId = exam.UserId,
                                SectionId = exam.SectionId,
                                Attempt = exam.Attempt
                            })
                            .GroupBy(x => new
                            {
                                x.UserId,
                                x.SectionId,
                                x.Attempt
                            })
                            .Select(x => new
                            {
                                UserId = x.Key.UserId,
                                SectionId = x.Key.SectionId,
                                Attempt = x.Max(x => x.Attempt)
                            })
                            .ToListAsync();

但结果是一样的：

{ UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 8, Attempt = 1 }
{ UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 10, Attempt = 1 }
{ UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 9, Attempt = 1 }
{ UserId = {e56e13b6-28e5-46b2-bd78-f975fd96e1a7}, SectionId = 10, Attempt = 2 }

在这个示例中，我需要排除 SectionId = 10 和 Attempt = 1 的位置

标签： c#linqentity-framework-core

解决方案

您继续按尝试分组，这意味着每个用户+部分+尝试都成为自己的组（可能大小为 1）

从操作的分组部分移除Attempt；仅按用户和部分分组

使用 SQL 解释可能更清楚。你正在这样做：

    SELECT user, section, attempt, MAX(attempt) --max is useless here 
    FROM ...
    GROUP BY user, section, attempt --always a group size of 1

你需要这样做：

    SELECT user, section, MAX(attempt)
    FROM ...
    GROUP BY user, section

推荐阅读