Introduction
When building modern applications, performance and scalability are everything. Whether we’re designing an API in .NET, a Blazor UI, or an admin dashboard, you’ll often deal with large datasets.
Showing all records at once is neither practical nor efficient — that’s where **pagination in EF Core** comes into play.
In this blog, we’ll:
- Understand what pagination is and why it matters.
- Explore pagination strategies in EF Core.
- Compare Offset Pagination vs Cursor Pagination.
- Run a BenchmarkDotNet test to measure performance.
What is Pagination?
Pagination is the technique of splitting a dataset into smaller chunks (pages) instead of retrieving everything at once.
For example:
- Instead of fetching 100,000 rows in one query, we load them in pages of 1,000 rows each.
- Users can then move through Page 1 → Page 2 → Page 3, or use infinite scrolling.
This ensures:
- Better performance — avoids memory overload.
- Smooth UX — users see manageable chunks of data.
- Scalability — applications remain responsive with millions of records.
- Network optimization — smaller payloads = faster API responses.
👉 In a Blazor QuickGrid UI, pagination helps you fetch only the rows needed for the current viewport. You can check out detailed guide on Blazor and .NET 8 QuickGrid Implementation.
Two Common Pagination Strategies in EF Core
- Offset Pagination – using Skip() and Take().
- Cursor (Keyset) Pagination – using WHERE ID > lastSeenId .
Offset Pagination in EF Core
Offset pagination uses Skip() and Take() :
SQL Generated:
Returns rows 4001–5000, but requires scanning the first 4000 rows before
returning results.
How It Works
- The client requests page N.
- Database skips (N - 1) * pageSize rows, then returns the next pageSize .
- Example: Page 5 (1000 per page) → OFFSET 4000 .
- Database must scan 4000 rows first before fetching 1000.
Performance Implications
- Page 1 → OFFSET 0 → Fast.
- Page 5 → OFFSET 4000 → Database scans 4000 rows, then returns 1000.
As datasets grow into millions, this becomes painfully slow.
✅ Pros
- Simple to implement.
- Works well for small datasets.
- Supports “Go to Page X”.
❌ Cons
- Performance degrades on large page numbers.
- Not ideal for infinite scroll or real-time apps.
Cursor Pagination in EF Core
Cursor pagination fetches rows after the last seen Id:
SQL Generated:
Returns rows 5001–6000, directly using an index seek on Id .
How It Works
- Instead of "jumping to page 5", the client says:
“Give me the next 1000 records after Id=5000.” - Database directly jumps to the required position using the index.
Performance Implications
- Page 1 → Fetch first 1000 rows.
- Page 5 → Uses Id > 5000 , still constant performance (no scanning thousands
of skipped rows). - Even on page 500 or page 5000, query remains fast.
✅ Pros
- Much faster on large datasets.
- Ideal for infinite scroll.
❌ Cons
- Cannot jump directly to “Page X” – must navigate sequentially.
- Requires stable unique ordering (e.g., Id or CreatedAt ).
Offset vs Cursor Pagination (Comparison Table)
| Feature | Offset Pagination | Cursor Pagination |
|---|---|---|
| Implementation | Easy ( SKIP / LIMIT ) | Slightly complex ( WHERE id > N ) |
| Fast | Fast | Concerns are separated by feature (end-to-end functionality in one place) |
| Performance (Large Data) | Slows down (scans skipped rows) | Constant (index seek) |
| Go to Page X | ✅ Supported | ❌ Not Supported |
| Infinite Scrolling | ❌ Not efficient | ✅ Very efficient |
Benchmarking Offset vs Cursor Pagination
Conclusion
- Choose Offset Pagination when you need traditional "Go to Page X"
navigation. - Prefer Cursor Pagination for APIs, Blazor QuickGrid infinite scrolling, or large
datasets, where performance and scalability are critical. - For maximum efficiency, consider combining Cursor Pagination with Hybrid
Caching in .NET to reduce redundant queries and serve results faster.
Closing Note
While this blog shows examples with EF Core, the concepts of Offset and Cursor
Pagination apply broadly. You can implement them with Dapper, ADO.NET, raw
SQL, or even in NoSQL databases like MongoDB or Cosmos DB.