Pagination

Last month, our customer portal started timing out. Users browsing past page 1,000 of their order history were met with a dreaded loading spinner that never went away. A quick look at the logs confirmed it: our classic offset-based pagination was hitting a table with 2.3 million orders, and query times had jumped from 50ms to over 4 seconds. SQL Server was burning up CPU cycles.

The culprit was Skip() and Take(). This one has burned me in production more than once. The fix was switching to keyset pagination (sometimes called cursor-based pagination), a technique that uses the last seen value to find the next page instead of counting rows to skip.

Here’s how it works and how to implement it properly in EF Core.

Why Skip() and Take() Grind to a Halt

Standard pagination in LINQ uses Skip(page * pageSize).Take(pageSize). Under the hood, this translates to SQL’s OFFSET and FETCH NEXT. For SQL Server to skip 50,000 rows, it has to actually read all 50,000 rows first.

Here’s the generated SQL for fetching page 2,501 (with a page size of 20):

-- This gets painfully slow as the offset increases
SELECT * FROM Orders 
ORDER BY OrderId 
OFFSET 50000 ROWS FETCH NEXT 20 ROWS ONLY;

Even with a perfect index on OrderId, the database still has to scan 50,020 rows from the index just to return the final 20. It’s an enormous amount of wasted work that gets worse with every page you click.

The Fix: Keyset Pagination (The “Seek Method”)

Instead of telling the …