Stable Pagination in .NET APIs: Offset Drift, Keyset Cursors, and Tie Breakers

Page 1 returns IDs 105,104. Before running the five-row program, predict page 2 after inserting 106 ahead of the cursor, then after deleting 105. Compare OFFSET with the saved boundary (12:00,104); keep the original timestamps and direction.

Worked boundary trace in C#

This complete console example uses an in-memory array, not EF Core or a database. All timestamps are non-null UTC values; Id is unique and numeric. It isolates the comparison rule so the expected rows are easy to check. The SQL fragment in the continuation-contract section remains illustrative, not a tested database query.

using System;
using System.Linq;

var t = new DateTimeOffset(2026, 9, 1, 12, 0, 0, TimeSpan.Zero);
var rows = new[]
{
    new Order(105, t), new Order(104, t), new Order(103, t),
    new Order(102, t.AddMinutes(-1)), new Order(101, t.AddMinutes(-2))
};

static Order[] Sorted(Order[] source) => source
    .OrderByDescending(x => x.CreatedAt)
    .ThenByDescending(x => x.Id).ToArray();

static Order[] After(Order[] source, Order cursor) => Sorted(source)
    .Where(x => x.CreatedAt < cursor.CreatedAt ||
        (x.CreatedAt == cursor.CreatedAt && x.Id < cursor.Id))
    .Take(2).ToArray();

static string Ids(Order[] page) => string.Join(",", page.Select(x => x.Id));

var first = Sorted(rows).Take(2).ToArray();
var cursor = first[^1];
var inserted = rows.Append(new Order(106, t.AddMinutes(1))).ToArray();
var deleted = rows.Where(x => x.Id != 105).ToArray();

Console.WriteLine($"first: {Ids(first)}");
Console.WriteLine($"insert offset: {Ids(Sorted(inserted).Skip(2).Take(2).ToArray())}");
Console.WriteLine($"insert keyset: {Ids(After(inserted, cursor))}");
Console.WriteLine($"delete offset: {Ids(Sorted(deleted).Skip(2).Take(2).ToArray())}");
Console.WriteLine($"delete keyset: {Ids(After(deleted, cursor))}");

record Order(int Id, DateTimeOffset CreatedAt);

Expected output:

first: 105,104
insert offset: 104,103
insert keyset: 103,102
delete offset: 102,101
delete keyset: 103,102

Why these answers follow: IDs 105, 104, and 103 share a timestamp, so the descending ID comparison decides their order. Page 1 ends at 104. Inserting 106 shifts array positions and makes offset repeat 104. Deleting 105 shifts positions the other way and makes offset skip 103. Neither change alters the keyset boundary (12:00, 104).

Check the deleted-boundary prediction

Answered edge cases

1. What if boundary row 104 is deleted? The saved values still define the boundary; the next page is 103,102. Do not require reloading row 104 to decode the cursor.
2. What if unseen row 103 moves to 12:02? It moves before the boundary and is absent from the continuation; the next page becomes 102,101. Immutable IDs do not compensate for mutable timestamps.
3. What if row 107 is inserted at 11:59:30? It is after the cursor and may appear on a later page even though it did not exist at page 1. This demonstrates why the cursor is not a snapshot.
4. What if the client changes status=shipped to status=pending? Reject the cursor/context mismatch and start a new traversal; do not silently apply the old boundary to a different result set.
5. Does an empty page prove the feed is permanently complete? No. It says there are no matching rows after this boundary at that read. Later inserts, membership changes, and retention can change the result.

Why counting positions drifts

Imagine a small timestamped order feed for a commerce system. The feed is sorted by CreatedAt descending, then by OrderId descending to break ties. A client asks for page 1, gets 20 items, and later asks for page 2.

With offset pagination, page 2 means “skip the first 20 rows currently visible.” That sounds simple, but it is brittle. If a new order is inserted between requests, or if an older order is deleted, the meaning of “skip 20” changes. The client can see duplicates, miss items, or both.

A stable cursor instead says, “continue after the last row I already saw, using the same sort order.” That is why keyset pagination is also called seek pagination. It does not count rows; it resumes from a concrete position in the ordering.

For an order feed, a practical sort key is a composite key:

• CreatedAt for recency
• OrderId as a unique tie breaker

Without a unique tie breaker, two orders created in the same timestamp bucket may swap positions between requests. That creates non-deterministic page boundaries even if the underlying rows never change. Deterministic ordering is the foundation of stable pagination.

Define the continuation contract

A robust .NET API usually exposes one of two pagination strategies:

1. Offset pagination: pageNumber and pageSize
2. Keyset pagination: a cursor containing the last seen sort key values

Offset pagination is easy for clients to understand, and it works well for admin dashboards or small data sets. But under concurrency it has what interviewers often call offset drift.

Here is the core problem in a feed sorted newest-first:

