APIs and data-heavy web apps always need pagination, but few developers get it right…
In this guide, I’ll walk you through how to implement pagination in .NET, covering both offset-based and keyset (cursor-based) approaches. You’ll learn when to use each method and get clean, production-ready C# code using .NET and EF Core (but easily adaptable to Dapper or other stacks).
What Is Pagination?
Pagination is the process of splitting a large dataset into smaller, manageable chunks, commonly called pages. Instead of fetching thousands or millions of records in a single query, you only retrieve the specific slice of data required for the current view. This reduces memory usage, improves query performance, and provides a better user experience.
There are many ways to implement pagination, but in this article, we’ll focus on the two most common strategies: Offset Pagination and Keyset Pagination. Offset is simple and widely used, but it can become slow on deep pages. Keyset, on the other hand, is designed for efficiency, especially with large datasets.
What Is Offset Pagination?
Offset pagination is the simplest and most common way to implement pagination in .NET and SQL-based systems. It uses an offset (the number of rows to skip) combined with a limit (the number of rows to return) to fetch a specific “page” of results.
This is the visualization using SQL:
SELECT *
FROM Users
WHERE IsActive = 1
ORDER BY Id
OFFSET (@pageNumber - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;
If you need to easily navigate to any page of results (e.g. page 3, 10, etc.) or go back to page, OFFSET is simpler. It allows you to directly specify the page and number of items per page.
This is the visualization using EF:
var paginatedData = await context.Users
.AsNoTracking()
.Where(u => u.IsActive)
.OrderBy(u => u.Id)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToListAsync();
You can easily implement offset pagination when you need straightforward page navigation and the ability to jump to any page in a sorted dataset.
Skip((pageNumber – 1) * pageSize) skips the records from previous pages.
Take(pageSize) fetches only the number of records for the current page.
ToListAsync() executes the query asynchronously and returns the paginated data.
What Is Keyset Pagination?
Keyset pagination, also known as cursor-based pagination, is a technique that retrieves the next set of records by using a reference to the last row from the previous page instead of skipping a fixed number of rows. This approach avoids the performance penalty caused by large OFFSET values because the database doesn’t need to scan all skipped rows.
With keyset pagination, you filter results based on a column (or a combination of columns) that is indexed and guarantees a stable order.
This is the visualization using SQL:
SELECT TOP (@pageSize) *
FROM Products
WHERE Id > @lastId
ORDER BY Id;
This query fetches the next pageSize products where Id is greater than lastId. Because it uses an indexed column (Id), the database can efficiently seek the starting position, making this approach scale much better for large datasets.
This is the visualization using EF:
var pagedResults = await context.Products
.AsNoTracking()
.Where(p => p.Id > lastId)
.Take(pageSize)
.ToListAsync();
Use Case: You need high performance on large data sets, do you want to have “more data” buttons, automatic loading when scrolling the page.
Conclusion
Pagination is more than just splitting data into pages…
It’s about doing it efficiently.
Offset pagination is simple and works for small datasets, but it slows down as the OFFSET grows.
Keyset pagination, on the other hand, avoids this overhead by using indexed columns to seek directly to the next set of rows, making it the better choice for APIs with deep paging or infinite scrolling.
When designing your pagination strategy in .NET, start by identifying your performance requirements. If you need predictable query times and scalability, keyset pagination is the way to go.
Thank you for reading.
See you next time!
