EF Core ↔ SQL Server: OFFSET/FETCH vs Keyset Pagination for Large Tables

When retrieving pages from a table that contains hundreds of thousands of rows, EF Core’s Skip/Take operators are the most straightforward option. They are translated to SQL OFFSET/FETCH clauses when an OrderBy is present, giving a simple page‑based API. However, each OFFSET forces the database to read and discard all preceding rows, which can become a bottleneck as page numbers grow.

The design space is constrained by several factors. A deterministic OrderBy is mandatory; otherwise the generated SQL can return non‑stable page results. The database provider influences performance: PostgreSQL handles OFFSET/FETCH efficiently, while SQLite does not. Keyset (seek) pagination requires a unique, monotonic ordering column and a WHERE clause on the last seen key, but EF Core does not expose a built‑in API for it.

Because EF Core lacks native keyset support, developers must decide whether to accept the potential performance hit of OFFSET/FETCH or to implement custom keyset logic. The choice also depends on data volatility, index design, and whether page stability is critical. I need guidance on the trade‑offs and practical patterns for integrating keyset pagination into an EF Core workflow.

Which pagination technique—OFFSET/FETCH or keyset—should I favor for a high‑volume, frequently updated table when using EF Core 7 on PostgreSQL?
What patterns can I adopt in EF Core to support keyset pagination without resorting to raw SQL or third‑party libraries?