Handling Large Datasets in EF Core: The Cost of Skip and Take

The Problem: Unstable Pages and Degraded Performance

When implementing pagination for a large dataset, it is common to use Skip() and Take(). However, developers often encounter two critical issues: records appearing on multiple pages (or disappearing entirely) and a noticeable increase in latency as the user navigates to higher page numbers.

Thesis: Efficient pagination requires server-side execution and deterministic ordering

For Skip and Take to be viable, the query must be executed entirely on the database provider and sorted by a unique key. Without these conditions, you risk loading entire tables into application memory or delivering inconsistent data to the user.

How EF Core Translates Pagination to SQL

EF Core uses the IQueryable interface to defer execution. When you chain pagination methods, EF Core translates them into provider-specific SQL. In SQL Server, this typically results in OFFSET and FETCH NEXT clauses.

Consider this implementation for a product catalog:

// Run this within a service method using your DbContext
int pageNumber = 5; 
int pageSize = 20;

var products = await _context.Products
    .Where(p => p.IsActive)
    .OrderBy(p => p.Category)
    .ThenBy(p => p.ProductId) // Essential for stability
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

The resulting SQL will look similar to this:

SELECT [p].[ProductId], [p].[Category], [p].[Name]
FROM [Products] AS [p]
WHERE [p].[IsActive] = 1
ORDER BY [p].[Category], [p].[ProductId]
OFFSET 80 ROWS FETCH NEXT 20 ROWS ONLY;

Critical Engineering Pitfalls

• Non-Deterministic Ordering: If you order by a column with duplicate values (like Category) without a secondary unique tie-breaker (like ProductId), the database does not guarantee the order of rows with the same value. This causes records to shift between pages.
• Premature Materialization: Calling .ToList() or .ToArray() before .Skip() forces the entire filtered dataset into the web server's RAM. This is a common cause of OutOfMemoryException in production.
• The Deep Paging Trap: OFFSET is not a constant-time operation. To skip 100,000 rows, the database must still scan through those 100,000 rows before returning the next 20. Performance degrades linearly as the offset increases.

Verification and Diagnostics

To ensure your pagination is executing on the server and not in memory, you can inspect the generated SQL. In your DbContext configuration, enable logging to the console:

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

Check: If the log shows a SELECT statement without OFFSET or LIMIT, but your C# code uses Skip, you have a client-side evaluation bug.

Alternative: Keyset Pagination (The Seek Method)

For datasets where users navigate deep into the results, replace Skip with a filter based on the last seen record. This allows the database to use an index to jump directly to the starting point.

// Instead of Skip(10000), use the ID of the last item from the previous page
int lastSeenId = 10000;

var nextPage = await _context.Products
    .Where(p => p.ProductId > lastSeenId)
    .OrderBy(p => p.ProductId)
    .Take(pageSize)
    .ToListAsync();

Trade-off: Keyset pagination is significantly faster for large offsets but prevents "jumping" to a specific page number (e.g., Page 500) because the query requires the state of the previous page.

Actionable Summary

1. Enforce Uniqueness: Always end your OrderBy chain with a primary key or unique column.
2. Order of Operations: Ensure Skip() and Take() occur before any terminal operator like ToListAsync().
3. Monitor Offsets: If your application allows users to navigate beyond a few thousand records, prototype a keyset pagination approach to avoid OFFSET performance degradation.
4. Verify SQL: Use LogTo during development to confirm the database is handling the pagination logic.