Slow pagination in EF Core? Try keyset pagination

Your pagination is slow, and as your dataset grows, it's only going to get worse. Let's fix it with keyset pagination.

How keyset pagination works

With keyset pagination, you take the last ID from the bottom of your results, append it to the query string, and when the next request comes in, the database uses that ID as its anchor point, jumping straight to where you left off. Every row before that ID doesn't get looked at by the database.

Here's an example of how the URL would look:

/api/products/keyset?sortBy=Name&sortDirection=Ascending&pageSize=500&lastId=1044997

Getting the anchor record

The first step in the query is to retrieve the anchor record. This is because you need its properties to apply the filter. We only do this when a lastId is provided. On the first page, there is no anchor yet, so we skip straight to the ordering and the number of records we are going to take.

// ProductRepository.cs
public class ProductRepository : IProductRepository
{
	private readonly EFCoreKeysetPaginationDbContext _dbContext;

	public ProductRepository(EFCoreKeysetPaginationDbContext dbContext)
	{
		_dbContext = dbContext;
	}

	public async Task<IReadOnlyList<ProductDto>> GetProductsByKeysetAsync(
		ProductSortBy sortBy, 
		SortDirection sortDirection, 
		int? lastId, 
		int pageSize, 
		CancellationToken cancellationToken)
	{
		var query = _dbContext.Products.AsNoTracking();

		if (lastId is not null)
		{
			var anchor = await query
				.Where(x => x.Id == lastId)
				.Select(x => new ProductDto(x.Id, x.Name,
				x.Price, x.Category.Name))
				.FirstOrDefaultAsync(cancellationToken);

			if (anchor == null)
			{
				throw new NullReferenceException(
					"Anchor record cannot be found");
			}

			query = ApplyKeysetFilter(query, sortBy, sortDirection,
				anchor);
		}

		...
	}
}

Applying the keyset filter

Once we have the anchor, we apply the keyset filter in a separate method. This uses a switch expression to handle each combination of sort column and direction.

To handle duplicate records, if two products share the same name, the sort falls back to the ID.

// ProductRepository.cs
public class ProductRepository : IProductRepository
{
	...

	private static IQueryable<Product> ApplyKeysetFilter(
		IQueryable<Product> query, 
		ProductSortBy sortBy, 
		SortDirection sortDirection, 
		ProductDto anchor)
	{
		return (sortBy, sortDirection) switch
		{
			(ProductSortBy.Name, SortDirection.Ascending) =>
				query.Where(x => x.Name.CompareTo(anchor.Name) > 0 ||
				(x.Name == anchor.Name && x.Id > anchor.Id))
			,
			(ProductSortBy.Name, SortDirection.Descending) =>
				query.Where(x => x.Name.CompareTo(anchor.Name) < 0 ||
				(x.Name == anchor.Name && x.Id < anchor.Id))
			,
			(ProductSortBy.Price, SortDirection.Ascending) => 
				query.Where(x => x.Price > anchor.Price ||
				(x.Price == anchor.Price && x.Id > anchor.Id)),
			(ProductSortBy.Price, SortDirection.Descending) => 
				query.Where(x => x.Price < anchor.Price ||
				(x.Price == anchor.Price && x.Id < anchor.Id)),
			(ProductSortBy.CategoryName, SortDirection.Ascending) => 
				query.Where(x =>
					x.Category.Name.CompareTo(anchor.CategoryName) > 0 ||
					(x.Category.Name == anchor.CategoryName && 
						x.Id > anchor.Id)),
			(ProductSortBy.CategoryName, SortDirection.Descending) =>
				query.Where(x =>
					x.Category.Name.CompareTo(anchor.CategoryName) < 0 ||
					(x.Category.Name == anchor.CategoryName &&
						x.Id < anchor.Id))
			,
			_ => throw new ArgumentOutOfRangeException(nameof(sortBy), sortBy, null)
		};
	}
}

Notice that each sort column needs its own ascending and descending case. As you add more sort options, this method will grow accordingly.

Applying the ordering

After filtering, we apply the ordering and take the records for the page. The ApplyOrder method follows a similar switch expression pattern.

// ProductRepository.cs
public class ProductRepository : IProductRepository
{
	...

	public async Task<IReadOnlyList<ProductDto>> GetProductsByKeysetAsync(
		ProductSortBy sortBy, 
		SortDirection sortDirection, 
		int? lastId, 
		int pageSize, 
		CancellationToken cancellationToken)
	{
		...

		return await ApplyOrder(query, sortBy, sortDirection)
			.Take(pageSize)
			.Select(product => new ProductDto(product.Id, product.Name, product.Price, product.Category.Name))
			.ToListAsync(cancellationToken);
	}

