ASP.NET Core API Pagination: Offset vs Cursor vs Keyset — Enterprise Decision Guide

Choosing the wrong pagination strategy in an ASP.NET Core API is one of those decisions that feels harmless in development and becomes a production nightmare at scale. The three dominant strategies — offset pagination, cursor pagination, and keyset (seek) pagination — each carry distinct trade-offs around performance, client complexity, and data consistency. Understanding those trade-offs is what separates a well-designed enterprise API from one that quietly degrades as your dataset grows.

🎁 Want implementation-ready .NET source code you can drop straight into your project? Join Coding Droplets on Patreon for exclusive tutorials, premium code samples, and early access to new content. 👉 https://www.patreon.com/CodingDroplets

Pagination is not just a UX concern. For APIs that serve thousands of concurrent requests against tables with millions of rows, the choice between SKIP/TAKE, a cursor token, or a keyset clause changes your SQL execution plan, your database index usage, and your overall throughput ceiling. Getting this right early is one of the highest-leverage architectural decisions a .NET team can make.

What Is Offset Pagination?

Offset pagination is the approach most developers reach for first. You accept a page number and a pageSize from the client, then translate that into a Skip() and Take() query in EF Core. The SQL equivalent is OFFSET n ROWS FETCH NEXT m ROWS ONLY.

The appeal is obvious: it is intuitive for both the API consumer and the developer implementing it. Clients can jump directly to page 50 without fetching pages 1 through 49 first. Admin dashboards with numbered page controls map directly to this model.

The problem surfaces as the dataset grows. When you skip 50,000 rows, the database still has to scan and discard those 50,000 rows before returning your page. On a table with millions of records, deep-page queries cause full or partial index scans, escalating CPU time and I/O with every page advance. A query that runs in 5ms on page 1 can take 800ms on page 5,000 — on the same index, the same query shape, just a different OFFSET value.

When to Use Offset Pagination:

• Small-to-medium datasets where deep pagination is rare in practice
• Admin interfaces with numbered page navigation
• Read-heavy endpoints where the client needs random page access
• Situations where simplicity and developer familiarity outweigh scale concerns

When Not to Use Offset Pagination:

• Tables with more than 100,000 rows where users regularly reach deep pages
• High-frequency API endpoints under significant concurrent load
• Any scenario where stale pages (duplicate or missing items due to inserts during traversal) are a data integrity risk

What Is Cursor Pagination?

Cursor pagination replaces the page number with an opaque token. The server processes the first request without a cursor, returns a set of results, and includes a nextCursor token in the response. The client passes that token back on the next request to retrieve the following page.

The cursor typically encodes the identity of the last returned item — often a primary key or a combination of sort fields — though it is serialized and sometimes encrypted so clients cannot reverse-engineer it. GraphQL APIs popularized cursor pagination through the Relay connection spec, and it has since become the default for most feed-style and stream-style APIs.

From a database perspective, a cursor-based query typically translates into a WHERE id > @lastId clause — which is an index seek, not a scan. The database jumps directly to the cursor position and reads forward, regardless of how far into the dataset you are. This is why well-implemented cursor pagination performs consistently across all pages.

When to Use Cursor Pagination:

• Real-time feeds and activity streams where items are inserted frequently
• APIs where consistency matters: if a new item is inserted between page 1 and page 2, cursor pagination will not show duplicates or miss items
• Mobile apps and infinite-scroll UIs where "next page" is the only navigation pattern needed
• External APIs where you want to hide internal IDs or sort keys from consumers

When Not to Use Cursor Pagination:

• When the client needs to jump to an arbitrary page number
• Bidirectional navigation (previous and next) requires careful cursor design
• When the sort key is not unique or stable — duplicate cursor values break traversal correctness

What Is Keyset (Seek) Pagination?

Keyset pagination, also called seek pagination, is structurally similar to cursor pagination but uses an explicit WHERE clause rather than an opaque token. Instead of a cursor, the client provides the actual values of the sort columns from the last returned row. The server generates a query like WHERE (created_at, id) > (@lastCreatedAt, @lastId) using a keyset predicate.

Microsoft explicitly recommends keyset pagination in the official EF Core documentation over offset pagination for large datasets. The reason is the same: it turns a SKIP into an index seek, and index seeks scale logarithmically while index scans scale linearly.

