dotnet-cursor-pagination-efcore

Cursor-based (keyset) pagination vs offset pagination in ASP.NET Core Web API — with EF Core, xUnit tests, and Swagger UI.

---

🚀 Support the Channel — Join on Patreon

If this sample saved you time, consider joining our Patreon community. You'll get exclusive .NET tutorials, premium code samples, and early access to new content — all for the price of a coffee.

👉 Join CodingDroplets on Patreon

Prefer a one-time tip? Buy us a coffee ☕

---

🎯 What You'll Learn

• Why offset pagination (Skip/Take) degrades at scale — and what to use instead
• How to implement cursor-based (keyset) pagination with EF Core for O(1) page seeks
• How to expose both strategies via a clean ASP.NET Core Web API
• How to design a PagedResult<T> envelope with NextCursor and HasNextPage
• How to write xUnit tests that walk all pages and verify no duplicates or gaps
• Which database indexes power cursor pagination in SQL Server
• When to choose cursor vs offset (comparison table included)

---

🗺️ Architecture Overview

┌─────────────────────────────────────────────────────────────────┐
│                          Client / Swagger UI                     │
│                                                                  │
│   GET /api/products/cursor?pageSize=10          (first page)     │
│   GET /api/products/cursor?afterCursor=10&pageSize=10  (page 2) │
│   GET /api/products/offset?page=2&pageSize=10  (offset style)    │
└────────────────────────────┬────────────────────────────────────┘
                             │ HTTP
                             ▼
┌─────────────────────────────────────────────────────────────────┐
│               ProductsController  (ASP.NET Core)                │
│  • Validates query parameters                                    │
│  • Delegates to IProductRepository                              │
│  • Returns ActionResult<PagedResult<T>>                          │
└────────────────────────────┬────────────────────────────────────┘
                             │ DI
                             ▼
┌─────────────────────────────────────────────────────────────────┐
│               ProductRepository  (EF Core)                      │
│                                                                  │
│  CURSOR PAGINATION (keyset seek):                               │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │ WHERE Id > @cursor  ORDER BY Id ASC  TAKE @pageSize + 1  │   │
│  │  → O(1) index seek regardless of depth in table          │   │
│  └──────────────────────────────────────────────────────────┘   │
│                                                                  │
│  OFFSET PAGINATION (for comparison):                            │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │ ORDER BY Id ASC  SKIP @offset  TAKE @pageSize            │   │
│  │  → O(N) full scan, degrades on large tables              │   │
│  └──────────────────────────────────────────────────────────┘   │
└────────────────────────────┬────────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────────┐
│          AppDbContext  (EF Core InMemory / SQL Server)           │
│  • 50 seeded products                                           │
│  • Indexes: IX_Products_Id (PK), IX_Products_CreatedAt_Id       │
└─────────────────────────────────────────────────────────────────┘

---

📋 Cursor vs Offset: Summary

Property	Cursor Pagination	Offset Pagination
Performance at scale	✅ O(1) — index seek	❌ O(N) — full scan
Stable results	✅ Insert-safe	❌ Row drift on insert/delete
Jump to page N	❌ Not supported	✅ Supported
Total record count	❌ Not required	✅ Returned in response
SQL pattern	WHERE Id > cursor	SKIP offset TAKE size
Best for	Infinite scroll, feeds, large datasets	Admin tables, paginated UIs
Index requirement	Must index sort column	Benefits from index

---

📁 Project Structure

dotnet-cursor-pagination-efcore/
├── dotnet-cursor-pagination-efcore.sln
│
├── src/
│   └── CursorPagination.Api/
│       ├── Controllers/
│       │   └── ProductsController.cs      # GET /api/products/cursor + /offset
│       ├── Data/
│       │   └── AppDbContext.cs            # EF Core context + seed data
│       ├── Models/
│       │   ├── Product.cs                 # Entity (Id, Name, Category, Price, CreatedAt)
│       │   ├── PagedResult.cs             # Cursor pagination envelope
│       │   └── OffsetPagedResult.cs       # Offset pagination envelope
│       ├── Repositories/
│       │   ├── IProductRepository.cs      # Abstraction
│       │   └── ProductRepository.cs       # EF Core implementation
│       ├── Properties/
│       │   └── launchSettings.json        # Swagger opens automatically
│       └── Program.cs                     # DI, EF, Swagger setup
│
└── tests/
    └── CursorPagination.Tests/
        └── ProductRepositoryTests.cs      # 12 xUnit tests

---

🛠️ Prerequisites

Tool	Version
.NET SDK	10.0+
IDE	Visual Studio 2022 / VS Code / JetBrains Rider

---

⚡ Quick Start

# 1. Clone the repo
git clone https://github.com/codingdroplets/dotnet-cursor-pagination-efcore.git
cd dotnet-cursor-pagination-efcore

# 2. Build
dotnet build -c Release

# 3. Run the API
dotnet run --project src/CursorPagination.Api --configuration Release

# 4. Open Swagger UI
# → http://localhost:5289/swagger

Visual Studio users: Press F5 — Swagger opens automatically.

---

🔧 How It Works

Cursor Pagination (Recommended)

The cursor is the Id of the last item returned on the current page. The next page query uses WHERE Id > cursor, which hits the primary-key index for an O(1) seek.

Step 1 — First page (no cursor)

GET /api/products/cursor?pageSize=10

Response:

{
  "items": [ { "id": 1, ... }, ..., { "id": 10, ... } ],
  "nextCursor": 10,
  "previousCursor": null,
  "hasNextPage": true,
  "hasPreviousPage": false,
  "count": 10
}

Step 2 — Next page (pass the cursor)

