Keeping API Pagination Reliable When Database Records Change

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

105, 104, 103

103, 102, 101

public class PaginationRequest
{
    public int Page { get; set; } = 1;

    public int PageSize { get; set; } = 20;
}

public static PaginationRequest Validate(
    PaginationRequest request)
{
    request.Page = Math.Max(1, request.Page);

    request.PageSize = Math.Clamp(
        request.PageSize,
        1,
        100);

    return request;
}

public class PagedResponse<T>
{
    public List<T> Items { get; set; } = new();

    public int Page { get; set; }

    public int PageSize { get; set; }

    public int TotalCount { get; set; }

    public int TotalPages { get; set; }
}

public async Task<PagedResponse<Order>> GetOrdersAsync(
    PaginationRequest request,
    CancellationToken cancellationToken)
{
    request = PaginationRequestValidator.Validate(
        request);

    var query = _context.Orders
        .AsNoTracking()
        .OrderByDescending(x => x.CreatedAtUtc)
        .ThenByDescending(x => x.Id);

    var totalCount = await query.CountAsync(
        cancellationToken);

    var items = await query
        .Skip((request.Page - 1) * request.PageSize)
        .Take(request.PageSize)
        .ToListAsync(cancellationToken);

    return new PagedResponse<Order>
    {
        Items = items,
        Page = request.Page,
        PageSize = request.PageSize,
        TotalCount = totalCount,
        TotalPages = (int)Math.Ceiling(
            totalCount / (double)request.PageSize)
    };
}

var orders = await _context.Orders
    .Skip(20)
    .Take(20)
    .ToListAsync();

var orders = await _context.Orders
    .OrderByDescending(x => x.CreatedAtUtc)
    .ThenByDescending(x => x.Id)
    .Skip(20)
    .Take(20)
    .ToListAsync();

CreatedAtUtc: 2026-11-02 10:00:00
Id: 105

var orders = await _context.Orders
    .Where(x =>
        x.CreatedAtUtc < lastCreatedAt ||
        (x.CreatedAtUtc == lastCreatedAt &&
         x.Id < lastOrderId))
    .OrderByDescending(x => x.CreatedAtUtc)
    .ThenByDescending(x => x.Id)
    .Take(pageSize)
    .ToListAsync(cancellationToken);

This query uses both the timestamp and unique ID to identify the next position.

Newer records inserted at the beginning of the list do not shift the cursor position.

That makes keyset pagination more reliable for frequently changing datasets.

Create a Cursor-Based Response

A cursor-based API does not need a page number.

Instead, return a continuation token representing the last record in the current result.

Create a response model:

public class CursorPagedResponse<T>
{
    public List<T> Items { get; set; } = new();

    public string? NextCursor { get; set; }

    public bool HasMore { get; set; }
}

Create a cursor model:

public class OrderCursor
{
    public DateTime CreatedAtUtc { get; set; }

    public long Id { get; set; }
}

The cursor should be encoded before returning it to the client.

public static class CursorHelper
{
    public static string Encode(OrderCursor cursor)
    {
        var json = JsonSerializer.Serialize(cursor);

        return Convert.ToBase64String(
            Encoding.UTF8.GetBytes(json));
    }

    public static OrderCursor Decode(string value)
    {
        var json = Encoding.UTF8.GetString(
            Convert.FromBase64String(value));

        return JsonSerializer.Deserialize<OrderCursor>(
            json) ?? throw new ArgumentException(
                "Invalid cursor.");
    }
}

Base64 encoding makes the cursor easier to transport in a URL. It does not provide security or prevent clients from modifying its contents.

For production APIs, consider signing the cursor or protecting it with ASP.NET Core Data Protection. Validate the decoded values and bind the cursor to the relevant filters and sort order.

Implement the Cursor Pagination Query

Now implement the complete query:

public async Task<CursorPagedResponse<Order>> GetOrdersAsync(
    string? cursor,
    int pageSize,
    CancellationToken cancellationToken)
{
    pageSize = Math.Clamp(pageSize, 1, 100);

    var query = _context.Orders
        .AsNoTracking()
        .AsQueryable();

    if (!string.IsNullOrWhiteSpace(cursor))
    {
        var last = CursorHelper.Decode(cursor);

        query = query.Where(x =>
            x.CreatedAtUtc < last.CreatedAtUtc ||
            (x.CreatedAtUtc == last.CreatedAtUtc &&
             x.Id < last.Id));
    }

    var results = await query
        .OrderByDescending(x => x.CreatedAtUtc)
        .ThenByDescending(x => x.Id)
        .Take(pageSize + 1)
        .ToListAsync(cancellationToken);

    var hasMore = results.Count > pageSize;

    var items = results
        .Take(pageSize)
        .ToList();

    string? nextCursor = null;

    if (hasMore && items.Count > 0)
    {
        var lastItem = items[^1];

        nextCursor = CursorHelper.Encode(
            new OrderCursor
            {
                CreatedAtUtc = lastItem.CreatedAtUtc,
                Id = lastItem.Id
            });
    }

    return new CursorPagedResponse<Order>
    {
        Items = items,
        HasMore = hasMore,
        NextCursor = nextCursor
    };
}