EF Core does not have a native keyset pagination API, but the open-source MR.EntityFrameworkCore.KeysetPagination library provides a clean integration that handles composite keyset predicates correctly, including null handling and descending sorts.

Keyset pagination is ideal for internal APIs and data export pipelines where the client can be trusted to pass structured sort-key values and does not need random access. It offers the best raw performance of the three strategies and is transparent enough for developers to debug without decoding opaque tokens.

When to Use Keyset Pagination:

• High-volume internal APIs and data pipeline consumers
• Any table with tens of millions of rows where offset pagination would cause performance cliffs
• ETL and bulk data export scenarios
• When index usage and query plan predictability are critical

When Not to Use Keyset Pagination:

• External-facing consumer APIs where exposing sort key values is a security or design concern
• Endpoints that require UI-friendly page numbers
• Dynamic sort orders where clients change sort columns per request (requires cursor re-generation or keyset re-negotiation)

Side-By-Side Comparison

Dimension	Offset	Cursor	Keyset
Deep-page performance	Degrades (linear scan)	Consistent (index seek)	Consistent (index seek)
Random page access	✅ Native	❌ Not supported	❌ Not supported
Data consistency under inserts	❌ Can duplicate/skip	✅ Consistent	✅ Consistent
Client complexity	Low	Medium	Medium-High
Opaque to client	N/A	✅ (cursor token)	❌ (exposes sort keys)
EF Core native support	✅ Skip()/Take()	Manual implementation	Via library
Bidirectional navigation	✅ Easy	Requires design	Requires design
Sort flexibility	Any column	Fixed after cursor issue	Rebuild cursor on change
Best for	Admin UIs, small data	Feeds, streams, mobile	ETL, internal APIs, bulk export

The Performance Cliff Problem — What It Looks Like in Production

A common production failure pattern starts like this: an API is built with offset pagination, it works perfectly in staging with 10,000 rows, it passes load testing, and it ships. Six months later the table has 3 million rows and customer support starts receiving reports of slow loading on the last pages of export screens.

The reason is that SKIP 2,950,000 TAKE 50 forces the database query engine to evaluate and discard nearly 3 million rows before returning 50. SQL Server and PostgreSQL will use the index to navigate, but they still need to count through the offset, and that count is proportional to n. At scale this is not a slow query — it is a runaway query that holds a reader lock and competes with every other transaction on that table.

Keyset or cursor pagination turns that same query into an index seek: WHERE id > 2950000 ORDER BY id TAKE 50. The database jumps directly to the row, reads 50, and returns. Execution time stays at single-digit milliseconds whether you are on row 50 or row 2,950,000.

Decision Matrix for Enterprise Teams

Use Offset Pagination when: your table has fewer than 500,000 rows, your clients need page numbers in the UI, and deep pagination is a rare edge case — not a routine user flow.

Use Cursor Pagination when: you are building a feed, a notification stream, an activity log, or any public API where you want to hide sort keys, support infinite scroll, and guarantee that clients do not see duplicates when new records are inserted during traversal.

Use Keyset Pagination when: you are building an internal data API, an ETL data feed, or a reporting endpoint against a large table. Keyset gives you the best raw performance and transparent debugging, at the cost of exposing sort key values to the consumer.

Never mix strategies within the same endpoint — if a /transactions endpoint serves both a numbered admin UI and a data pipeline export, version the API and give each consumer the strategy that fits its access pattern.

Is There a Hybrid Approach?

Yes, and it is worth considering for APIs that serve diverse client types. A common pattern is to use cursor pagination as the primary mechanism but encode a page number hint inside the cursor token server-side. This gives feed-style clients efficient traversal while letting admin UIs display "Page 5 of 12" by computing the total using a separate COUNT query — executed once and cached, not on every page request.

This hybrid requires server-side cursor state (often in Redis or a distributed cache) and adds complexity, but it eliminates the need to choose one strategy for all client types.

☕ Prefer a one-time tip? Buy us a coffee — every bit helps keep the content coming!

Anti-Patterns to Avoid

The "total count" trap: Returning a total count alongside every paginated response feels helpful but it doubles the database work — the same filter must run twice, once for the page and once for the count. Cache the total or omit it for high-frequency endpoints. Cursor-based APIs should return a hasNextPage boolean rather than a row count.

