Pagination in EF Core: a practical guide for developers

Pagination in EF Core: When Offset Becomes Your Bottleneck

Most of us reach for Skip().Take() the moment we need to paginate results in ASP.NET Core. It works, it's simple, and for small datasets or admin dashboards it's perfectly fine. But once you're building a feed that grows to millions of records, or users are jumping to page 500, that offset approach will quietly destroy your query performance—and you'll spend hours wondering why your database is suddenly on fire.

In this article, I'm going to show you why offset-based pagination breaks down at scale, and more importantly, I'll walk you through keyset pagination, a cursor-based approach that keeps your queries snappy regardless of how deep into your dataset you go. By the end, you'll know exactly when to use each strategy and have production-ready code to reach for.

Skip/Take Is Fine for Admin Tables

Let's be clear: offset pagination isn't wrong. It's the right tool for the right job. When you're building an internal admin interface where users browse a table of, say, 10,000 support tickets and rarely go beyond page 3, Skip().Take() is your friend.

The code is trivial:

public async Task<List<OrderDto>> GetOrders(int pageNumber, int pageSize, CancellationToken ct)
{
    var orders = await db.Orders
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .OrderByDescending(o => o.CreatedAt)
        .Select(o => new OrderDto 
        { 
            Id = o.Id, 
            CustomerName = o.CustomerName, 
            Total = o.Total 
        })
        .ToListAsync(ct);

    return orders;
}

You send back page numbers, display "Page 5 of 847" in the UI, maybe some numbered buttons at the bottom. Users click around, and it all feels responsive.

The reason this works for admin interfaces is twofold: (1) the user is unlikely to request page 500, and (2) it's internal—you control the load. You're not serving millions of page requests a second. The database overhead is tolerable.

Use offset pagination when:

• Your dataset is small (under 50,000 rows commonly accessed).
• Users typically browse the first few pages only.
• It's an admin or internal tool where load is predictable.
• Your business logic genuinely needs absolute page numbers.

But the moment you build a public-facing feed, a product search with millions of results, or any user-driven interface where people can scroll indefinitely, offset becomes a liability.

Deep Offsets Scan and Discard Rows

Here's what's happening under the hood when someone requests page 500 of your dataset with 50 items per page. Your database has to:

1. Apply all your filters and joins.
2. Sort the entire result set.
3. Skip the first 24,950 rows (that's (500 - 1) * 50).
4. Take 50 rows.
5. Discard the 24,950 it skipped.

Even if your table has an index on the sort column, the database still has to scan or iterate through those 24,950 rows before it can serve the 50 you actually want. With a million rows and someone asking for page 20,000, you're making the database work through 1 million rows just to discard 999,950 of them.

Let me show you a real scenario. Imagine an e-commerce platform where users search for "blue shoes" and browse results. Your query might look like:

public async Task<PagedResult<ProductDto>> SearchProducts(
    string query, 
    int pageNumber, 
    int pageSize, 
    CancellationToken ct)
{
    var skip = (pageNumber - 1) * pageSize;
    
    var products = await db.Products
        .Where(p => p.Name.Contains(query) || p.Description.Contains(query))
        .Where(p => p.IsActive)
        .OrderByDescending(p => p.SearchRank)
        .ThenByDescending(p => p.CreatedAt)
        .Skip(skip)
        .Take(pageSize)
        .ToListAsync(ct);

    return products;
}

On page 1, this is fast. On page 100, your database is doing serious work. At scale, you'll see query times climb from 50ms to 500ms to several seconds as the offset grows. CPU spikes. Users complain. You get paged at 2 AM.

The solution isn't to throw more hardware at it. The solution is to stop skipping rows entirely.

Keyset Pagination: WHERE Id > lastId

Keyset pagination—also called cursor-based pagination—solves this by remembering the last row you saw and asking for rows that come after it. Instead of "give me rows 24,950 to 25,000," you say "give me 50 rows after the one with ID 99,542."

The magic is simple: you use a unique, indexed column (usually the primary key or a composite of sort columns) as your anchor. Your WHERE clause becomes:

WHERE Id > lastId

This single condition, with a proper index, lets the database jump directly to the right place and fetch exactly what you need. No scanning through hundreds of thousands of discarded rows.

Here's what it looks like in practice:

public async Task<CursoredResult<OrderDto>> GetOrdersPaginated(
    long? cursorId, 
    int pageSize, 
    CancellationToken ct)
{
    var query = db.Orders.AsQueryable();

    // If we have a cursor, start after that record
    if (cursorId.HasValue)
    {
        query = query.Where(o => o.Id > cursorId.Value);
    }

    // Fetch one extra to know if there's a next page
    var orders = await query
        .OrderBy(o => o.Id)
        .Take(pageSize + 1)
        .Select(o => new OrderDto 
        { 
            Id = o.Id, 
            CustomerName = o.CustomerName, 
            Total = o.Total,
            CreatedAt = o.CreatedAt
        })
        .ToListAsync(ct);

    var hasNextPage = orders.Count > pageSize;
    var results = orders.Take(pageSize).ToList();
    var nextCursor = hasNextPage ? results.Last().Id : (long?)null;

    return new CursoredResult<OrderDto>
    {
        Items = results,
        NextCursor = nextCursor,
        HasNextPage = hasNextPage
    };
}

public class CursoredResult<T>
{
    public List<T> Items { get; set; }
    public long? NextCursor { get; set; }
    public bool HasNextPage { get; set; }
}

Notice what we're doing: we fetch pageSize + 1 rows. If we get more than pageSize, we know there's a next page. The last item's ID becomes our cursor for the next request. This is significantly faster because the database index can seek directly to ID 99,542 and read forward 50 rows. No wasted work.

The tradeoff? You can't jump to page 500 directly. You have to traverse from the beginning, one page at a time. But here's the thing: in real user behavior, almost nobody does that. Users scroll through a feed, not jump to page 500. This constraint isn't a bug; it's a feature that aligns with actual usage patterns.

Return a Cursor, Not Page Numbers, for Feeds

When you shift to keyset pagination, your API contract changes. Instead of returning page numbers, you return a cursor—an opaque string (often a base64-encoded ID) that the client passes back on the next request.

Here's the revised endpoint:

[HttpGet("orders")]
public async Task<IActionResult> GetOrders(
    [FromQuery] string cursor, 
    [FromQuery] int pageSize = 50, 
    CancellationToken ct = default)
{
    // Decode cursor if provided
    long? decodedCursor = null;
    if (!string.IsNullOrEmpty(cursor))
    {
        var decoded = Encoding.UTF8.GetString(Convert.FromBase64String(cursor));
        if (long.TryParse(decoded, out var id))
        {
            decodedCursor = id;
        }
    }

    var result = await GetOrdersPaginated(decodedCursor, pageSize, ct);

    var response = new
    {
        Items = result.Items,
        NextCursor = result.NextCursor.HasValue 
            ? Convert.ToBase64String(Encoding.UTF8.GetBytes(result.NextCursor.Value.ToString()))
            : null,
        HasNextPage = result.HasNextPage
    };

    return Ok(response);
}

Your client now does:

// First request
GET /api/orders?pageSize=50

// Response
{
  "items": [...50 orders...],
  "nextCursor": "OTk1NDI=",  // base64-encoded ID
  "hasNextPage": true
}

// Second request (with cursor)
GET /api/orders?cursor=OTk1NDI=&pageSize=50

// And so on...

The cursor is opaque. Clients don't know it's an ID, and they don't need to. They just pass it back. This also gives you flexibility to change the underlying cursor implementation (maybe it becomes a timestamp later, or a composite key) without breaking clients.

For feeds—Twitter-like endless scrolls, social media timelines, product search results—this is the standard approach. It scales beautifully because every query is equally fast, whether it's the first page or the 10,000th. And it handles concurrent requests gracefully; multiple users scrolling different parts of the dataset don't interfere with each other.

When to Use Keyset vs. Offset

I want to give you a decision tree because this is where teams often get it wrong:

• Offset pagination: Admin dashboards, internal tools, small result sets, users need absolute page numbers.
• Keyset pagination: Public feeds, search results, endless scroll, any user-facing paginated list with potential for large offsets.

A practical rule: if your dataset is small enough that users typically stay on pages 1–5, offset is fine and simpler. If users can theoretically scroll forever, or your result set exceeds 100,000 rows, keyset is the safer bet.

Common Mistakes

Mistake 1: Sorting by multiple columns without considering cursor logic.

If you sort by CreatedAt and then Id, your keyset condition should check both:

// Wrong: only checks ID
WHERE Id > lastId

// Right: checks both sort columns
WHERE (CreatedAt < lastCreatedAt) 
   OR (CreatedAt == lastCreatedAt AND Id > lastId)

This ensures stable pagination if two records share the same creation timestamp.

Mistake 2: Forgetting to fetch pageSize + 1 rows.

You need that extra row to determine if there's a next page. Without it, you can't tell if you're at the end of the dataset or if there's more.

Mistake 3: Not indexing the cursor column.

If your keyset pagination uses ID as the cursor but ID isn't indexed (or is part of a composite index that doesn't optimize your WHERE clause), you've built a fast algorithm on top of a slow query. Ensure your cursor column is indexed or part of an index that covers your WHERE and ORDER BY.

Mistake 4: Exposing raw IDs in cursors.

Base64-encoding or otherwise obscuring the cursor is more than obfuscation. It's a contract boundary. If you later want to change how cursors work internally, clients don't need to know or change their code.

Key Takeaways

• Skip/Take works fine for admin tables and small datasets, but degrades badly at deep offsets. The database wastes CPU scanning and discarding rows you don't need.
• Keyset pagination eliminates that waste by using a WHERE clause to start after the last seen row. Every query is equally fast, regardless of position in the dataset.
• Return an opaque cursor to clients instead of page numbers. This aligns with real user behavior (scrolling, not jumping) and gives you internal flexibility.
• Use keyset for feeds and large result sets; use offset for small admin interfaces. Match the tool to the problem.
• When implementing keyset, fetch pageSize + 1 rows, index your cursor column, and handle multi-column sorts carefully. These details matter for correctness and performance.