The query retrieves one additional record to determine whether another page exists.

For example, if the requested page size is 20, the database retrieves up to 21 records.

If 21 records are returned, the API knows that another page is available. It returns the first 20 and creates the next cursor from the twentieth record.

This avoids running a separate count query for every request.

Understand the Limitations of Keyset Pagination

Keyset pagination is not a complete snapshot of the database.

It prevents newly inserted records before the current cursor from shifting the next page, but it does not guarantee that every record remains unchanged between requests.

For example:

• A record can be deleted before the client reaches it.
• An existing record can change its sort value and move across the cursor.
• A record inserted with an older timestamp may appear in a later page.
• Changes to filtering fields can affect which records qualify.

If the application needs a consistent snapshot across multiple page requests, use a suitable snapshot strategy.

One option is to capture a fixed upper boundary on the first request and include it in every subsequent cursor. Another is to use database snapshot isolation within a transaction, although keeping a transaction open across multiple HTTP requests is usually not a good design.

The right approach depends on the application’s consistency requirements.

Add Database Indexes for Pagination

Pagination performance depends heavily on database indexes.

If the API frequently sorts by creation time and ID, create a composite index.

For SQL Server, the index could look like this:

CREATE INDEX IX_Orders_CreatedAtUtc_Id
ON Orders (CreatedAtUtc DESC, Id DESC);

The exact index design depends on the database engine and query workload.

For EF Core, configure the index in the model:

modelBuilder.Entity<Order>()
    .HasIndex(x => new
    {
        x.CreatedAtUtc,
        x.Id
    });

If the API also filters by fields such as customer ID or status, evaluate whether those columns should be included in a composite index.

Do not create indexes without checking actual query plans. Additional indexes increase storage requirements and make inserts and updates more expensive.

Choose the Right Pagination Strategy

Offset and keyset pagination solve different problems.

Requirement	Offset pagination	Keyset pagination
Numbered pages	Supported	Not naturally supported
Jump to a specific page	Supported	Difficult
Frequent inserts	Can shift results	More stable
Large offsets	Can become expensive	Avoids large skips
Next-page navigation	Supported	Well suited
Total record count	Easy to include	Usually separate
Stable traversal	Limited with changing data	Better with a stable cursor

Use offset pagination for administrative screens where users need numbered pages and the dataset is relatively stable.

Use keyset pagination for activity feeds, order histories, transaction lists, and other datasets where new records arrive frequently.

Common Mistakes to Avoid

One common mistake is using Skip and Take without an explicit sort order. This can produce inconsistent results even when the database has not changed.

Another mistake is sorting only by a timestamp. Multiple records may share the same timestamp, so use a unique secondary key.

Developers also sometimes assume Base64-encoded cursors are secure. They are only encoded, not protected against modification.

Finally, avoid running an expensive CountAsync query on every request when the interface does not need an exact total count. A HasMore value is often enough for next-page navigation.

Summary

Reliable API pagination requires more than limiting the number of records returned by a query.

Offset pagination is useful for numbered pages, but inserts and deletions can shift records between requests. Keyset pagination uses a stable cursor to make next-page navigation more predictable when the database changes frequently.

Use deterministic sorting with a unique secondary key, choose the pagination strategy based on the application’s requirements, and create indexes that support the actual query pattern.

For production applications, also validate cursor values, consider snapshot requirements, and test pagination while records are being inserted, updated, and deleted.

Best ASP.NET Core 11.0 Hosting

The feature and reliability are the most important things when choosing a good ASP.NET Core 11.0 hosting. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core , their servers are optimized for PHP web applications such as the latest ASP.NET Core 11.0 version. The performance and the uptime of the ASP.NET Core  hosting service are excellent, and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. At HostForLIFE.eu, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its data centers are equipped with top equipment like cooling system, fire detection, high-speed Internet connection, and so on. That is why HostForLIFE.eu guarantees 99.9% uptime for ASP.NET Core . And the engineers do regular maintenance and monitoring works to assure its ASP.NET Core  hosting are security and always up.