• Client requests page 1 and gets items 1–20
• A new order arrives at the top
• Client requests page 2 with skip=20
• With just that one insertion and no other changes, the first 20 rows are now the new order plus old items 1-19. Page 2 therefore starts with old item 20, duplicating the last item from the original page 1.

Deletes cause a different drift: if an item from page 1 is removed, then skip=20 may skip one too many rows and the client misses an item that was previously on page 2.

Keyset pagination avoids this positional drift by using a predicate based on the last seen key. For predictable forward traversal, prefer immutable ordering keys. If an existing row's sort values change across the cursor boundary, it can still be skipped or encountered again; a unique tie breaker alone does not solve that. For a descending feed ordered by (CreatedAt, OrderId) the next page uses a condition like:

WHERE (CreatedAt < @lastCreatedAt)
   OR (CreatedAt = @lastCreatedAt AND OrderId < @lastOrderId)
ORDER BY CreatedAt DESC, OrderId DESC

That predicate is the important idea, not the exact SQL flavor. It says: keep scanning from the point after the last item you saw, in the same total order.

In a .NET API, the cursor often serializes the last item’s sort key values into an opaque token. Clients should treat the cursor as an implementation detail, not as a business identifier. A good cursor is typically:

• opaque
• compact
• tamper-resistant if needed
• tied to a specific sort order

A key interview nuance: cursor pagination is not snapshot isolation. A cursor can help you move forward deterministically through a changing list, but it does not automatically freeze the data at a point in time. If the database changes between requests, later pages may include newly inserted rows or omit rows deleted after the first request. That is normal unless you explicitly design a snapshot-backed feed.

This distinction matters:

• Cursor pagination answers: “Where do I continue?”
• Snapshot isolation answers: “Am I reading the same historical view?”
• Exactly-once delivery answers: “Will I process each item exactly once?”

Those are different guarantees. A cursor alone does not provide exactly-once semantics. If a client retries a request, it may receive overlapping rows unless the API or consumer layer deduplicates by stable IDs. Likewise, if you need the user to see a frozen export or an audit-complete report, you need storage support for a versioned snapshot, transaction timestamp, or a materialized export table.

For a timestamped order feed, a practical API design might be:

• GET /orders?limit=20&after=...
• The server returns items sorted by CreatedAt DESC, OrderId DESC
• The response includes nextCursor
• The cursor encodes the last item’s (CreatedAt, OrderId) pair

Direction matters too. If the API supports both newest-first and oldest-first browsing, the cursor must encode the direction or be invalid outside the original ordering. You cannot safely reuse a cursor produced for descending order in ascending order, because the seek predicate changes.

Filtering also matters. If the client filters by status, customer, or tenant, the cursor must belong to that same filtered result set. A cursor built over status = shipped should not be replayed against status = pending. In practice, the filter criteria are often included in the signed cursor payload or validated separately on the server. A valid signature does not grant access: authorize the current caller on every request and apply the permitted tenant scope to the query. Reject a cursor whose version, direction, or normalized filter context does not match this request. Preserve the exact stored timestamp precision; rounding a boundary value changes which rows satisfy the predicate.

Tradeoffs to mention in interviews:

• Offset pagination is simpler and supports random access by page number, but it is unstable under concurrent writes.
• Keyset pagination avoids offset drift with immutable keys and a fixed query context. Efficient deep paging still depends on the actual query plan and storage design; a cursor is not a performance guarantee. It does not jump directly to page 57 without a separately maintained position map or walking the cursor chain.
• A composite keyset cursor is only correct if the sort order is deterministic and total.
• If you need a historical snapshot, design for snapshot storage; do not assume cursors can reconstruct the past.

Failure modes

Common mistakes include:

• Using offset pagination on a rapidly changing feed and assuming pages are repeatable
• Sorting only by timestamp when many rows share the same timestamp
• Encoding a cursor without the tie breaker, which causes missing or duplicated rows near page boundaries
• Reusing a cursor after changing sort direction
• Reusing a cursor after changing filters or tenant scope
• Claiming that cursor pagination guarantees exactly-once processing
• Claiming that a cursor gives a historical snapshot without dedicated storage support

Another subtle failure mode is non-deterministic ordering from the database itself. If your ORDER BY does not fully determine a unique order, different query plans can produce different relative row orderings among ties. That becomes visible as page jitter even when the underlying data is unchanged.

Interview drill

If asked to explain stable pagination in an interview, you can answer in three steps:

1. State that offset pagination drifts under concurrent inserts and deletes.
2. Describe keyset pagination using a composite ordering key, such as (CreatedAt DESC, OrderId DESC).
3. Clarify that cursors are continuation tokens, not snapshots, and that exactly-once delivery requires separate deduplication or storage guarantees.

A strong follow-up answer is to explain how you would encode and validate a cursor. Mention that the cursor should capture the last seen sort values, the direction, and the active filter context, ideally in a tamper-resistant opaque form.

Reference

• Microsoft EF Core pagination