Web API Pagination with .NET Core

An informal but comprehensive guide to understanding and implementing efficient pagination strategies in your .NET Core Web APIs.

A few weeks ago, I did a LinkedIn post “.NET Core API Pagination - What is it & how to implement it?” and there was a lot of interest in the topic. Today, we’ll dive deeper into the practicalities of implementing pagination in web APIs with .NET Core. We’ll discuss pagination, why pagination is important, what kinds of pagination are there, how to implement them using .NET Core and what performance implications each pagination type has. Without further ado, let’s start!

Pagination is a design pattern where you (the developer) return only a subset of a larger dataset to make it easier for the user to consume the provided information without being overwhelmed. Let’s consider a simple feed functionality of a fictional social network application.

User’s feed of the fictional social network

This feed shows the user all the posts shared by their network. It would take a significant time to load the user’s feed if the application loaded all the posts. Also, a user could be overwhelmed by all the information in these posts. Therefore, an increased loading time and overwhelming feed would be a bad user experience. Not to forget, loading all that data for different users could slow down the server and result in a large response payload. Implementing pagination is important in such an application because by only returning a subset of data,

1. Pagination will reduce the server load

2. Pagination will improve the feed loading times

3. Pagination will reduce response payloads

4. Pagination will make the feed experience less overwhelming for the user

5. Pagination can prevent your public endpoints from being misused by malicious users.

Now that we understand the importance of pagination, let’s look at the Post class we’ll use in our implementation examples later.

public class Post
{
    public int Id { get; set; }
    public string Content { get; set; }

    public DateTime CreatedOn { get; set; } = DateTime.UtcNow;
}

Types of Pagination

Offset Pagination

This is the simplest of the two pagination types. It depends on two parameters for returning the appropriate page data.

• Page Size: The number of rows to return.

• Offset: The number of rows to skip, which can also be calculated as offset = (current page - 1) * page size i.e. given the current page is 5 and the page size is 10, the offset will be (5-1) * 10 = 40.

Example

public (Post[] data, int count) GetAll(int offset, int pageSize = 10)
{
    var query = _context.Posts
        .OrderBy(x => x.Id);

    return (
        data: query
            .Skip(offset)
            .Take(pageSize)
            .ToArray(),
        count: query.Count()
        );
}

As seen in the example above, offset pagination can be implemented using the .Take() and .Skip() LINQ methods in .NET Core. Always remember to organise your dataset first — in the example above, it is done using the autoincrementing int Id — before you filter on it to return accurate results. We must also return the total count of the records in the base query so that the front end can use this information to calculate the total number of pages.

Keyset Pagination

This type of pagination can be a bit tricky to implement. It also depends on two parameters for returning the appropriate page data:

• Page Size: The number of rows to return.

• Cursor: This represents the last item of the last retrieved page but could be a Guid, a DateTime or something else based on your sorting criteria. For our example, we’ll consider this to be a combination of int & DateTime type.

Example

public (Post[] data, int count) GetAll(
    (DateTime lastDate, int lastId) cursor,
    int pageSize = 10
    )
{
    var query = _context.Posts
        .OrderBy(x => x.CreatedOn)
        .ThenBy(x => x.Id)
        .Where(x => x.CreatedOn > cursor.lastDate || (x.CreatedOn == cursor.lastDate && x.Id > cursor.lastId));

    return (
        data: query
            .Take(pageSize)
            .ToArray(),
        count: query.Count()
        );
}

Keyset pagination is implemented using the .Where() LINQ query in .NET Core. In the example above, our cursor combines CreatedOn and Id properties. It is because the first part of the where clause (x.CreatedOn > cursor.lastDate) will only fetch records where CreatedOn is greater than the cursor but what if there is more than one record with the same CreatedOn value? That is why we need the second part (x.CreatedOn == cursor.lastDate && x.Id > cursor.lastId) of the where clause as well.

In real-world scenarios, your sorting criteria might involve more than a few simple properties and therefore require a more complex where clause. This is why keyset pagination can be trickier to implement because a logical error in the where clause could result in inaccurate page data.

Gotchas

When implementing keyset pagination, here are a few gotchas to be careful of,