Sorting on a non-unique column: Sorting on created_at alone and using it as a keyset or cursor breaks on ties. Always include the primary key as a tiebreaker: ORDER BY created_at, id.

Ignoring index alignment: The fastest pagination in the world is useless if the ORDER BY clause does not align with a covering index. Always verify your pagination query hits an index seek in the execution plan, not a sort operator.

Using OFFSET 0 on every request: Some API clients re-fetch page 1 on every request to check for new items. This defeats the purpose of any pagination strategy. Design your API to return lastModified or etag headers so clients can use conditional requests instead.

Exposing raw cursor tokens to untrusted clients without validation: A base64-encoded cursor containing a primary key value is readable and potentially manipulable. For public APIs, either encrypt the cursor or validate that the decoded cursor value belongs to a record the caller is authorized to see.

A Note on EF Core and Pagination Libraries

For teams using EF Core, offset pagination is available out of the box with Skip() and Take(). For keyset pagination, the MR.EntityFrameworkCore.KeysetPagination NuGet package is the most production-ready option available in the .NET ecosystem — it handles composite keys, descending sorts, and null values correctly.

For cursor pagination without a third-party library, the recommended pattern is to encode the last record's sort key values as a signed JWT or encrypted string, decode and validate it on the next request, and apply the equivalent WHERE clause. This keeps cursor tokens opaque while giving the server full control over the cursor format.

Microsoft's official guidance on EF Core pagination can be found in the EF Core Querying documentation — it explicitly recommends keyset pagination over offset for large datasets and provides working examples.

FAQ

Q: What is the default pagination strategy in ASP.NET Core? ASP.NET Core does not enforce a default strategy. Most tutorials default to offset pagination using Skip() and Take() in EF Core because it is the simplest to implement. However, Microsoft's official EF Core documentation recommends keyset (seek) pagination for large datasets due to its superior index performance.

Q: When does offset pagination become a performance problem in .NET APIs? Offset pagination typically starts causing noticeable slowdowns when the table has more than 100,000–500,000 rows and users or consumers regularly access deep pages. The query cost of SKIP n scales linearly with n, so a large SKIP against an unoptimized table can take seconds regardless of indexing. Teams should benchmark and switch to keyset or cursor pagination before deploying to production at scale.

Q: Can cursor pagination and keyset pagination be used together in ASP.NET Core? Yes. Many enterprise APIs use keyset predicates server-side (for performance) while exposing opaque cursor tokens to clients (for security and encapsulation). The server decodes the cursor token, extracts the keyset values, generates the WHERE clause, and runs the keyset query. Clients see only an opaque token. This hybrid approach gives you the best of both worlds.

Q: Does EF Core natively support cursor or keyset pagination? EF Core natively supports offset pagination via Skip() and Take(). Keyset pagination requires manual WHERE clause construction or a third-party library such as MR.EntityFrameworkCore.KeysetPagination. Cursor pagination requires custom implementation — typically encoding the last sort key as a token and decoding it on subsequent requests. There is no built-in EF Core cursor or keyset API as of EF Core 10.

Q: Which pagination strategy is best for ASP.NET Core APIs with EF Core and large datasets? For large datasets (hundreds of thousands to millions of rows), keyset pagination delivers the best database performance because it turns a costly OFFSET scan into a direct index seek. For external or consumer-facing APIs where sort keys should not be exposed, cursor pagination is preferred. Offset pagination is only appropriate for small tables or scenarios where random page access is genuinely required by the UI.

Q: How do I prevent duplicate results in paginated APIs when new data is inserted? Offset pagination is the most vulnerable to this problem. If a new record is inserted at page 1 while a client is on page 2, all subsequent pages shift by one and the client sees duplicates or misses records. Cursor and keyset pagination are immune to this problem because they use a positional anchor (the last seen row) rather than a row count, so insertions before the cursor do not affect the traversal.

Q: What is keyset pagination and how does it differ from cursor pagination in ASP.NET Core? Keyset pagination uses an explicit WHERE clause containing the sort column values of the last returned row — for example, WHERE (created_at, id) > (@lastCreatedAt, @lastId). The client must pass these values directly. Cursor pagination encodes those same values into an opaque token that the server decodes. Both strategies achieve the same index seek performance, but cursor pagination hides the sort key structure from the client, making it better for external APIs.