Query: Investigate supporting simple patterns for keyset pagination

[divega]

This can perform better than paging based on OFFSET. For more information, see:

• http://use-the-index-luke.com/no-offset
• https://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/

[erikest]

just read use-the-index-luke and immediately wondered what the implementation in EF/EF Core was and here I am.

As the article mentions, the technique can't really replace skip/take because it can't jump to an exact 'starting point' based on the implicit row number from the sort order - but, it would be an excellent feature for async yield of a large result set, broken up into multiple queries, where you can choose your 'chunk/page size' and keep yielding new chunks.

As in, a KeyPage function that returns an IAsyncEnumerable<>

I'd love to write code like this, for example:

IAsyncEnumerable<IEnumerable<Foo>> pages =  _context.Foos.Where(foo=>foo.Total>20).OrderBy(foo=>foo.Date).KeyPage(pageSize: 100);

await foreach(var page in pages) {
    gridItems.AddRange(page);
}

or

void ShowMoreResults(IAsyncEnumerable<IEnumerable<Foo>> pages) {
    if (await pages.MoveNextAsync())
        gridItems.AddRange(pages.Current);
}

[MoienTajik]

Any news on this topic?

[ajcvickers]

@MoienTajik This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 5.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[mrahhal]

Hey. I have fully implemented keyset pagination for EFCore in a package here.

I took some liberty in designing the package but I imagine there will be certain requirements for how the api should look like in EFCore. I'd be happy if I could help bringing this in.

[roji]

Allowing users to express row value comparisons - which are an important part of keyset pagination - is tracked by #26822.

[roji]

We looked into this, and we can indeed add row value comparisons to make multi-key keyset pagination more efficient (#26822). Beyond that we don't think there's anything we need to build: the building blocks are there, and keyset pagination can be expressed with OrderBy and Where operators.

There may be room for a higher-level pagination API, but at least at this point we think this can be implemented outside of EF Core, over building blocks we provide (see MR.EntityFrameworkCore.KeysetPagination as an example).