1. Unique Sorting Key: Make sure that the column used for sorting is unique, to prevent unpredictable results.

2. GUID Performance: GUIDs are non-sequential and will impact the performance if used as the cursor value. Try using sequential types e.g. int or DateTime instead.

3. Consistent Ordering: Ordering with which the data is sorted should remain consistent between requests or can cause issues with data sequence.

4. Initial Page Load: Handle what happens when a cursor value is not provided. The example above does not do that, I’ll let you figure it out. You can check the GitHub repository for the final solution.

5. Filtering Data: It can be complex filtering data alongside keyset pagination. Remember to include the appropriate filtering criteria in your where method.

6. Database Indices: Make sure that the columns being used in the where method are indexed by the database. Otherwise, it might have a significant impact on performance.

Offset vs Keyset

Benchmarks

Let’s run a few benchmarks and compare common scenarios, and how different pagination types can impact the performance.

Benchmark results in Windows Terminal

Here are some of the findings that can be deduced from the results above:

• When fetching starting pages, the performance difference between offset & keyset pagination is negligible.

• When fetching middle pages, the performance difference between offset & keyset pagination becomes visible. Keyset pagination is ~4x faster.

• When fetching ending pages, the performance difference between offset & keyset pagination becomes quite significant. Keyset pagination is ~337x faster.

Pros & Cons

Now that we understand both pagination techniques, let's review their pros and cons.

1. Offset pagination is slower because when you use .Skip() & .Take() methods, the database will fetch all the records before discarding the ones to skip and returning the ones to take.

2. Keyset pagination is faster because the cursor helps the database identify exactly which X rows to take and therefore no extra data is fetched in the process.

3. Offset pagination can be used 90% of the time due to its simplicity.

4. Keyset pagination should be used only if the need arises because despite the performance benefits, added complexity of filtering and sorting might not be worth it.

5. If the application users never go past the first 5 pages, offset pagination would be a better choice due to negligible performance difference and ease of implementation.

6. If users are constantly navigating to the middle or end pages, you would benefit from the performance of keyset pagination given that your dataset is large enough.

Related Concepts

Infinite Scrolling

Some people might confuse Infinite Scrolling as an alternative to pagination because seemingly it does not have any pages, right? Unfortunately, that is not the case. Infinite Scrolling is another way of implementing pagination, mostly used in scenarios where the total number of records might not be fixed (a social network’s feed is a good example). Behind the scenes, it also uses either of the pagination types mentioned above. Whenever a user scrolls close to the end, the front end will fetch the next page and append it at the end, making it seem like an infinitely scrolling single page.

However, one thing to note is that Infinite Scroll can be faster than regular pagination. Because — in the examples above — the total count of records returned, impacts API performance and adds to the server load. That total count is unnecessary with infinite scrolling since we don’t display the total number of pages anywhere, hence omitting that can make the API slightly faster.

Standard Paginated Response

The public Web API endpoints should always be paginated. Therefore, when paginating your responses, use a standard response model so that the API consumers can implement a generic pagination logic.

Below is an example of a simple paginated response model for reference.

public record PaginatedResponse<T>(
    T[] Data,
    int Count
    );

HATEOAS

HATEOAS, an acronym for Hypermedia as the Engine of Application State, is a design pattern based on REST principles. The concept involves embedding hypermedia links (URLs) within the API response body. These links lead to other related resources or actions, thus enabling the consumer to navigate between different states of the application. For pagination, these API responses involve URLs to the next, previous, first and last pages. Below is an example of a HATEOAS API response:

{
  "count": 100,
  "next": "http://example.com/platforms?page=4",
  "previous": "http://example.com/platforms?page=2",
  "results": [
    ...
  ]
}

Conclusion

We looked at pagination in detail, understanding its significance and the different types - Offset and Keyset. It’s clear that pagination isn’t just a fancy feature, but a necessity for improving user experience and web API performance. We also compared offset and keyset pagination, giving you a general guideline on when to use which. Remember this, always choose the right pagination for your application, and make an informed decision based on your application users’ behaviour and Web API performance stats in mind.

PS. The complete solution with the benchmarks is available on GitHub.