Cursor-Based Pagination

1. What exactly is Cursor-Based Pagination?

Your ASP.NET Core API shows catalog items 20 at a time, reading them from Azure SQL sorted newest first. Page 1 shows items 1 to 20. Page 2 shows items 21 to 40. And so on.

While a user is looking at page 1, a few new items get added. New items always go to the top, since it's sorted newest first, and that pushes everything else down. So when the app asks for page 2, it still asks Azure SQL for "items 21 to 40", but which items those numbers point to has just changed. Some of them are items the user already saw on page 1.

New rows added to Azure SQL push everything else down, so the next page ends up showing items the previous page already showed. That's exactly what cursor-based pagination fixes.

2. How would you fix it in .NET/Azure?

Cursor-based pagination remembers a position instead of a page number: have the API remember the last item the user actually saw, and query Azure SQL for the next batch of items that come after that one in the list, not for a specific page number. After sending that batch, remember its last item too, so the next request can continue from there.

Use: EF Core

Remember the last item the user actually saw, not a page number, so new rows added to Azure SQL never shift anything already shown.

3. How can you visualize it in everyday terms?

A bookmark shows exactly where you stopped reading. Open the book again, and it takes you right back to that page, no matter how many pages get added earlier in the book.

Counting a fixed number of pages from the front instead, say page 100, would land you somewhere else entirely once new pages get added before that point.

It marks the exact spot the user stopped, not a page count that new items can throw off.