Holistic end-to-end pagination feature

[roji]

EF currently provides various primitives for performing pagination (Take/Skip as well as keyset pagination - see docs). However, we regularly see a need for a higher-level, more end-to-end solution that would provide easy, efficient pagination capabilities without having to manually work everything together each time.

The basic API could be something like a terminating ToPageAsync() call (proposed by @AndriySvyryd in #24513, as well as by @michaelstaib in his work on GraphQL EF integration). The query would return a page containing the rows in the page, an easy means for fetching the next/previous page, and some additional data (HasNextPage, HasPreviousPage, TotalCount).

Following is a summary of general notes/requirements (thanks @michaelstaib for the useful discussion!):

• Paginating queries frequently need to know whether there's a previous and next page (for UI purpoess). This can be done by selecting out an additional row on each side, or something nicer is probably possible with window functions.
• Similarly, it's frequently desired to know the total number of rows when performing a paginating query, without doing an additional database query just for that. Doing this efficiently depends on window function support.
• It should be possible to extract some sort of "cursor" or "pagination/continuation token", which would allow fetching the next (or previous page), with a repeated invocation of ToPageAsync(). The actual contents/format of the token should be opaque, and will vary across databases/techniques (Cosmos has a continuation token, keyset pagination has its keyset, offset pagination has the numeric position). It should ideally be easy to get a string representation (or similar) to transfer the token to the client for disconnected scenarios.
• For keyset pagination, it's necessary to specify the sorting keys. Instead of requiring these to be specified explicitly, an interesting idea would be for the ToPageAsync() operator to infer the keys by examining the IOrderedQueryable it's built on top of.
• Cosmos support for pagination is tracked by #24513. We ideally would arrive at an abstract API shape which can work for both relational and Cosmos.
• Note that paging isn't only a terminating/top-level concern - we should support it in filtered includes as well, to allow paging over dependent collections (e.g. context.Blogs.Include(b => b.Posts.OrderBy(...).ThenBy(...).Page(...)). This has particular importance to GraphQL (/cc @michaelstaib).
• We'd need to think about whether the API buffers the entire page, or whether it allows streaming. Streaming is probably not very important (since we're paginating anyway), and may complicate the API (additional data like TotalCount/HasNextPage won't be available before reading the first row).

Prior art: MR.EntityFrameworkCore.KeysetPagination, Pagination.EntityFrameworkCore.Extensions

/cc @michaelstaib @mrahhal @SitholeWB

[mrahhal]

If done, would this be a part of Microsoft.EntityFrameworkCore or some kind of additional official EF Core pagination package? I'm asking because this seems to be the first "end-to-end" api in EF Core that is this high level (requires configuration params such as whether we need the additional info, and returns a custom model).

A few quick thoughts, and raising some questions. If this single abstract api is to support the different pagination techniques, how would we differentiate between wanting an offset or a keyset query (especially in the first call where you don't have a continuation token)? In addition, these two techniques feel a bit different from Cosmos, which is an altogether different db and has an adapter, and you could just use keyset pagination with it anyway (I'm assuming). How would you call this api with the intention of using keyset pagination, but your adapter happens to be Cosmos? (I'm not really experienced with Cosmos though so I might be missing something)

Opaque tokens are great, it was something I was investigating in KeysetPagination. In the case of keyset pagination, if the keyset includes a string column then this token could be large in size. I think it's ok and that it's the user's responsibility (in the first place, an unbounded string column in the keyset is orthogonal to using keyset pagination for perf), but worth noting especially that this token could end up being a part of an http header.

For offset pagination, the abstract api would also have to support the ability to do random access through specifying a certain page directly (the only advantage to offset), without having a token first. Going back to my prev question about how to choose what technique we want in this api, it seems to me that reconciling these different techniques needs either some kind of an involved options object as a param, or more than one api. In the case of MR.AspNetCore.Pagination I have different apis, but in this case I would prefer some kind of options param if possible.