Stop Using Skip/Take for EF Core 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 database how many rows to skip, we tell it where to start. We use a WHERE clause based on the last value from the previous page.

-- Always fast, no matter how deep you go
SELECT TOP 20 * FROM Orders 
WHERE OrderId > @LastOrderId -- @LastOrderId was 50000
ORDER BY OrderId;

This simple change transforms an expensive index scan into a highly efficient index seek. The database can jump directly to the right spot without counting anything. The performance difference is night and day.

graph TD
    A[Orders Table<br/>2.3M rows] --> B{Pagination Method}
    
    B -->|Offset (`Skip`/`Take`)| C[Count & Skip<br/>50,000 rows]
    C --> D[Scan Index<br/>50,020 entries]
    D --> E[Return 20 rows<br/>⏱️ 4,200ms]
    
    B -->|Keyset (`Where`/`Take`)| F[Seek WHERE<br/>OrderId > 50000]
    F --> G[Index Jump<br/>Direct to position]
    G --> H[Return 20 rows<br/>⏱️ 12ms]
    
    style C fill:#ffcccc
    style D fill:#ffcccc
    style E fill:#ffcccc
    style F fill:#ccffcc
    style G fill:#ccffcc
    style H fill:#ccffcc
    

How to Do It in EF Core

Here’s the pattern I use in production. It’s clean, reusable, and handles the edge cases.

public class OrderService
{
    private readonly AppDbContext _context;

    public async Task<PagedResult<Order>> GetOrdersAsync(
        int? lastOrderId = null, 
        int pageSize = 20)
    {
        var query = _context.Orders.AsNoTracking().AsQueryable();
        
        // The core of keyset pagination: filter based on the last seen ID.
        if (lastOrderId.HasValue)
        {
            query = query.Where(o => o.OrderId > lastOrderId.Value);
        }

        // The trick to know if there's a next page.
        // We fetch one more item than we need.
        var orders = await query
            .OrderBy(o => o.OrderId)
            .Take(pageSize + 1) 
            .ToListAsync();

        var hasNextPage = orders.Count > pageSize;
        if (hasNextPage)
        {
            // Remove the extra item we fetched. It's only used for the check.
            orders.RemoveAt(pageSize);
        }

        return new PagedResult<Order>
        {
            Items = orders,
            HasNextPage = hasNextPage,
            // The cursor for the next page is the ID of the last item.
            NextCursor = orders.LastOrDefault()?.OrderId
        };
    }
}

public class PagedResult<T>
{
    public List<T> Items { get; set; } = new();
    public bool HasNextPage { get; set; }
    public int? NextCursor { get; set; }
}

The key piece of this is fetching pageSize + 1. It’s a dead simple way to check for a next page without running a separate COUNT(*) query. If we get back 21 items, we know there’s more data, and we just trim the extra one off before returning the result.

The Proof Is in the Numbers

I ran a quick test on our staging database (a decent Azure SQL S2 instance) with 2.3 million orders. The results speak for themselves.

Keyset pagination delivered consistent, fast performance. It didn’t matter if we were on the first page or the fifty-thousandth page.

What About Sorting by Non-Unique Columns?

This is a common “gotcha.” What if you’re sorting by CreatedDate, which isn’t unique? Multiple orders could have the exact same timestamp. If you only filter by WHERE CreatedDate > @LastCreatedDate, you risk skipping records.

The solution is to create a composite key by adding a unique column (like the primary key) as a tie-breaker.

public async Task<PagedResult<Order>> GetOrdersByDateAsync(
    DateTime? lastCreatedDate = null, 
    int? lastOrderId = null, // The tie-breaker
    int pageSize = 20)
{
    var query = _context.Orders.AsQueryable();
    
    if (lastCreatedDate.HasValue && lastOrderId.HasValue)
    {
        // This composite WHERE clause is the key.
        // It finds records AFTER the last date, OR records on the SAME date
        // but with a higher ID.
        query = query.Where(o => 
            o.CreatedDate > lastCreatedDate.Value || 
            (o.CreatedDate == lastCreatedDate.Value && o.OrderId > lastOrderId.Value));
    }

    var orders = await query
        .OrderBy(o => o.CreatedDate)
        .ThenBy(o => o.OrderId) // The tie-breaker MUST be in the ORDER BY
        .Take(pageSize + 1)
        .ToListAsync();

    // ... same pagination logic to check for next page and set cursor ...
}

Just remember: your ORDER BY clause must match your WHERE clause logic, including the tie-breaker.

Wiring It Up in an API Controller

Exposing this in a REST API is straightforward. The client just needs to receive the nextCursor from one request and send it back to get the next page.

A small security tip: I don’t recommend exposing raw database IDs in your API. A simple Base64 encoding is enough to prevent users from guessing sequential IDs and scraping your entire dataset.

[HttpGet]
public async Task<IActionResult> GetOrders(
    [FromQuery] string? cursor = null,
    [FromQuery] int pageSize = 20)
{
    // Don't let clients request a million records at once.
    if (pageSize > 100) pageSize = 100; 
    
    int? decodedCursor = null;
    if (!string.IsNullOrEmpty(cursor))
    {
        try
        {
            var bytes = Convert.FromBase64String(cursor);
            decodedCursor = BitConverter.ToInt32(bytes, 0);
        }
        catch (FormatException)
        {
            return BadRequest("Invalid cursor format.");
        }
    }
    
    var result = await _orderService.GetOrdersAsync(decodedCursor, pageSize);
    
    // Encode the next cursor for the client
    string? nextCursorEncoded = null;
    if (result.NextCursor.HasValue)
    {
        var bytes = BitConverter.GetBytes(result.NextCursor.Value);
        nextCursorEncoded = Convert.ToBase64String(bytes);
    }
    
    var response = new {
        result.Items,
        result.HasNextPage,
        NextCursor = nextCursorEncoded
    };
    
    return Ok(response);
}

The client gets a simple response and only needs to care about the nextCursor string.

{
  "items": [...],
  "hasNextPage": true,
  "nextCursor": "AQAAADIAAA=="
}

The Takeaway: When to Use Keyset (and When Not To)

So, should you refactor all your pagination code? Not necessarily. It’s about using the right tool for the job.

Here’s when I use keyset pagination:

• For any public-facing API that returns a list of items from a large table (orders, activity feeds, logs, messages).
• Infinite scroll UIs. Keyset is perfect for this.
• Any situation where performance consistency is critical.

And here’s when I stick with Skip()/Take() (offset pagination):

• Internal admin dashboards where datasets are small (less than a few thousand rows). The performance hit is negligible.
• When the user must be able to jump to a specific page number, like page 50 of 100. Keyset pagination is forward-only.
• When the columns you’re sorting on can change between requests. This can cause weird duplicates or missed records with keyset.

For any high-traffic system, keyset pagination isn’t just a “nice-to-have” optimization. It’s a requirement for stable performance at scale. The trade-off is losing random page access, but for 99% of user-facing scenarios, a “Load More” button is a better experience anyway.

References

• EF Core - Paging
• Query Processing Architecture Guide - SQL Server
• Pagination with OFFSET and FETCH NEXT is Slow - by Brent Ozar

FAQ