Stable pagination with a composite keyset

A page cursor needs a fully specified order

Rows have no guaranteed relational order without an explicit ordering. A SortKey can have ties, so the lesson adds Id as a unique second key and carries both values into the cursor. The next-page predicate must express the same lexicographic order: a larger SortKey, or an equal SortKey with a larger Id. Leaving out one component can skip or repeat tied rows. This is a forward-page contract rather than a generic promise that a resultset remains frozen while other users change it. Page size, tenant scope and cursor validation remain additional application decisions.

Insert before the cursor and verify the next real page

SQLite stores A and B at sort key ten and C and D at twenty. The first ordered Take(2) yields A,B and captures B’s complete cursor. The fixture inserts NewBefore at sort key five, then queries strictly after the earlier pair and materializes C,D. Assertions verify both pages rather than treating a generated predicate as sufficient evidence. The configured two-column index aligns with that query shape, but the test does not claim a measured plan or universal index use. A real production plan still depends on provider, statistics, selected columns and workload.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using var db = new LabDb(options); await db.Database.EnsureCreatedAsync();
db.Items.AddRange(new Item { Id = 1, Name = "A", SortKey = 10 }, new Item { Id = 2, Name = "B", SortKey = 10 }, new Item { Id = 3, Name = "C", SortKey = 20 }, new Item { Id = 4, Name = "D", SortKey = 20 }); await db.SaveChangesAsync();
var first = await db.Items.AsNoTracking().OrderBy(x => x.SortKey).ThenBy(x => x.Id).Take(2).ToListAsync(); var cursor = first.Last();
db.Items.Add(new Item { Id = 5, Name = "NewBefore", SortKey = 5 }); await db.SaveChangesAsync();
var next = await db.Items.AsNoTracking().Where(x => x.SortKey > cursor.SortKey || x.SortKey == cursor.SortKey && x.Id > cursor.Id).OrderBy(x => x.SortKey).ThenBy(x => x.Id).Take(2).ToListAsync();
Check(string.Join(",", first.Select(x => x.Name)) == "A,B" && string.Join(",", next.Select(x => x.Name)) == "C,D", "exclusive composite keyset");
Console.WriteLine("first:" + string.Join(",", first.Select(x => x.Name))); Console.WriteLine("next:" + string.Join(",", next.Select(x => x.Name)));

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

Trace the exclusive composite boundary on supplied pairs

The BCL practice receives four key pairs, excludes pairs not strictly after ten/two and returns IDs three and four. Ordering and Take are ordinary Enumerable operations over supplied values. This establishes the intended tie-breaking rule without a database cursor implementation or SQL translation claim. Additions before the anchor cannot enter that particular predicate, but changes to existing sort keys can still move rows across it. Keep the model narrow enough that it remains a useful independent expected-result check rather than pretending to implement transaction isolation or a complete pagination service.

Keysets improve a boundary but do not create a snapshot

Offset pages can shift when preceding rows are inserted or removed, while a keyset asks about a recorded ordering boundary. It still does not provide a transaction-wide immutable snapshot, handle arbitrary reordering automatically or grant permission to use any client-supplied tenant cursor. A deleted anchor can be represented by its stored key values, but the application must decide how long such cursors remain meaningful. Backward navigation requires its own reversed-order strategy. A cursor carrying a date alone is insufficient if IDs break ties. Make these policies explicit before exposing encoded cursor text as a public API.

Practice: test ties and a changed sort key

Add more rows sharing the same SortKey and fetch several pages, verifying that each expected ID appears once under an unchanged ordered set. Then modify an existing row’s SortKey between page requests and document what the current contract permits. Do not rewrite the test expectation as if keyset pagination guaranteed snapshot membership. Inspect the actual SQL and an execution plan on realistic data before making performance claims about the index. Keep the provided insertion-before-anchor case as a focused regression, because it distinguishes the keyset rule from an offset that simply counts preceding positions.

• Hint: every key in OrderBy/ThenBy must be represented by the cursor predicate.
• Hint: unique ordering is necessary, but it does not freeze mutable data.

Practice: validate cursor and page-size inputs

Extend the supplied-pair helper with a bounded positive page size and a structured two-field cursor. Test zero size, an oversized size, equal keys and an empty result independently. If the public transport encodes the cursor as text, validate its shape before passing values to the pure rule and obtain tenant authority separately. Encoding is not authorization or integrity protection. Keep the .NET9 model free of fake IQueryable types. Its job is to specify allowed inputs and expected ordered IDs; the real external provider must still translate and execute the corresponding relational predicate.

• Hint: a valid-looking cursor is still untrusted application input.
• Hint: choose an explicit policy for invalid page sizes rather than silently creating unbounded queries.

Review identity, order and consistency separately

The external run establishes two concrete pages after an insertion using actual SQLite translation and materialization. The portable lab establishes the supplied key comparison. Neither proves a production query plan, a cursor-security mechanism or cross-request snapshot consistency. A useful handoff records the complete ordering, exclusive predicate, page-size bound, cursor scope and mutable-sort policy. Preserve the distinction when adding filters or joins: a seemingly small query change can alter ordering uniqueness, row multiplicity or the meaning of an earlier cursor even when the method still ends with Take.