Pagination in a .NET with EF Core

Introduction

Pagination allows you to retrieve a large number of records split into pages instead of returning all the results at once. This is especially useful in scenarios where you need to retrieve a large number of records. In this article, I present how to implement OffSet and Keyset pagination in a .NET 8 Web API using Entity Framework Core.

What is Pagination in .NET?

Pagination is the process of splitting data into pages, and it can be used in scenarios where instead of retrieving all the data at once, you split them into small bunks and retrieve them per page. This makes the performance of your app better, as it’s not necessary to retrieve all the data at once, and it also allows the client to navigate between the data.

There are two ways to implement pagination: the Offset pagination and the Keyset pagination. Each approach has pros and cons and can be used in different scenarios. I’m going to present both approaches and also demonstrate how to implement them.

Offset Pagination

The Offset pagination requires two values as input: the page number and the page size, and it will use this information to query the data. It uses the Skip method (FETCH NEXT/OFFSET in SQL) to return the data. This approach supports random access pagination, which means the user can jump to any page he wants.

Behind the scenes, the OFFSET specifies the first record position, and the LIMIT / FETCH NEXT specifies the number of records you want to fetch. For example, if your database contains 500 records, and you request the records for page number 4 and page size 100, the records from position 301 up to 400 will be returned.

In the code below, there is an example of a query using the Skip method (I will explain it in more detail in the next topic)

var products = await _dbContext.Products.AsNoTracking()
    .OrderBy(x => x.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

When a request is made with a page number equal to 4 and a page size equal to 100, the EF Core query will be transcribed in the following SQL query (for MS SQL Server):

SELECT p.Id, p.Name
FROM Products AS p
ORDER BY p.Id
OFFSET 300 ROWS FETCH NEXT 100 ROWS ONLY

The downside of the Offset pagination is that if the database contains, for example, 500 records, and you need to return the records from rows 301 up to 400 (i.e. Skip(300). Take(100)), the database must still process (read) the first 300 entries (even if they are not returned), so the database will look through 300 rows, and skip these rows to reach rows 301 up to 400, and this can create a significant computation load that increases with the number of rows being skipped. The higher the number of rows you need to skip, the higher will be the workload imposed on the database.

Another problem with the Offset pagination is that 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. For example, if an entry is added in the initial rows as the user is moving from page 2 to 3, then the last returned row is going to appear again as the first row on the next page.

Keyset Pagination

The Keyset pagination (also known as seek-based pagination or cursor pagination) is an alternative to the offset pagination, and it uses a WHERE clause to skip rows instead of using an offset (Skip). In this approach, the client knows the last searched element and uses it as a filter in the Where condition.

The keyset pagination requires two properties as input: a reference value (which can be some sequential identifier for the last returned value) and the page size.

For example, assuming the reference is the last returned ID and your database contains 500 records, when you make a request with a reference value equal to 300 and a page size equal to 100, it will filter the records that only have an Id bigger than 300 and will take the next 100 records:

This kind of pagination is more performant than offset pagination because when a query is executed, the database does not need to process all the previous rows before reaching the row number that needs to be retrieved.

In the code below, there is an example using the Where condition filtering by the reference:

var products = await _dbContext.Products.AsNoTracking()
   .OrderBy(x => x.Id)
   .Where(p => p.Id > reference) // in this example, reference is the "lastId"
   .Take(pageSize)
   .ToListAsync();

When a request is made with a reference value equal to 300 and a page size equal to 100, the EF Core query will be transcribed in the following SQL query (for MS SQL Server):

SELECT TOP(100) p.Id, p.Name
FROM Products AS p
WHERE p.Id > 300
ORDER BY p.Id

For the keyset pagination, the key must be some sortable property such as a sequential Id, or a date time property that you can compare, etc, in this example is the Id property. With the Where condition, instead of going through all the initial rows to skip them, it jumps directly to the searched row. Assuming an index is defined on ID, this query is very efficient and also isn’t sensitive to any concurrent changes happening in lower ID values.

As with any other query, proper indexing is vital for good performance: make sure to have indexes in place that 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. (Microsoft Docs)

This kind of pagination can be considered in scenarios where you do not need to jump to a random page, but instead, you only need to access the previous and the next page, and also in scenarios where you want to create an endless scroll content application, because it is efficient and solves offset paging problems.

The downside of the Keyset pagination is that it does not support random access, where the user can jump to any specific page. This approach is appropriate for pagination interfaces where the user navigates forwards and backward, which means it’s only possible to execute next/previous page navigation.

Offset and Keyset pagination - Pros & Cons

Below, you can see a comparison table with the pros and cons for each approach:

Offset pagination is recommended for cases when you don’t have a large amount of data, or when you need to have the possibility to jump to a specific page. Thinking about UI, it can be used to present data with the possibility to navigate to not only the previous or next pages but also jump to a specific page.

Keyset pagination is recommended for cases where you have a large amount of data and you need to prioritize performance or when you can not miss or show duplicated items. Thinking about UI, it can be a good fit for cases where you have infinite scroll, such as showing posts on social media, etc.

Conclusion

Pagination allows you to retrieve data split into pages, which is a great way to retrieve data in cases where you have a large amount of records. It improves website performance by reducing the number of data that needs to be loaded at once, and it also enhances user experience by providing an easy way to navigate between a large number of data. The Offset pagination can be used when jumping to a specific page is a requirement, and the Keyset pagination can be used when jumping to a specific page is not a requirement, and data consistency or performance needs to be prioritized.