ASP.NET Core: Designing API Pagination That Scales

Introduction

An API that returns every matching record may work during early development. As the dataset grows, however, the same endpoint can become expensive in database work, response size, network transfer, and client-side processing.

Pagination limits how much data is returned in a request. The strategy you choose affects performance, consistency, and usability.

Why Pagination Matters

Imagine an endpoint that returns every order belonging to a customer. If that customer has hundreds of thousands of orders, loading everything at once is wasteful.

A paginated endpoint might use:

GET /api/orders?page=1&pageSize=25

The server should enforce a maximum page size, validate input, and define a stable ordering so results behave predictably.

Implement Offset Pagination

Offset pagination is straightforward and works well for many administrative screens and moderate datasets.

public sealed record PageResult<T>(
    IReadOnlyList<T> Items,
    int Page,
    int PageSize
);

A service method can validate the requested page and size before querying:

public async Task<PageResult<OrderDto>> GetOrdersAsync(
    int page,
    int pageSize,
    CancellationToken cancellationToken)
{
    page = Math.Max(page, 1);
    pageSize = Math.Clamp(pageSize, 1, 100);

    var offset = (long)(page - 1) * pageSize;

    var items = await dbContext.Orders
        .AsNoTracking()
        .OrderByDescending(order => order.CreatedAt)
        .ThenByDescending(order => order.Id)
        .Skip((int)Math.Min(offset, int.MaxValue))
        .Take(pageSize)
        .Select(order => new OrderDto(
            order.Id,
            order.CreatedAt,
            order.Total
        ))
        .ToListAsync(cancellationToken);

    return new PageResult<OrderDto>(
        items,
        page,
        pageSize
    );
}

This illustrates the structure, but a production implementation should reject unreasonable page numbers rather than silently allowing a huge offset. The code assumes OrderDto, dbContext, and the required Entity Framework Core namespaces are defined elsewhere.

Always Use a Stable Sort

Ordering only by a timestamp can produce ambiguous results when multiple records share that timestamp.

Add a unique tie-breaker:

.OrderByDescending(x => x.CreatedAt)
.ThenByDescending(x => x.Id)

A deterministic order reduces unpredictable results. It does not guarantee that pages remain unchanged when records are inserted or deleted between requests.

Understand Offset Costs

Large offsets can become expensive because the database may need to locate and skip many rows before returning the requested page.

SELECT *
FROM Orders
ORDER BY CreatedAt DESC, Id DESC
OFFSET 100000 ROWS
FETCH NEXT 25 ROWS ONLY;

Actual performance depends on the database engine, indexes, query plan, and data distribution. Benchmark representative queries instead of assuming every offset query is slow.

Offset vs. Keyset Pagination

Consideration	Offset pagination	Keyset pagination
How it works	Skips a specified number of rows	Continues from the last record’s sort values
Example	?page=5&pageSize=25	?cursor=…
Deep-page performance	May degrade as offsets increase	Often more efficient with suitable indexes
Jumping to a numbered page	Straightforward	Not naturally supported
Concurrent inserts and deletes	Can shift records between pages	Usually less affected by changes before the cursor
Implementation	Relatively simple	Requires cursor encoding, validation, and consistent ordering
Total count	Often included	Frequently unnecessary
Typical use	Admin tables and numbered-page navigation	Feeds, event logs, and large sequential datasets

When to Choose Offset Pagination

Offset pagination is a good choice when users need numbered pages, direct access to a specific page, or familiar table navigation. It is also practical for moderate datasets where query performance remains acceptable.

For example, an administrative screen displaying a few thousand products may work perfectly well with offset pagination and a reasonable page-size limit.

When to Choose Keyset Pagination

Keyset pagination is often better for large datasets where users move forward or backward through results and deep offsets become expensive.

Suppose results are ordered by CreatedAt DESC, Id DESC. The next page can use the timestamp and ID of the last record returned.

var query = dbContext.Orders
    .AsNoTracking()
    .OrderByDescending(x => x.CreatedAt)
    .ThenByDescending(x => x.Id);

if (cursorCreatedAt is not null && cursorId is not null)
{
    query = query
        .Where(x =>
            x.CreatedAt < cursorCreatedAt ||
            (x.CreatedAt == cursorCreatedAt &&
             x.Id < cursorId))
        .OrderByDescending(x => x.CreatedAt)
        .ThenByDescending(x => x.Id);
}

This illustrates the required comparison; in a complete implementation, keep the query types consistent and validate the cursor. The cursor should include every field needed to continue the ordering and should be protected against tampering when appropriate.

Keyset pagination is not immune to every concurrent data change. Its consistency depends on the ordering fields and how records are updated or deleted.

Design a Useful Response

An offset-based response might look like this:

{
  "items": [],
  "page": 2,
  "pageSize": 25,
  "totalCount": 480
}

A total count is useful for numbered-page navigation, but calculating it can add database work. Cursor-based APIs may instead return a next-page cursor or a hasMore indicator.

Choose response metadata according to what the client actually needs.

Add the Right Indexes

For a query ordered by creation time and ID, a composite index aligned with the filter and sort may improve performance. The ideal index depends on the database provider and query.

Inspect execution plans and benchmark realistic data volumes. Remember that indexes also add storage costs and can slow down writes.

Test Edge Cases

Test page sizes below and above the allowed range, empty datasets, final pages, duplicate sort values, records inserted between requests, and invalid cursors.

Apply authorization and filtering before pagination. Returning only a small number of unauthorized records is still a security bug.

Conclusion

Offset pagination is simple and useful for many APIs. Keyset pagination is often preferable for large, ordered datasets where users navigate sequentially.

Choose based on the interface and measured performance. In both cases, enforce limits, use deterministic ordering, create suitable indexes, and test under realistic conditions.