Prefer keyset pagination for deep lists

Offset pagination with Skip and Take is simple, but it gets worse as users move deeper into a large list. The database still has to process the skipped rows, and concurrent inserts or deletes can make items appear twice or disappear between pages.

Use keyset pagination when the user mostly moves forward or backward through an ordered result set. Instead of asking for page 500, ask for the next items after the last seen key.

In EF Core, that usually means replacing a deep Skip with a seek predicate:

// Avoid for deep lists: the database still has to walk skipped rows.
var page500 = await db.AuditLogs
    .OrderByDescending(x => x.CreatedAt)
    .ThenByDescending(x => x.Id)
    .Skip(10_000)
    .Take(20)
    .ToListAsync(ct);

// Prefer: continue after the last row from the previous page.
var nextPage = await db.AuditLogs
    .Where(x =>
        x.CreatedAt < lastSeenDate ||
        (x.CreatedAt == lastSeenDate && x.Id < lastSeenId))
    .OrderByDescending(x => x.CreatedAt)
    .ThenByDescending(x => x.Id)
    .Take(20)
    .ToListAsync(ct);

EF Core can express the composite seek as normal LINQ predicates, but you still have to carry the cursor values yourself. SQL row-value syntax such as (CreatedAt, Id) < (@date, @id) is not currently expressible in LINQ.

The important part is a stable, unique order. If you sort by date, include a tie-breaker such as an ID. Then the next query can continue from the last seen date and ID instead of counting past thousands of rows.

Forward pagination is the simple case. Previous-page navigation usually needs its own cursor, often from the first row in the current page, plus a reversed sort that you normalize before returning results.

Offset pagination is still fine when users need random page access. For feeds, logs, search results, audit trails, and deep admin lists, keyset pagination usually matches the user behavior and the database better.