	private static IQueryable<Product> ApplyOrder(IQueryable<Product> query, ProductSortBy sortBy, SortDirection sortDirection)
	{
		return (sortBy, sortDirection) switch
		{
			(ProductSortBy.Name, SortDirection.Ascending) => 
				query.OrderBy(product => product.Name)
					.ThenBy(product => product.Id),
			(ProductSortBy.Name, SortDirection.Descending) => 
				query.OrderByDescending(product => product.Name)
					.ThenByDescending(product => product.Id),
			(ProductSortBy.Price, SortDirection.Ascending) => 
				query.OrderBy(product => product.Price)
					.ThenBy(product => product.Id),
			(ProductSortBy.Price, SortDirection.Descending) => 
				query.OrderByDescending(product => product.Price)
					.ThenByDescending(product => product.Id),
			(ProductSortBy.CategoryName, SortDirection.Ascending) => 
				query.OrderBy(product => product.Category.Name)
				.ThenBy(product => product.Id),
			(ProductSortBy.CategoryName, SortDirection.Descending) => 
				query.OrderByDescending(product => product.Category.Name)
					.ThenByDescending(product => product.Id),
			_ => throw new ArgumentOutOfRangeException(nameof(sortBy), sortBy, null)
		};
	}	
}

The SQL query

With the SQL query, all the filtering happens in the WHERE clause.

WHERE [p].[Name] > @anchor_Name OR ([p].[Name] = @anchor_Name AND [p].[Id] > @anchor_Id)

The database seeks directly to the anchor and reads forward from there.

Here's the full SQL output:

SELECT [p0].[Id], [p0].[Name], [p0].[Price], [c].[Name]
FROM (
    SELECT TOP(@p) [p].[Id], [p].[CategoryId], [p].[Name], [p].[Price]
    FROM [Products] AS [p]
    WHERE [p].[Name] > @anchor_Name OR ([p].[Name] = @anchor_Name AND [p].[Id] > @anchor_Id)
    ORDER BY [p].[Name], [p].[Id]
) AS [p0]
INNER JOIN [Categories] AS [c] ON [p0].[CategoryId] = [c].[Id]
ORDER BY [p0].[Name], [p0].[Id]

Offset pagination

Offset pagination is the alternative to keyset pagination and works differently. Instead of anchoring to a record, it skips several rows and fetches the next set. It is significantly simpler to write. There is no anchor lookup and no filter method to maintain.

// ProductRepository.cs
public class ProductRepository : IProductRepository
{
	...

	public async Task<IReadOnlyList<ProductDto>> GetProductsByOffsetAsync(ProductSortBy sortBy, SortDirection sortDirection, int skip, int take, CancellationToken cancellationToken)
	{
		var query = _dbContext.Products.AsNoTracking();

		return await ApplyOrder(query, sortBy, sortDirection)
			.Skip(skip)
			.Take(take)
			.Select(product => new ProductDto(product.Id, product.Name, product.Price, product.Category.Name))
			.ToListAsync(cancellationToken);
	}

	...
}

The ApplyOrder method is shared between both approaches, so there is no duplication there. The difference is in how the filtering is handled. With offset pagination, it skips records by calling Skip, rather than applying the filtering in the WHERE clause.

The SQL query

The SQL output for offset looks quite different:

OFFSET @p ROWS FETCH NEXT @p1 ROWS ONLY

The database uses OFFSET to skip rows, then fetches the next batch. There is no WHERE clause. On large datasets, that skip is expensive. The database still must read and discard every row before the offset.

Here is the full SQL:

SELECT [p0].[Id], [p0].[Name], [p0].[Price], [c].[Name]
    FROM (
        SELECT [p].[Id], [p].[CategoryId], [p].[Name], [p].[Price]
        FROM [Products] AS [p]
        ORDER BY [p].[Name], [p].[Id]
        OFFSET @p ROWS FETCH NEXT @p1 ROWS ONLY
    ) AS [p0]
    INNER JOIN [Categories] AS [c] ON [p0].[CategoryId] = [c].[Id]
    ORDER BY [p0].[Name], [p0].[Id]

Adding indexes

Before adding indexes, both approaches are slow. With 3 million records and no indexes, a request for either keyset or offset pagination took over a second to complete. We need to add an index for each column we are filtering or ordering on.

// ProductConfiguration.cs
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
	public void Configure(EntityTypeBuilder<Product> builder)
	{	
		… 

		builder.HasIndex(product => new { product.Name, product.Id });

		builder.HasIndex(product => new { product.Price, product.Id });		
	}
}

// CategoryConfiguration.cs
public class CategoryConfiguration : IEntityTypeConfiguration<Product>
{
	public void Configure(EntityTypeBuilder<Product> builder)
	{	
		… 

		builder.HasIndex(category => category.Name);
	}
}

This speed advantage only exists if the index is there.

Performance comparison

With indexes in place and a page size of 500 at page 100, the results we got were telling:

• Keyset: consistently around 8ms

• Offset: consistently around 57ms

Keyset pagination is the clear winner. Because it doesn't have to skip thousands of rows to get the record set, it's significantly quicker. With offset pagination, to reach page 100, it must skip 49,500 rows before the query can start.

On the other hand, it's difficult to use page numbers for keyset pagination. The only way you could do it is to store the lastId for each of the page numbers, adding more logic. We had to use offset pagination to get the lastId of page 99 so we could test out the keyset pagination.

Watch the video

Watch the video where we implement both keyset and offset pagination in Entity Framework Core, add indexes, and run a real-world performance comparison between the two.