Offset vs Cursor Pagination in EF Core: A Simple, Complete Guide

Lining up at the railway ticket counter

Imagine a long line of people at a railway ticket counter. The clerk can only help a few people at a time. There are two ways the clerk can keep track of who is next.

Way one: the clerk says, "Let me serve people number 1 to 10, then 11 to 20, then 21 to 30." To find person number 21, the clerk has to count past the first 20 people every single time. If the line has 5,000 people and the clerk wants person number 4,990, that is a lot of counting.

Way two: the clerk gives each person a small token. When you come back, you show your token and say, "I was right after the person with token number 4,990." Now the clerk does not count from the start at all. The clerk jumps straight to the next person.

These two ways are exactly how pagination works in EF Core. Offset pagination is the counting way. Cursor pagination is the token way. Both are useful, and by the end of this post you will know which one to pick and why.

What pagination means

Pagination simply means breaking a big list into small pages. Instead of pulling 50,000 products from the database at once, you pull 20 at a time. This keeps your app fast and your memory low.

Every pagination method answers two questions:

1. Where do I start?
2. How many rows do I take?

The difference between offset and cursor pagination is only in how they answer the first question.

Offset pagination with Skip and Take

Offset pagination is the one most people learn first. You decide a page number and a page size. Then you tell EF Core how many rows to skip and how many to take.

Here is a simple example. We have a Product table, and we want page 3 with 20 items per page.

public async Task<List<Product>> GetProductsPageAsync(
    AppDbContext db, int pageNumber, int pageSize)
{
    int skip = (pageNumber - 1) * pageSize;
 
    return await db.Products
        .OrderBy(p => p.Id)        // ordering is required and must be stable
        .Skip(skip)                // jump past earlier pages
        .Take(pageSize)            // take just this page
        .ToListAsync();
}

For page 3 with a page size of 20, skip becomes (3 - 1) * 20 = 40. EF Core translates this into SQL that looks like this:

SELECT * FROM Products
ORDER BY Id
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;

The OFFSET 40 part tells the database to walk past the first 40 rows. That is the catch. The database does not magically teleport to row 41. It reads rows 1 to 40, throws them away, and only then starts returning the rows you want.

Why offset gets slow

On page 1, skipping 0 rows is free. On page 2, you skip 20. That is still cheap. But what about page 5,000 with a page size of 20? Now the database must read and discard 99,980 rows before it hands you 20. The deeper you go, the slower it gets. This is sometimes called the "deep page" problem.

There is a second problem: shifting data. Suppose you are on page 2 and someone deletes a product on page 1. The whole list shifts up by one. When you click "next" to page 3, one product silently slips past you, unseen. Or if someone adds a row, you might see the same product twice.

A nice bonus of offset

Offset pagination has one real advantage. It can jump to any page directly. Page 1, page 50, page 873 are all the same single query. It also lets you show a total page count, because you can run a separate COUNT(*) query. This is why admin dashboards with numbered page buttons love offset.

Here is how you build a full page result with a total count:

public record PagedResult<T>(List<T> Items, int TotalCount, int Page, int PageSize);
 
