ASP.NET Core: Designing Pagination for Fast and Predictable APIs

An API that returns every matching database record can become slow as the dataset grows. Large responses increase database work, network traffic, serialization time, and memory consumption.

Pagination limits the amount of data returned per request and gives clients a predictable way to navigate large collections.

Why Pagination Matters

Imagine an endpoint that returns every order:

app.MapGet("/api/orders", async (AppDbContext db) =>
{
    return await db.Orders.ToListAsync();
});

This implementation may be acceptable for a tiny internal dataset, but it does not scale well when the table contains hundreds of thousands of rows.

Pagination makes the amount of work more manageable.

Implement Basic Offset Pagination

A simple API can accept page and pageSize parameters:

app.MapGet("/api/orders", async (
    AppDbContext db,
    int page = 1,
    int pageSize = 20) =>
{
    if (page < 1 || pageSize < 1 || pageSize > 100)
    {
        return Results.BadRequest(
            "Invalid pagination parameters.");
    }

    var orders = await db.Orders
        .OrderBy(order => order.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(order => new OrderDto(
            order.Id,
            order.Total))
        .ToListAsync();

    return Results.Ok(orders);
});

This example assumes that OrderDto is defined and that Id provides a suitable ordering. The application should also consider overflow-safe page calculations and whether the client needs total counts.

Always Use a Stable Ordering

Pagination without a deterministic order can produce confusing results. Records may appear on multiple pages or be skipped because the database is free to return rows in different orders.

Ordering by a unique column, such as an ID, provides a stable baseline. If ordering by a non-unique field such as creation time, add a unique tie-breaker.

Even with stable ordering, new records inserted between requests can affect offset-based navigation.

Understand Offset Pagination’s Limits

Offset pagination is convenient because clients can request a particular page. However, large offsets may become expensive because the database may need to scan or skip many rows before returning the requested page.

For large or frequently changing datasets, keyset pagination can be more efficient.

Instead of requesting page 500, the client requests records after a known position, such as the last ID returned by the previous request.

var orders = await db.Orders
    .Where(order => order.Id > lastSeenId)
    .OrderBy(order => order.Id)
    .Take(pageSize)
    .Select(order => new OrderDto(
        order.Id,
        order.Total))
    .ToListAsync();

This simplified query illustrates the principle. A production cursor should encode the complete ordering position and be validated so clients cannot supply arbitrary or malformed cursor values.

Return Useful Metadata

Depending on the API, a paginated response might include:

• The returned items.
• The next-page cursor or link.
• The page size.
• The total count, if it is practical and required.

Total counts can be expensive for large filtered datasets. Do not calculate them automatically when clients do not need them.

Document the maximum page size, ordering rules, filtering behavior, and what happens when the requested page is empty.

Apply Authorization Before Pagination

Filtering must respect the caller’s access rights before records are returned. Do not fetch a broad dataset and rely on the client to hide unauthorized rows.

Apply tenant, ownership, and permission constraints in the database query wherever practical. Pagination is a performance technique, not an authorization mechanism.

Conclusion

Good API pagination combines bounded response sizes, deterministic ordering, appropriate query strategies, and clear response contracts. Offset pagination works well for many ordinary endpoints, while cursor-based approaches are often better for large or frequently changing collections.

Choose the strategy based on how clients navigate the data and how the underlying database behaves.