Efcore: View offset not working as expected

[bpaczkowski]

I added a view for 2 tables I need to query as one, and I need pagination on this view. The problem with EF Core is the OFFSET part of the query is on the main query, and not on the subquery where the view is being selected. See below for an example:

This is EF generated query:

SELECT t.*
FROM (
    SELECT f."Id", f."CreatedAt"
    FROM public."MyView" AS f
    ORDER BY f."CreatedAt" DESC
    LIMIT 10
) AS t
ORDER BY t."CreatedAt" DESC
OFFSET 20;

This is what it should have generated (or without using a subquery at all, don't know what the actual expected behavior is):

SELECT t.*
FROM (
    SELECT f."Id", f."CreatedAt"
    FROM public."MyView" AS f
    ORDER BY f."CreatedAt" DESC
    LIMIT 10
    OFFSET 20
) AS t;

As you can see, it also orders by the same column twice for some reason.

Further technical details

EF Core version: 2.1.0
Database Provider: Npgsql.EntityFrameworkCore.PostgreSQL
Operating system: Windows 10

[smitpatel]

Can you also post the linq query and relevant pieces of model where you have defined the view?

[bpaczkowski]

View class:

public class File
{
    public Guid? Id { get; set; }
    public DateTime? CreatedAt { get; set; }
}

OnModelCreating configuration:

modelBuilder.Query<File>().ToView("MyView")

Query:

_dbContext.Files.OrderByDescending(f => f.CreatedAt)
                .Take(limit)
                .Skip(offset)
                .ToListAsync();

[smitpatel]

It is the ordering of Take & Skip in your linq query which causes the subquery. The expected translation you posted will not match with the results of linq query posted.

When you have Limit/Offset both at same level in SQL then it skips offset rows and takes next limit rows.
In your linq query, you are taking limit rows first and then (out of those limit rows), skipping offset no of rows.

[bpaczkowski]

I see your point, but I don't think it should matter which order they go. The query executes as a whole, and not the individual parts of it, so you're not actually limiting rows first and then skipping rows from the result, you're doing them both at the same time.

[smitpatel]

Linq does not work that way. If you have Take before Skip then you have to apply take on the Enumerable before you can start skipping elements. Order of operations _does matter_ in Linq. The SQL generated is just translation to match what linq would produce on client side.