public async Task<PagedResult<Product>> GetPageAsync(
    AppDbContext db, int page, int pageSize)
{
    int total = await db.Products.CountAsync();   // one extra round trip
 
    var items = await db.Products
        .OrderBy(p => p.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();
 
    return new PagedResult<Product>(items, total, page, pageSize);
}

Cursor pagination with keyset

Cursor pagination, also called keyset or seek pagination, throws away the page-number idea. Instead, it remembers the last item you saw and asks for rows that come after it.

Think back to the token at the ticket counter. The token is the cursor. It is usually the value of the column you sort by, plus a unique tie-breaker like the primary key.

Say we order products by Id. The last product on the current page had Id = 60. To get the next page, we do not skip anything. We ask for rows where Id > 60:

public async Task<List<Product>> GetNextPageAsync(
    AppDbContext db, int? lastSeenId, int pageSize)
{
    var query = db.Products.OrderBy(p => p.Id).AsQueryable();
 
    if (lastSeenId is not null)
    {
        query = query.Where(p => p.Id > lastSeenId.Value);  // seek, do not skip
    }
 
    return await query.Take(pageSize).ToListAsync();
}

The SQL is beautifully simple:

SELECT TOP(20) * FROM Products
WHERE Id > 60
ORDER BY Id;

If Id has an index (and a primary key always does), the database jumps straight to the row after 60. It does not read or discard anything before it. This is why page 1 and page 10,000 cost almost the same.

The tie-breaker rule

There is one rule you must never break with cursor pagination: your ordering must be fully unique.

Imagine you order products by CreatedAt (a date), and many products share the exact same date. If you only filter by WHERE CreatedAt > lastDate, two products with the same timestamp might get skipped or repeated, because the database does not promise a stable order among equal values.

The fix is to add a unique tie-breaker, usually the primary key. You order by both columns, and your seek compares both:

// Order by CreatedAt, then Id as a unique tie-breaker
var query = db.Products
    .OrderBy(p => p.CreatedAt)
    .ThenBy(p => p.Id);
 
if (lastCreatedAt is not null && lastId is not null)
{
    query = query.Where(p =>
        p.CreatedAt > lastCreatedAt
        || (p.CreatedAt == lastCreatedAt && p.Id > lastId));
}
 
var items = await query.Take(pageSize).ToListAsync();

This (CreatedAt > x) OR (CreatedAt == x AND Id > y) pattern is the heart of correct keyset pagination on more than one column.

Indexing makes or breaks cursor pagination

Cursor pagination is only fast if the columns in your ORDER BY are covered by an index that matches the order exactly. If you sort by CreatedAt then Id, create a composite index on (CreatedAt, Id). Without the right index, the database falls back to scanning, and you lose the whole benefit.

modelBuilder.Entity<Product>()
    .HasIndex(p => new { p.CreatedAt, p.Id });

Offset vs cursor: side by side

Here is a direct comparison so you can see the trade-offs at a glance.

And here is a rough feel for how query cost grows as pages get deeper. The numbers are illustrative, not a benchmark, but the shape is real.

The pattern is clear. Offset work grows with the page number. Cursor work stays flat.

How to choose

You do not have to pick a side forever. Many real apps use both, depending on the screen. Here is a simple way to decide.

Pick offset when:

• Users click numbered page buttons (1, 2, 3, … 50).
• You must show a total count or total pages.
• The list is small, or people rarely go past the first few pages.
• An admin table or a back-office report is the use case.

Pick cursor when:

• The list is huge (millions of rows).
• People scroll endlessly, like a social feed or activity log.
• Data changes a lot while users browse.
• You build a public API and want stable, fast paging.

A common mistake to avoid

A very common bug is forgetting OrderBy. Without an explicit, stable order, the database is free to return rows in any order it likes. Your "page 2" might overlap with "page 1", or skip rows entirely. Always order by something unique before you call Skip/Take or build a cursor. The primary key is the safest tie-breaker.

Another mistake is loading huge related data on every page. If you call .Include() on big collections while paging, you can pull far more data than you expect. Page first, then load only what you need.

A note on tooling

Plenty of helper libraries exist for keyset pagination in EF Core, and the official Microsoft Learn docs cover both styles in detail. Many popular .NET packages are free and open source. Always check the license before adding a dependency to a commercial project, because some well-known .NET libraries have recently moved to commercial licensing. For pagination, though, you rarely need a library at all. The plain Skip/Take and WHERE patterns shown here are usually enough.

References and further reading

• Pagination — EF Core (Microsoft Learn)
• Efficient Querying — EF Core (Microsoft Learn)
• How To Implement Offset and Cursor-Based Pagination in EF Core (antondevtips)
• MR.EntityFrameworkCore.KeysetPagination (GitHub)

Quick recap

• Pagination breaks a big list into small pages so your app stays fast.
• Offset pagination uses Skip and Take. It is simple and can jump to any page, but it slows down on deep pages because the database reads and discards skipped rows.
• Cursor (keyset) pagination uses a WHERE filter on the last item seen. It stays fast on any page and handles changing data well, but it only supports next/previous, not page jumps.
• Always use a stable, unique order (add the primary key as a tie-breaker) so rows are never skipped or repeated.
• Cursor pagination needs an index that matches your ORDER BY to stay fast.
• Use offset for numbered pages and small lists. Use cursor for huge lists, feeds, and public APIs.