Question 89195 in PostgreSQL Question posted in
The official documentation can be found here.

EF Core 3.1 PostgreSQL LEAD window function

We are using .NET Core 3.1, Microsoft.EntityFrameworkCore 3.1.9 and Npgsql 4.1.9. We have the following simple TestExecution scaffolded class:

[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; }
}

Data in the PostgreSQL database looks like this:

id test_result name
1 OK test 1
2 OK test 2
3 ERROR test 3
4 ERROR test 4
5 OK test 5
6 OK test 6
7 WARNING test 7

We would like to return rows with the smallest id for the sequence of the same consecutive test_result values. Our expected output is:

id test_result name
7 WARNING test 7
5 OK test 5
3 ERROR test 3
1 OK test 1

This can be achieved with the following SQL query:

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

How can we produce this SQL query by EF Core 3.1 and Linq without writing any raw SQL?

Tags:

2

Answers


  1. 0

    EF Core doesn’t currently support window functions – that’s tracked by this issue.

    Login or Signup to reply.
  2. 0

    You can use linq2db.EntityFrameworkCore and window functions usage Window functions. Install version 3.x. (note that I’m one of the creators)

    var windowedQuery = 
    from te in context.test_execution
    select new 
    {
        te.id,
        te.test_result,
        te.name,
        next_result = Sql.Ext.Lead(te.test_result)
            .Over()
            .OrderByDesc(te.id)
            .ToValue()
    };

var query = 
    from w in windowedQuery
    where w.test_result.IsDistinctFrom(w.next_result)
    select new
    {
        w.id,
        w.test_result,
        w.name
    };

var result = await query.ToListAsyncLinqToDB(); // execute query via LINQ to DB
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search