Edit

Pagination

  • Article

Pagination refers to retrieving results in pages, rather than all at once; this is typically done for large resultsets, where a user interface is shown that allows the user to navigate to the next or previous page of the results.

Warning

Regardless of the pagination method used, always make sure that your ordering is fully unique. For example, if results are ordered only by date, but there can be multiple results with the same date, then results could be skipped when paginating as they're ordered differently across two paginating queries. Ordering by both date and ID (or any other unique property or combination of properties) makes the ordering fully unique and avoids this problem. Note that relational databases do not apply any ordering by default, even on the primary key.

Offset pagination

A common way to implement pagination with databases is to use the Skip and Take (OFFSET and LIMIT in SQL). Given a page size of 10 results, the third page can be fetched with EF Core as follows:

var position = 20;
var nextPage = context.Posts
    .OrderBy(b => b.PostId)
    .Skip(position)
    .Take(10)
    .ToList();

Unfortunately, while this technique is very intuitive, it also has some severe shortcomings:

  1. The database must still process the first 20 entries, even if they aren't returned to the application; this creates possibly significant computation load that increases with the number of rows being skipped.
  2. If any updates occur concurrently, your pagination may end up skipping certain entries or showing them twice. For example, if an entry is removed as the user is moving from page 2 to 3, the whole resultset "shifts up", and one entry would be skipped.

Keyset pagination

The recommended alternative to offset-based pagination - sometimes called keyset pagination or seek-based pagination - is to simply use a WHERE clause to skip rows, instead of an offset. This means remember the relevant values from the last entry fetched (instead of its offset), and to ask for the next rows after that row. For example, assuming the last entry in the last page we fetched had an ID value of 55, we'd simply do the following:

var lastId = 55;
var nextPage = context.Posts
    .OrderBy(b => b.PostId)
    .Where(b => b.PostId > lastId)
    .Take(10)
    .ToList();

Assuming an index is defined on PostId, this query is very efficient, and also isn't sensitive to any concurrent changes happening in lower Id values.

Keyset pagination is appropriate for pagination interfaces where the user navigates forwards and backwards, but does not support random access, where the user can jump to any specific page. Random access pagination requires using offset pagination as explained above; because of the shortcomings of offset pagination, carefully consider if random access pagination really is required for your use case, or if next/previous page navigation is enough. If random access pagination is necessary, a robust implementation could use keyset pagination when navigation to the next/previous page, and offset navigation when jumping to any other page.

Multiple pagination keys

When using keyset pagination, it's frequently necessary to order by more than one property. For example, the following query paginates by date and ID:

var lastDate = new DateTime(2020, 1, 1);
var lastId = 55;
var nextPage = context.Posts
    .OrderBy(b => b.Date)
    .ThenBy(b => b.PostId)
    .Where(b => b.Date > lastDate || (b.Date == lastDate && b.PostId > lastId))
    .Take(10)
    .ToList();

This ensures that the next page picks off exactly where the previous one ended. As more ordering keys are added, additional clauses can be added.

Note

Most SQL databases support a simpler and more efficient version of the above, using row values: WHERE (Date, Id) > (@lastDate, @lastId). EF Core does not currently support expressing this in LINQ queries, this is tracked by #26822.

Indexes

As with any other query, proper indexing is vital for good performance: make sure to have indexes in place which correspond to your pagination ordering. If ordering by more than one column, an index over those multiple columns can be defined; this is called a composite index.

For more information, see the documentation page on indexes.

Additional resources