如何用LINQ从RepairStatus表获取各RepairId的最新记录

场景说明 #

现有RepairStatus实体类定义：

public class RepairStatus
{
    [Key]
    public int RepairStatusId { get; set; }

    [Required]
    [ForeignKey("Repair")]
    public int RepairId { get; set; }

    public virtual Repair? Repair { get; set; }

    [Required]
    [ForeignKey("Status")]
    public int StatusId { get; set; }

    public virtual Status? Status { get; set; }

    [Required]
    [DataType(DataType.DateTime)]
    public DateTime DateTime { get; set; }
}

RepairStatus表样本数据：

RepairStatusId    RepairId    StatusId    DateTime 
    1                5          1         2022/05/27 18:20:00
    2                5          2         2022/05/28 18:20:00
    3                4          3         2022/06/15 18:20:00
    4                5          4         2022/06/17 18:20:00
    5                5          5         2022/06/18 18:20:00
    6                6          3         2022/07/25 18:20:00
    7                6          1         2022/08/25 18:20:00

需求：通过LINQ查询，获取每个RepairId对应的最新记录（去重RepairId，保留各RepairId最后更新的记录），预期输出：

RepairStatusId    RepairId    StatusId    DateTime 
    3                4          3         2022/06/15 18:20:00
    5                5          5         2022/06/18 18:20:00
    7                6          1         2022/08/25 18:20:00

解决方案（无需新增辅助属性） #

以下三种方法均可实现需求，可根据项目使用的EF版本和性能要求选择：

方法1：分组后筛选最新记录

先按RepairId分组，再在每个分组内按DateTime降序排序，取第一条记录：

var latestRepairStatuses = dbContext.RepairStatus
    .GroupBy(rs => rs.RepairId)
    .Select(group => group.OrderByDescending(rs => rs.DateTime).FirstOrDefault())
    .Where(rs => rs != null)
    .ToList();

方法2：子查询匹配最新时间

先通过子查询获取每个RepairId对应的最大更新时间，再关联原表获取完整记录：

// 先获取每个RepairId的最新时间
var latestStatusDates = dbContext.RepairStatus
    .GroupBy(rs => rs.RepairId)
    .Select(group => new { RepairId = group.Key, LatestDate = group.Max(rs => rs.DateTime) });

// 关联原表筛选出对应记录
var latestRepairStatuses = dbContext.RepairStatus
    .Join(latestStatusDates,
        repairStatus => new { repairStatus.RepairId, repairStatus.DateTime },
        latestDate => new { latestDate.RepairId, latestDate.LatestDate },
        (repairStatus, latestDate) => repairStatus)
    .ToList();

方法3：EF Core窗口函数（EF Core 3.0+适用）

利用ROW_NUMBER()窗口函数，按RepairId分区、DateTime降序排序，取每个分区的第一条记录：

var latestRepairStatuses = dbContext.RepairStatus
    .Select(rs => new 
    {
        RepairStatus = rs,
        RowNumber = EF.Functions.RowNumber()
            .Over(PartitionBy(rs.RepairId).OrderByDescending(rs.DateTime))
    })
    .Where(result => result.RowNumber == 1)
    .Select(result => result.RepairStatus)
    .ToList();

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