将SQL ROW_NUMBER转换为LINQ时出现错误翻译及性能问题

需要转换的SQL逻辑是：筛选出最近1小时内、Location为USA的CountryStaff记录，按StaffId和Location分组后，取每组中最新的一条记录（按Timestamp倒序排序后的第一条）。原SQL代码如下：

SELECT * 
FROM(
    SELECT ROW_NUMBER() OVER (PARTITION BY StaffId,[Location] ORDER BY [Timestamp] DESC) AS rn, 
           StaffId, 
           Location, 
           [Timestamp] 
    FROM CountryStaff 
    WHERE [Timestamp] >= 
            DATEADD(hour,-1, GETDATE())
            AND [Location] = 'USA'
) x  
WHERE x.rn = 1

尝试的LINQ代码及问题 #

第一段代码（返回错误结果） #

编写的LINQ代码：

var date = DateTime.UtcNow.AddHours(-1);
var filteredTransactions = CountryStaff
    .Where(t => t.Location == "USA" && t.Timestamp >= date)
    .OrderByDescending(t => t.Timestamp);

filteredTransactions
    .OrderByDescending(t => t.Timestamp)
    .Skip(0)
    .Take(1)
    .Select(t => new
    {
        StaffId = t.StaffId,
        Location = t.Location,
        Timestamp = t.Timestamp
    }).ToList();

这段代码生成的SQL未按StaffId和Location分区，仅返回整个筛选结果的第一条记录，不符合需求。生成的SQL如下：

DECLARE @p0 NVarChar(1000) = 'USA'
DECLARE @p1 DateTime = '2024-02-28 06:25:22.590'
DECLARE @p2 Int = 0
DECLARE @p3 Int = 1

SELECT [t2].[StaffId], [t2].[Location], [t2].[Timestamp]
FROM (
    SELECT [t1].[StaffId], [t1].[Timestamp], [t1].[Location], [t1].[ROW_NUMBER]
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Timestamp] DESC, [t0].[Timestamp] DESC) AS [ROW_NUMBER], [t0].[StaffId], [t0].[Timestamp], [t0].[Location]
        FROM [CountryStaff] AS [t0]
        WHERE ([t0].[Location] = @p0) AND ([t0].[Timestamp] >= @p1)
    ) AS [t1]
    WHERE [t1].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3
) AS [t2]
ORDER BY [t2].[ROW_NUMBER]

第二段代码（性能极差） #

尝试以下代码后，结果符合要求，但执行耗时从原SQL的0.6ms飙升至5s：

CountryStaff 
    .Where(t => t.Timestamp >= date && t.Location == "USA")
    .GroupBy(t => new { t.StaffId, t.Location })
    .Select(g => g.OrderByDescending(t => t.Timestamp).First())
    .ToList();

正确的LINQ写法（匹配原SQL逻辑且性能优异） #

在Entity Framework Core 8中，可直接使用RowNumber窗口函数实现原SQL的分区逻辑，生成的SQL与原SQL结构几乎一致，能保证原有的高性能：

var date = DateTime.UtcNow.AddHours(-1);

var result = CountryStaff
    .Where(t => t.Location == "USA" && t.Timestamp >= date)
    .Select(t => new 
    {
        t.StaffId,
        t.Location,
        t.Timestamp,
        Rn = EF.Functions.RowNumber().Over(
            partitionBy: new { t.StaffId, t.Location },
            orderBy: t => t.Timestamp descending)
    })
    .Where(x => x.Rn == 1)
    .Select(x => new 
    {
        x.StaffId,
        x.Location,
        x.Timestamp
    })
    .ToList();

该写法完全复刻原SQL的窗口函数分区逻辑，执行性能与原SQL一致。

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