BuildingBlocks.Pagination.EntityFrameworkCore 1.0.0

Production-oriented cursor pagination for .NET 8 / 9 / 10, built around keyset pagination with typed sort keys, composite ordering, forward/backward navigation, opaque cursors, EF Core and Dapper support, cancellation, optional total counts, and provider-aware query behavior.

The library avoids string-based/reflection-driven sort selection and keeps pagination rules explicit, typed, and testable.

What's Included

• Typed sorting with SortKey / SortKeyRegistry
• Keyset pagination with composite keys and unique tie-breakers
• Forward and backward navigation with NextCursor / PreviousCursor
• Last-page navigation using PageDirection.Backward
• Opaque cursor encoding and validation
• Optional HMAC cursor signing
• Optional total counts with IncludeTotalCount
• CancellationToken throughout EF Core and Dapper execution
• Nullable string ordering with NullOrder
• HasKeysetIndex for recommended composite indexes
• Provider-aware QueryHint with a deliberately small allowlist

EF Core

• ToCursorPageAsync(...) for keyset pagination
• Typed SortKey / SortKeyRegistry instead of runtime property-name reflection
• Composite ordering such as Price + Id, Name + Id, and CreatedAt + Id
• Forward and backward cursor navigation
• Optional COUNT + PAGE
• SQL Server ReadUncommitted support
• Explicit restoration of READ COMMITTED after SQL Server hint operations
• PostgreSQL and SQLite treat the SQL Server-only hint as a no-op

Dapper

• QueryCursorPageAsync(...)
• Same cursor/keyset model as the EF Core adapter
• SQL Server-specific query-hint handling
• COUNT and page queries receive the same hint behavior

Dapper remains an in-repository adapter and is not packed into this NuGet package.

FeatureFusion Showcase

FeatureFusion provides a runnable integration showcase using a real PostgreSQL product catalog.

The same GetProductsQuery powers:

• GET /api/v2/products-page
• POST /api/v2/products-page
• POST /api/v2/Product/products
• POST /api/v2/Product/products-dapper
• MCP products.list

The catalog demonstrates typed product sort keys, composite Price + Id, Name + Id, and CreatedAt + Id ordering, unique tie-breakers, forward/backward navigation, first-page totals, last-page navigation, opaque cursors, cancellation, and EF Core + Dapper against the same PostgreSQL data.

Cursor navigation

First page:

GET /api/v2/products-page?limit=20&sortBy=Price&sortDirection=Ascending

Next page:

GET /api/v2/products-page?limit=20&sortBy=Price&sortDirection=Ascending&cursor=<NextCursor>

Previous page:

GET /api/v2/products-page?limit=20&sortBy=Price&sortDirection=Ascending&cursor=<PreviousCursor>

Last page:

GET /api/v2/products-page?limit=20&pageDirection=Backward

Cursors are opaque and should be returned unchanged.

Performance Validation

The repository includes a reproducible --probe using large SQLite datasets from the FeatureFusion benchmark catalog.

Returned IDs are validated against a live OFFSET query before timing. The validation itself is not timed.

10M rows, skip 5M

• OFFSET: 154.9 ms
• FeatureFusion keyset: 17.8 ms
• MR: 19.9 ms

100M rows, skip 50M

• OFFSET: 2470.4 ms
• FeatureFusion keyset: 177.2 ms
• MR: 218.0 ms

These are Stopwatch probe results from the FeatureFusion benchmark catalog on the documented hardware. They are not universal performance claims, and SQLite results should not be interpreted as SQL Server or PostgreSQL performance results.

At a 50M-row skip in this catalog, FeatureFusion keyset pagination measured approximately 14x faster than OFFSET in this particular probe.

QueryHint

QueryHint intentionally exposes only:

• None
• ReadUncommitted

For SQL Server:

• EF Core uses one ReadUncommitted transaction around COUNT + PAGE when there is no ambient transaction.
• Ambient transactions are not nested; the hint is ignored.
• The SQL Server session is explicitly restored to READ COMMITTED.
• Restoration also occurs after exceptions and cancellation.
• Dapper applies the isolation level for the operation and restores it afterward when the connection remains open.
• No WITH (NOLOCK), UPDLOCK, or arbitrary raw hint strings are generated.

For PostgreSQL and SQLite, ReadUncommitted is a no-op.

Validation

Pagination v1.0 was validated across:

• .NET 8 / 9 / 10
• Core pagination tests
• EF Core SQLite execution tests
• Dapper SQL-generation and execution tests
• SQL Server QueryHint integration tests using Aspire
• PostgreSQL QueryHint no-op integration test
• FeatureFusion PostgreSQL integration tests
• Cancellation and cancellation-after-command-start
• Sort-column mutation scenarios
• Nullable-string ordering
• Forward and backward pagination
• Cursor validation
• COUNT + PAGE behavior
• EF Core and Dapper consistency

The SQL Server integration suite verifies isolation cleanup on an already-open connection after successful execution, exceptions, and cancellation.

What's Changed

• Finalize BuildingBlocks.Pagination v1.0.0
• Add typed keyset/cursor pagination for EF Core
• Add forward and backward cursor navigation
• Add composite sort keys and recommended keyset indexes
• Add optional COUNT + PAGE support
• Add cancellation support
• Add in-repository Dapper adapter
• Add SQL Server QueryHint support with isolation cleanup
• Expand provider and integration test coverage
• Add 10M / 100M large-dataset performance probes
• Add FeatureFusion PostgreSQL pagination showcase
• Improve package and repository documentation

Documentation

• PACKAGE_README.md
• docs/building-blocks/pagination.md
• docs/adr/0003-pagination-keyset.md
• docs/building-blocks/PAGINATION_TEST_MATRIX.md

Full Changelog:
https://github.com/Maxofpower/FeatureFusion/commits/pagination-v1.0.0

Compare:
...pagination-v1.0.0

What's Changed

• feat(pagination): finalize cursor pagination v1.0.0 by @Maxofpower in #19

Full Changelog: mcp-v1.0.0...pagination-v1.0.0