如何用Entity Framework Core 6获取每个Id的最新记录？

问题背景 #

MSSQL数据库中有Readings表，主键为Id和Updated，存储各实体（如A、B、C）带时间戳的测量值。需求是：给定一个日期，获取每个Id在该日期前的最新一条记录。示例：

• 指定2023-01-14时，仅返回A、B的最新记录
• 指定2023-01-18时，返回A、B、C的最新记录

原始SQL实现 #

最初实现需求的SQL语句如下：

SELECT * FROM Readings r
WHERE EXISTS (
  SELECT NULL
  FROM Readings r2
  WHERE r.Id = r2.Id
  AND r2.Updated <= '2023-01-18' -- 替换为目标查询日期
  HAVING r.Updated = MAX(r2.Updated)
)

EF Core 6 实现尝试 #

无法直接将上述SQL转换为EF Core的Lambda表达式，曾尝试用.Any()仅过滤日期，但无法结合Updated的最大值筛选每个Id的最新记录。

经建议，使用以下EF Core代码实现需求：

var history = context.Readings
   .GroupBy(r => r.Id)                                      
   .Select(g =>
      g.OrderByDescending(r => (DateTime?)r.Updated)
      .FirstOrDefault()
   ).ToList();

该代码生成的SQL基于ROW_NUMBER() OVER()实现，但未生成带HAVING子句的语句。

业务限制说明 #

由于业务需要支持随时更新任意日期的数据，无法使用MSSQL的时态表AS OF查询——主表存储当前有效数据，历史表仅用于审计。

性能问题与优化需求 #

当前生成的SQL在包含65k行、235列的数据集上执行耗时约15秒，其中LEFT JOIN内的ROW_NUMBER()部分耗时占比最高。现有聚集索引为(Id ASC, Updated ASC)，寻求可行的性能优化方案。

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