GET /api/products/cursor?afterCursor=10&pageSize=10

Step 3 — Keep paginating until hasNextPage is false

Repository implementation (core logic)

// ProductRepository.cs
var query = _db.Products.AsNoTracking().OrderBy(p => p.Id);

if (afterCursor.HasValue)
{
    // Exclusive seek — only rows AFTER the cursor position
    query = (IOrderedQueryable<Product>)query.Where(p => p.Id > afterCursor.Value);
}

// Fetch one extra to detect whether another page exists
var items = await query.Take(pageSize + 1).ToListAsync();

var hasNextPage = items.Count > pageSize;
if (hasNextPage)
    items.RemoveAt(items.Count - 1); // Remove sentinel

return new PagedResult<Product>
{
    Items = items,
    NextCursor = hasNextPage ? items[^1].Id : null
};

Offset Pagination (For Comparison)

GET /api/products/offset?page=1&pageSize=10

{
  "items": [ ... ],
  "page": 1,
  "pageSize": 10,
  "totalCount": 50,
  "totalPages": 5,
  "hasNextPage": true,
  "hasPreviousPage": false
}

The underlying EF Core query:

var items = await _db.Products
    .AsNoTracking()
    .OrderBy(p => p.Id)
    .Skip((page - 1) * pageSize)   // ← O(N) full scan
    .Take(pageSize)
    .ToListAsync();

---

📡 API Endpoints

Method	Endpoint	Description	Status
GET	/api/products/cursor	Cursor-based pagination (recommended)	200 OK
GET	/api/products/offset	Offset pagination (comparison)	200 OK

Cursor endpoint query parameters

Parameter	Type	Default	Description
afterCursor	int?	null	Id of last item on previous page. Omit for first page.
pageSize	int	10	Items per page (1–100).

Offset endpoint query parameters

Parameter	Type	Default	Description
page	int	1	1-based page number.
pageSize	int	10	Items per page (1–100).

---

🧪 Running Tests

dotnet test -c Release --logger "console;verbosity=normal"

Test	What It Verifies
GetPagedAsync_FirstPage_ReturnsCorrectItemCount	First page returns exactly pageSize items
GetPagedAsync_FirstPage_ItemsAreSortedById	Results are in stable ascending order
GetPagedAsync_FirstPageCursor_MatchesLastItemId	NextCursor equals last item Id
GetPagedAsync_SecondPage_StartsBeyondFirstPageCursor	Second page starts after cursor
GetPagedAsync_LastPage_HasNoNextCursor	HasNextPage is false on last page
GetPagedAsync_ExactPageBoundary_ReturnsFullPageAndHasNextPage	Handles exact boundary
GetPagedAsync_PageSizeClamped_WhenExceeds100	Page size is clamped to 100
GetPagedAsync_NoCursorAfterLastPage_ReturnsEmptyResult	Beyond-end cursor returns empty
GetOffsetPagedAsync_Page1_ReturnsTotalCountAndItems	Offset page 1 returns total count
GetOffsetPagedAsync_LastPage_HasCorrectCount	Offset last page metadata is correct
GetOffsetPagedAsync_BeyondLastPage_ReturnsEmpty	Out-of-range page returns empty
GetPagedAsync_FullTraversal_YieldsAll50Products	Full walk: 50 unique items, no gaps

Result: 12/12 passing ✅

---

🤔 Key Concepts

Why cursor pagination is faster

Offset pagination forces the database to scan and discard N rows before returning results:

-- Offset: scans all 1,000,000 rows, discards first 999,990
SELECT * FROM Products ORDER BY Id OFFSET 999990 ROWS FETCH NEXT 10 ROWS ONLY;

Cursor pagination seeks directly to the cursor position using the index:

-- Cursor: seeks directly to Id=999990 via index — O(1)
SELECT TOP 11 * FROM Products WHERE Id > 999990 ORDER BY Id;

Required SQL Server Indexes

-- Primary key covers the basic cursor seek on Id
CREATE UNIQUE INDEX IX_Products_Id ON Products (Id);

-- Composite index for cursor pagination on multiple sort columns
CREATE INDEX IX_Products_CreatedAt_Id ON Products (CreatedAt ASC, Id ASC);

Pagination Strategy Decision

Is the table > ~10,000 rows?
├── YES → Use cursor pagination
│         Does the UI need "jump to page N"?
│         ├── YES → Hybrid: cursor for API calls + total count query for display
│         └── NO  → Pure cursor (feeds, infinite scroll)
└── NO  → Either works; offset is simpler if you need total count

---

🏷️ Technologies Used

Technology	Role
.NET 10 / ASP.NET Core 10	Web API framework
Entity Framework Core 10	ORM + InMemory provider
EF Core SQL Server	Production DB provider (swap-in ready)
Swashbuckle / Swagger UI	API documentation
xUnit 2	Unit testing framework
FluentAssertions	Readable test assertions

---

📚 References

• Keyset Pagination — Use The Index, Luke
• Implement Offset and Keyset Pagination — Microsoft Docs
• ASP.NET Core Web API — Microsoft Docs

---

📄 License

This project is licensed under the MIT License.

---

🔗 Connect with CodingDroplets

Platform	Link
🌐 Website	https://codingdroplets.com/
📺 YouTube	https://www.youtube.com/@CodingDroplets
🎁 Patreon	https://www.patreon.com/CodingDroplets
☕ Buy Me a Coffee	https://buymeacoffee.com/codingdroplets
💻 GitHub	http://github.com/codingdroplets/

Want more samples like this? Support us on Patreon or buy us a coffee ☕ — every bit helps keep the content coming!