Your agent's list endpoint pages with Skip and Take: prove it drops rows, then switch to keyset paging
A 40-line walk-every-page check shows duplicated and missing rows in an offset-paged EF Core endpoint, and gives the agent a failing target for the keyset rewrite. About 15 minutes to try.
A support ticket arrives: a customer says their order history shows the same order twice, and another order they placed last week has vanished from the list. The orders are all in the database. The endpoint that lists them was written by an agent, reviewed in two minutes, and it does exactly what the prompt asked: ?page=3&size=10, OrderByDescending, Skip, Take. It passes every test anyone wrote for it.
Those tests walk one page at a time against a table that sits still. The bug only shows when the table moves between page requests, which is what a live orders table does all day. Offset paging reads “rows 20 to 29 of whatever exists right now”, and what exists changes.
This Playbook builds one small check that makes that failure visible in an xUnit test: walk every page of the endpoint while rows are inserted or deleted between requests, and assert that every surviving row comes back exactly once. Then it hands the failing check to the agent as the target for a keyset rewrite, and shows what the rewrite costs and what it can’t do. The check and the prompt work with any coding agent that can read files and run dotnet test. The example targets .NET 10, EF Core 10 and SQLite in memory, so everything runs offline.
The setup
Create a test project and add the SQLite provider. The only files are the entity, a context with the index the paging order needs, and a couple of records.
dotnet new xunit -n Paging -o Paging
cd Paging
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
using Microsoft.EntityFrameworkCore;
namespace Paging;
public class Order
{
public int Id { get; set; }
public DateTime PlacedUtc { get; set; }
public string Reference { get; set; } = "";
}
public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
public DbSet<Order> Orders => Set<Order>();
protected override void OnModelCreating(ModelBuilder b) =>
b.Entity<Order>().HasIndex(o => new { o.PlacedUtc, o.Id });
}
public record OrderDto(int Id, DateTime PlacedUtc, string Reference);
public record Page(IReadOnlyList<OrderDto> Items, string? Next);
Page carries an opaque Next string instead of a page number. That’s deliberate: the contract the agent implements is “give me a token, get the next page”, and the token’s meaning is the implementation’s business. The offset version will smuggle the page number in it, the keyset version will smuggle the last row’s sort key.
Here’s the offset implementation, the one an agent produces when asked for a paged list and given no constraints:
using Microsoft.EntityFrameworkCore;
namespace Paging;
public class OffsetPager(ShopDb db)
{
public async Task<Page> GetAsync(string? cursor, int size = 10)
{
var page = cursor is null ? 0 : int.Parse(cursor);
var items = await db.Orders.AsNoTracking()
.OrderByDescending(o => o.PlacedUtc)
.Skip(page * size).Take(size)
.Select(o => new OrderDto(o.Id, o.PlacedUtc, o.Reference))
.ToListAsync();
return new Page(items, items.Count == size ? (page + 1).ToString() : null);
}
}
Nothing in it looks wrong. It has no bug a linter or a quick read would flag, and that’s why it ships.
The check: walk every page while the table moves
The harness has three parts: a fixture that owns an in-memory SQLite database, a Walk that follows Next until it runs out and lets the test change the table after each page, and two assertions that name the offending ids.
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
namespace Paging;
public sealed class Fixture : IDisposable
{
readonly SqliteConnection _conn = new("Data Source=:memory:");
public Fixture() { _conn.Open(); using var db = New(); db.Database.EnsureCreated(); }
public ShopDb New() => new(new DbContextOptionsBuilder<ShopDb>().UseSqlite(_conn).Options);
public void Dispose() => _conn.Dispose();
public void Seed(int count, Func<int, DateTime> placed)
{
using var db = New();
for (var i = 0; i < count; i++)
db.Orders.Add(new Order { PlacedUtc = placed(i), Reference = $"ORD-{i:0000}" });
db.SaveChanges();
}
}
public class PagingInvariants
{
static readonly DateTime T0 = new(2026, 11, 1, 9, 0, 0, DateTimeKind.Utc);
static async Task<List<int>> Walk(Func<string?, Task<Page>> fetch, Action<int>? between = null)
{
var seen = new List<int>();
string? cursor = null;
for (var pageNo = 0; pageNo < 100; pageNo++)
{
var page = await fetch(cursor);
seen.AddRange(page.Items.Select(i => i.Id));
between?.Invoke(pageNo);
if (page.Next is null) return seen;
cursor = page.Next;
}
throw new Exception("Paging never ended");
}
static void AssertNoDuplicates(List<int> ids)
{
var repeated = ids.GroupBy(i => i).Where(g => g.Count() > 1).Select(g => g.Key).Order().ToList();
Assert.True(repeated.Count == 0, $"Returned twice: [{string.Join(", ", repeated)}]");
}
static void AssertNothingMissing(List<int> returned, IEnumerable<int> expected)
{
var missing = expected.Except(returned).Order().ToList();
Assert.True(missing.Count == 0, $"Never returned: [{string.Join(", ", missing)}]");
}
}
Two details matter. The between hook runs after a page has been fetched and before the next request, which is the window a real client leaves open (the user reads the page, thinks, taps “next”). And the failure messages list ids. An agent that gets Expected: 38, Actual: 35 has to guess what went wrong, while Returned twice: [26, 27, 28] tells it which rows to look at.
Now the scenarios. First, a control: the walk with nothing changing must pass for the offset pager, otherwise the harness is the thing under suspicion.
[Fact]
public async Task Offset_is_fine_when_nothing_changes()
{
using var f = new Fixture();
f.Seed(35, i => T0.AddMinutes(i));
using var db = f.New();
var ids = await Walk(c => new OffsetPager(db).GetAsync(c));
Assert.Equal(35, ids.Count);
AssertNoDuplicates(ids);
}
Second, new orders arrive after the first page is fetched. The list is newest-first, so each new order lands at the front and pushes every older row one slot down:
[Fact]
public async Task Offset_repeats_rows_when_new_orders_arrive_mid_walk()
{
using var f = new Fixture();
f.Seed(35, i => T0.AddMinutes(i));
using var db = f.New();
var ids = await Walk(c => new OffsetPager(db).GetAsync(c), between: p =>
{
if (p == 0) f.Seed(3, i => T0.AddDays(1).AddMinutes(i));
});
AssertNoDuplicates(ids);
}
Third, rows the client has already seen are deleted (a purge job, a cancelled order). Everything behind them shifts the other way. This one is a theory so the same scenario can run against either pager:
[Theory]
[InlineData("offset")]
[InlineData("keyset")]
public async Task No_surviving_order_is_skipped_when_seen_orders_are_deleted_mid_walk(string kind)
{
using var f = new Fixture();
f.Seed(35, i => T0.AddMinutes(i));
using var db = f.New();
Func<string?, Task<Page>> fetch = kind == "offset"
? c => new OffsetPager(db).GetAsync(c)
: c => new KeysetPager(db).GetAsync(c);
var ids = await Walk(fetch, between: p =>
{
if (p == 0)
{
using var w = f.New();
w.Orders.Where(o => o.Id > 30).ExecuteDelete(); // 5 of the 10 rows already returned
}
});
AssertNothingMissing(ids, Enumerable.Range(1, 30));
}
The assertion is about survivors only: orders 1 to 30 still exist, so every one of them must be returned at some point. Orders 31 to 35 were deleted after the client saw them, and whether they appear in ids doesn’t matter.
Run it against the offset pager (the KeysetPager class doesn’t exist yet, so stub it or run just the offset cases first):
dotnet test --filter "FullyQualifiedName~Offset|FullyQualifiedName~No_surviving"
Offset fails both mutation scenarios and passes the control, which is what an honest harness should do:
Failed Paging.PagingInvariants.Offset_repeats_rows_when_new_orders_arrive_mid_walk [47 ms]
Error Message:
Returned twice: [26, 27, 28]
Failed Paging.PagingInvariants.No_surviving_order_is_skipped_when_seen_orders_are_deleted_mid_walk(kind: "offset") [8 ms]
Error Message:
Never returned: [21, 22, 23, 24, 25]
Three inserts made orders 26, 27 and 28 appear on page 1 and again on page 2. Five deletes made orders 21 to 25 slide into the slots the client had already paged past, so they never show up at all. Both match what the EF Core documentation says about offset pagination: with concurrent updates, entries can be skipped or shown twice.
Hand the failure to the agent
Now the prompt. The point is to give the agent the failing check, the constraint that makes the fix a fix, and the limits of what it may touch. Keep it as plain text so it works in any tool:
The paging tests in PagingInvariants.cs fail for OffsetPager. Add a KeysetPager
with the same GetAsync(string? cursor, int size) signature and make all tests
pass without editing PagingInvariants.cs or Data.cs.
Requirements:
- Order by PlacedUtc descending, then Id descending. The ordering must be
fully unique, because PlacedUtc alone has ties.
- No Skip. The cursor holds the PlacedUtc and Id of the last row returned, and
the next query filters with a WHERE clause on those two values.
- Fetch size + 1 rows to decide whether Next is null. Do not run a second query.
- The cursor is an opaque URL-safe string.
- Run: dotnet test. Report the output, not a summary of it.
Two lines in that prompt earn their keep. “Without editing the test file” stops the usual escape of loosening the assertion until it passes. And “Do not run a second query” blocks the common shortcut for working out whether a next page exists, a COUNT(*) that is itself a race against the inserts the test is simulating.
What the keyset version looks like
This is the implementation that satisfies the prompt. If the agent’s attempt differs, compare it to this on three points: the tie-breaker, the strict inequality direction, and the size + 1 fetch.
using System.Buffers.Text;
using System.Text;
using Microsoft.EntityFrameworkCore;
namespace Paging;
public class KeysetPager(ShopDb db)
{
public async Task<Page> GetAsync(string? cursor, int size = 10)
{
var query = db.Orders.AsNoTracking();
if (cursor is not null)
{
var (placed, id) = Decode(cursor);
query = query.Where(o => o.PlacedUtc < placed
|| (o.PlacedUtc == placed && o.Id < id));
}
var rows = await query
.OrderByDescending(o => o.PlacedUtc).ThenByDescending(o => o.Id)
.Take(size + 1) // one extra row says whether another page exists
.Select(o => new OrderDto(o.Id, o.PlacedUtc, o.Reference))
.ToListAsync();
var items = rows.Take(size).ToList();
var next = rows.Count > size ? Encode(items[^1]) : null;
return new Page(items, next);
}
static string Encode(OrderDto last) =>
Base64Url.EncodeToString(Encoding.UTF8.GetBytes($"{last.PlacedUtc.Ticks}:{last.Id}"));
static (DateTime, int) Decode(string cursor)
{
var parts = Encoding.UTF8.GetString(Base64Url.DecodeFromChars(cursor)).Split(':');
return (new DateTime(long.Parse(parts[0]), DateTimeKind.Utc), int.Parse(parts[1]));
}
}
The cursor says “everything strictly older than this row”, expressed as values rather than a position. An insert at the front of the list changes nothing the cursor refers to. A delete of a row behind the cursor changes nothing either. Even a delete of the cursor row itself is harmless, because the cursor is a pair of values and not a reference to a row that has to still exist.
The Id tie-breaker is what the EF Core docs warn about: order by date alone and rows with equal dates can be ordered differently across two queries, so some get skipped. Add a fourth scenario for it, with every order stamped with the same second (an import job does this):
[Fact]
public async Task Keyset_returns_every_row_once_when_all_timestamps_tie()
{
using var f = new Fixture();
f.Seed(35, _ => T0);
using var db = f.New();
var ids = await Walk(c => new KeysetPager(db).GetAsync(c));
Assert.Equal(35, ids.Count);
AssertNoDuplicates(ids);
}
The insert scenario from earlier gets a keyset twin. The three new orders sit before the cursor, so the walk doesn’t return them, and nothing is repeated:
[Fact]
public async Task Keyset_survives_new_orders_mid_walk()
{
using var f = new Fixture();
f.Seed(35, i => T0.AddMinutes(i));
using var db = f.New();
var ids = await Walk(c => new KeysetPager(db).GetAsync(c), between: p =>
{
if (p == 0) f.Seed(3, i => T0.AddDays(1).AddMinutes(i));
});
AssertNoDuplicates(ids);
Assert.Equal(35, ids.Count);
}
Base64Url lives in System.Buffers.Text and is available from .NET 9, so there’s no hand-rolled Replace('+', '-') to get wrong. Run the keyset cases:
dotnet test --filter "FullyQualifiedName~Keyset|FullyQualifiedName~No_surviving"
The two keyset tests pass, including the insert scenario with the same 35 rows and no repeats. The summary line also shows the one offset case still failing (the No_surviving theory runs both rows):
Failed Paging.PagingInvariants.No_surviving_order_is_skipped_when_seen_orders_are_deleted_mid_walk(kind: "offset") [21 ms]
Failed! - Failed: 1, Passed: 3, Skipped: 0, Total: 4, Duration: 1 s - Paging.dll (net10.0)
In the real project, delete OffsetPager and its cases once the endpoint is switched over. They exist here to prove the harness can fail.
What the rewrite costs
Offset paging has a second problem the invariants don’t show: the database still has to walk and discard every skipped row. The EF Core docs call that out, noting the load grows with the number of rows skipped. A quick way to see it is to load 200,000 orders and time a deep page against the equivalent keyset page:
var offset = db.Orders.OrderByDescending(o => o.PlacedUtc).Skip(150000).Take(10);
var keyset = db.Orders.Where(o => o.PlacedUtc < placed || (o.PlacedUtc == placed && o.Id < 100000))
.OrderByDescending(o => o.PlacedUtc).ThenByDescending(o => o.Id).Take(11);
ToQueryString() prints the SQL EF Core generates for each:
SELECT "o"."Id", "o"."PlacedUtc", "o"."Reference"
FROM "Orders" AS "o"
ORDER BY "o"."PlacedUtc" DESC
LIMIT @p1 OFFSET @p
SELECT "o"."Id", "o"."PlacedUtc", "o"."Reference"
FROM "Orders" AS "o"
WHERE "o"."PlacedUtc" < @placed OR ("o"."PlacedUtc" = @placed AND "o"."Id" < 100000)
ORDER BY "o"."PlacedUtc" DESC, "o"."Id" DESC
LIMIT @p
Twenty runs of each against in-memory SQLite took 214 ms for the offset query at position 150,000 and 17 ms for the keyset query, so roughly 11 ms against under 1 ms per call. Treat those figures as a shape and not a benchmark: SQLite in memory has no network, no buffer pool pressure and one connection, and a production database will give different absolute numbers. What carries over is that the keyset query does the same work on page 15,000 as on page 1, as long as the (PlacedUtc, Id) index from the context exists. That index is not optional. Without it, the ORDER BY plus WHERE becomes a sort of the whole table on every request.
Make it yours
Three variations cover most real endpoints.
A user-selectable sort. If the client can sort by Total or Customer, the cursor has to carry that column’s value and the query has to switch the comparison direction with the sort direction. Parameterise the invariants over each sort the endpoint offers, so the walk runs once per sort key. Every sort key still needs the Id tie-breaker and its own composite index.
Filters. A filter that changes between page requests produces a different list and any paging scheme will mislead the client, so bake the filter into the cursor or reject a cursor whose filter doesn’t match the request.
PostgreSQL, SQL Server and other databases with row values. The EF Core docs note that most SQL databases support WHERE (Date, Id) > (@lastDate, @lastId), which is simpler and can be more efficient, but that EF Core can’t express it in LINQ (tracked as dotnet/efcore#26822). If the OR form shows up as a problem in a query plan, FromSql is the way out. That path wasn’t run here, so check the plan on your own database before and after.
The harness itself ports to other stacks. In TypeScript, a Vitest walk over a Prisma, Drizzle or hand-written endpoint has the same shape: a walk(fetch, between) helper, two assertions that list ids, and the same four scenarios.
What goes wrong
The tests don’t cover updates to the sort column. If an order’s PlacedUtc can change after creation, a row can jump from behind the cursor to in front of it, or the reverse, and no keyset scheme fixes that. Sort by an immutable column (creation time, not “last modified”) or accept that the list is a best-effort view.
A garbage cursor crashes Decode. Base64Url.DecodeFromChars and the long.Parse calls throw on bad input, so a client sending ?cursor=hello gets a 500. Catch the exceptions at the endpoint and return a 400. Add a scenario that sends one.
Keyset paging has no random access. There’s no “jump to page 40”, only next and, with more work, previous. The EF Core docs say so plainly and suggest asking whether a real use case needs random access at all. If one does, a hybrid works: keyset for next and previous, offset for jumps.
The cursor isn’t a security boundary. It’s base64 of two numbers, readable and forgeable. That’s fine for ordering, but an endpoint must still check authorisation on every row it returns, because a forged cursor can point anywhere in the table.
Don’t bother with any of this for a list that has a few hundred rows total, for an admin screen where one person is the only writer, or for a table that only ever gets appended to and is read oldest-first. Offset paging is wrong under concurrent change and slow at depth, and neither applies there.
First 15 minutes
Pick one list endpoint in a codebase you own that uses Skip and Take. Add the Walk helper and the two mutation scenarios from this article, pointed at that endpoint’s query. Run them and read the ids in the failure messages. If they fail, paste the prompt above into your agent of choice with the file names swapped, and let the test output be the judge of what it produces.