如何在EF Core 3.1中用LINQ实现PostgreSQL的LEAD窗口函数查询？

技术栈与实体类 #

我们使用.NET Core 3.1、Microsoft.EntityFrameworkCore 3.1.9 及 Npgsql 4.1.9，自动生成的TestExecution实体类如下：

[Table("test_execution", Schema = "test")]
public partial class TestExecution
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("test_result")]
    public string TestResult { get; set; }

    [Column("name")]
    public string Name { get; set; }
}

数据库原始数据 #

PostgreSQL数据库中的test.test_execution表数据如下：

需求说明 #

需要返回连续相同test_result值序列中id最小的行，预期输出如下：

原生SQL实现 #

该需求可通过以下PostgreSQL查询实现：

SELECT
    id, 
    test_result, 
    name
FROM (
    SELECT
        id, 
        test_result, 
        name,
        LEAD(test_result) OVER (ORDER BY id DESC) AS next_result
    FROM test.test_execution
    ) s
WHERE
    test_result is distinct from next_result;

EF Core 3.1 LINQ实现方案 #

要在EF Core 3.1中用LINQ生成等价查询，需利用EF Core对窗口函数的支持，结合Lead方法实现，具体代码如下：

var query = context.TestExecution
    .OrderByDescending(t => t.Id)
    .Select(t => new 
    {
        t.Id,
        t.TestResult,
        t.Name,
        NextResult = EF.Functions.Lead(t.TestResult, 1)
            .Over(OrderByDescending(t => t.Id))
    })
    .Where(s => s.TestResult != s.NextResult || s.NextResult == null)
    .Select(s => new TestExecution
    {
        Id = s.Id,
        TestResult = s.TestResult,
        Name = s.Name
    });

var result = await query.ToListAsync();

关键说明 #

1. 窗口函数映射：EF.Functions.Lead对应SQL中的LEAD窗口函数，Over(OrderByDescending(t => t.Id))实现了SQL中OVER (ORDER BY id DESC)的排序规则。
2. 空值兼容处理：SQL中的test_result is distinct from next_result会自动处理空值，LINQ中通过s.TestResult != s.NextResult || s.NextResult == null等价实现，确保最后一行（无后续行）被保留。
3. 实体投影：最后将匿名类型投影回TestExecution实体，适配业务代码的实体使用习惯。

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