Alternative to avoid calculating the total record count when performing pagination

Avoid calculating the total record count by using cursor-based pagination, also known as keyset pagination. This method retrieves the next page of results by filtering on the last seen value of a unique, indexed column instead of executing a heavy COUNT(*) query. It significantly improves performance for large datasets in Entity Framework Core.

The Performance Problem with Offset Pagination

In traditional web applications, pagination is often implemented using offset and limit parameters. For example, to get the second page of 10 items, you might execute a SQL query like SELECT * FROM Users ORDER BY Id OFFSET 10 LIMIT 10. While this works fine for small tables, it becomes problematic as data grows. To display a "Page X of Y" footer or enable random access to any page, the database must calculate the total number of rows matching the criteria. In Entity Framework Core, this triggers a separate query such as context.Users.Count().

The COUNT(*) operation requires the database engine to scan through all matching records, which can be extremely slow on large tables without proper indexing or when complex joins are involved. As the offset increases (e.g., going to page 1000), the database still has to skip over thousands of rows before returning the requested slice. This dual-query approach—counting everything and then fetching a slice—is the primary bottleneck in high-traffic applications dealing with millions of records.

Understanding Keyset Pagination

Keyset pagination solves this by eliminating the need for a total count entirely. Instead of asking "what is the 10th item?", you ask "give me the next 10 items after ID 50". This approach relies on a deterministic ordering, typically based on a unique index like a primary key or a timestamp. The client sends the value of the last item from the previous page (the "cursor") along with the desired page size.

This method transforms the query from an offset-based skip into a range-based filter. The database can use an index to jump directly to the row where the key exceeds the cursor value and fetch only the required number of rows. There is no scanning of preceding rows, and no calculation of the total dataset size. This makes the query execution time constant regardless of how deep into the dataset you are navigating.

Implementing Keyset Pagination in EF Core

To implement this in C# using Entity Framework Core, you need to modify your LINQ queries to accept a cursor value. Assume you have a User entity with an Id property that serves as the unique identifier. Here is how you structure the query:

var pageSize = 10;
int lastSeenId = 0; // This comes from the previous page's last item

var users = await context.Users
    .Where(u => u.Id > lastSeenId)
    .OrderBy(u => u.Id)
    .Take(pageSize)
    .ToListAsync();

If you are sorting by multiple columns, such as CreatedDate and then Id, the logic becomes slightly more complex but remains efficient. You must ensure that the combination of sort keys is unique to avoid inconsistent ordering. The query would look like this:

var users = await context.Users
    .Where(u => 
        u.CreatedDate > lastSeenDate || 
        (u.CreatedDate == lastSeenDate && u.Id > lastSeenId))
    .OrderBy(u => u.CreatedDate)
    .ThenBy(u => u.Id)
    .Take(pageSize)
    .ToListAsync();

Notice that we removed any reference to Count(). The application now only knows whether there are more pages if the returned list contains exactly pageSize items. If it returns fewer, you know you are at the end. This simplifies the UI logic as well, as you typically show a "Next Page" button rather than a full page counter.

Handling Non-Unique Sort Keys

A common mistake is using a non-unique column as the sole cursor, such as CreatedAt without including the primary key. If two records have the same creation timestamp, the query might skip one or return duplicates depending on the implementation. Always append the primary key to your cursor condition to guarantee uniqueness. For example, if you sort by Name, you should order by Name then Id and include both in your WHERE clause logic.

When to Use Which Approach

Keyset pagination is ideal for infinite scroll interfaces, real-time feeds, or large admin dashboards where users browse sequentially. It is not suitable if you strictly require displaying "Page 5 of 50" because the total count is unknown. However, many modern applications have moved away from showing exact page counts, favoring simpler navigation controls. If you must support random page access, consider caching the total count periodically rather than calculating it on every request, or use a hybrid approach where keyset pagination is used for sequential browsing and offset pagination is disabled for deep paging.

For more insights on optimizing database interactions in .NET, you might find our guide on Entity Splitting in EF Core helpful for understanding complex entity mappings that can impact query performance.

Frequently Asked Questions

Can I use keyset pagination with string columns?

Yes, you can use string columns as cursors as long as they are part of a unique index or combined with a primary key. Ensure the string comparison is consistent across cultures and collations to avoid unexpected ordering issues in the database.

Does this work with complex joins in EF Core?

It works, but you must ensure the join conditions do not create duplicate rows that break the uniqueness of the cursor. If joins cause duplication, you may need to use Distinct() or restructure the query to maintain a one-to-one relationship between the result set and the underlying table.

How do I handle descending order with keyset pagination?

For descending order, you change the comparison operator. Instead of u.Id > lastSeenId, you use u.Id < lastSeenId. The logic remains the same: you are looking for values that come before the cursor in the specified sort direction.

Is keyset pagination supported by all databases?

Most relational databases support the underlying SQL syntax required for keyset pagination. However, some older or specific NoSQL databases may have limitations on complex range queries. Always test your generated SQL to ensure it utilizes indexes effectively.

What if my data is deleted while paginating?

If a record is deleted between requests, the cursor might skip over a gap, causing a slight inconsistency in the sequence. This is generally acceptable for most use cases. If strict continuity is required, you may need to implement a tombstone system or re-index frequently.