EF Core Pagination: Offset and Keyset (Cursor-Based) Explained

When building applications with Entity Framework Core (EF Core), one of the most common challenges developers face is handling large datasets efficiently. Returning thousands (or even millions) of rows in a single query isn’t practical – it slows down applications, increases memory usage, and provides a poor user experience.

That’s where pagination comes in. Pagination allows results to be split into smaller chunks, so users or API consumers can browse through data page by page.

In EF Core, the two most common pagination methods are:

• Offset Pagination
• Keyset Pagination (Cursor-Based or Seek-Based)

We’ll explore both in detail, explain when to use each, and also highlight the importance of indexing for performance.

Imagine a customer database with 2 million records. If a web application tried to load all customers at once, the response would be painfully slow and might even crash the server.

Instead, pagination allows only a subset of records to be fetched at a time – for example, 20 customers per request. This improves performance, reduces database load, and creates a smoother user experience.

But not all pagination strategies are created equal. Let’s break them down.

Offset pagination is the most common approach because it’s easy to implement and works well with small datasets. It uses two parameters:

• Skip (OFFSET) – tells the query how many records to bypass.
• Take (LIMIT) – tells the query how many records to return after skipping.

Think of it like a numbered list of results. If you want page 3 with 20 results per page, you skip the first 40 rows and return the next 20.

app.MapGet("/api/customers", async (
    AppDbContext db,
    int pageNumber = 3,
    int pageSize = 20,
    CancellationToken ct = default) =>
{
    if (pageNumber <= 0 || pageSize <= 0)
    {
        return Results.BadRequest("PageNumber and PageSize must be greater than zero.");
    }

    var customers = await db.Customers
        .AsNoTracking()               
        .OrderBy(c => c.Id)           
        .Skip((pageNumber - 1) * pageSize) // Skip the previous pages (e.g., for page 3 with size 20 → skip 40)
        .Take(pageSize)               // Take only the number of rows needed for the current page
        .ToListAsync(ct);           

    return Results.Ok(customers);
});

app.MapGet("/api/customers", async (
    AppDbContext db,
    int pageNumber = 3,
    int pageSize = 20,
    CancellationToken ct = default) =>
{
    if (pageNumber <= 0 || pageSize <= 0)
    {
        return Results.BadRequest("PageNumber and PageSize must be greater than zero.");
    }

    var customers = await db.Customers
        .AsNoTracking()               
        .OrderBy(c => c.Id)           
        .Skip((pageNumber - 1) * pageSize) // Skip the previous pages (e.g., for page 3 with size 20 → skip 40)
        .Take(pageSize)               // Take only the number of rows needed for the current page
        .ToListAsync(ct);           

    return Results.Ok(customers);
});

This query skips the first 40 customers and fetches the next 20, giving us page 3.

• Very simple to implement: Just use .Skip() and .Take() in EF Core.
• User-friendly: Easy to support “Page 1, Page 2, Page 3” navigation in a UI.
• Flexible: Works with any dataset as long as sorting is applied.

• Performance issues with large datasets: The database still scans all skipped rows. For example, fetching page 1,000 with 20 rows per page means scanning almost 20,000 rows just to discard them.
• Inconsistent with frequently changing data: If records are inserted or deleted while paginating, users may see duplicates or miss rows.
• Not ideal for infinite scrolling: Because each page is tied to a fixed offset, it doesn’t handle real-time updates well.

Offset pagination is good for small datasets or admin dashboards where exact page numbers are needed. But for APIs or real-time applications with millions of rows, we need something more efficient.

Keyset pagination, often called cursor-based pagination or seek-based pagination, avoids the inefficiency of skipping rows. Instead of jumping to a page by offset, it uses the last-seen key (like an Id or timestamp) to fetch the next set of results.

For example: “Give me the next 20 customers after the customer with Id = 200.”

This works much faster because the database can jump directly to the key instead of scanning skipped rows.

app.MapGet("/customers", async (
    AppDbContext db,
    int? lastId,
    int pageSize = 20,
    CancellationToken ct = default) =>
{
    if (pageSize <= 0)
    {
        return Results.BadRequest("PageSize must be greater than zero.");
    }

    var query = db.Customers.AsNoTracking().AsQueryable();

    if (lastId.HasValue)
    {
        query = query.Where(c => c.Id > lastId.Value);
    }

    var customers = await query
        .OrderBy(c => c.Id)
        .Take(pageSize)  // Take only the required page size
        .ToListAsync(ct);

    return Results.Ok(customers);
});

app.MapGet("/customers", async (
    AppDbContext db,
    int? lastId,
    int pageSize = 20,
    CancellationToken ct = default) =>
{
    if (pageSize <= 0)
    {
        return Results.BadRequest("PageSize must be greater than zero.");
    }

    var query = db.Customers.AsNoTracking().AsQueryable();

    if (lastId.HasValue)
    {
        query = query.Where(c => c.Id > lastId.Value);
    }

    var customers = await query
        .OrderBy(c => c.Id)
        .Take(pageSize)  // Take only the required page size
        .ToListAsync(ct);

    return Results.Ok(customers);
});

Here, the query returns the next 20 customers after the last-seen Id (200). No rows are scanned unnecessarily.

• High performance: The database can use indexes to fetch rows quickly.
• Stable with changing data: No skipped or duplicated records if new rows are added.
• Ideal for infinite scrolling: APIs and mobile apps often use cursors for seamless data loading.

• No direct page access: You can’t jump straight to page 25; you must follow the chain of cursors.
• Requires unique, sequential keys: Works best with indexed fields like Id or CreatedDate.
• Slightly more complex to implement than offset.

Keyset pagination is the best choice when dealing with large datasets or real-time applications like social media feeds, activity logs, or APIs.

Pagination queries are only as fast as the indexes behind them. To make keyset pagination efficient, always ensure that the columns used in ordering are indexed.

• For a single ordering column (e.g., Id), a normal index is enough.
• For multiple ordering columns (e.g., CreatedDate + Id), use a composite index.

A composite index allows the database to quickly navigate ordered results without scanning unnecessary rows. Without proper indexes, even keyset pagination can become slow.

There isn’t a single “best” method – the choice depends on use case:

• Offset Pagination works well for small datasets or when users need to jump directly to page numbers (Page 1, Page 2, etc.).
• Keyset Pagination is the right choice for large datasets, APIs, and infinite scrolling, where performance and consistency matter more than direct page access.

Pagination is essential when working with large datasets in EF Core.

• Offset Pagination is easy to implement and supports page numbers, but performance suffers on large datasets.
• Keyset Pagination is far more efficient, especially for APIs and continuous scrolling, and avoids inconsistencies caused by data changes.
• Indexes are crucial – without them, even the best pagination strategy can slow down.

• Use Offset Pagination only for smaller datasets or simple UIs.
• Use Keyset Pagination for scalable, high-performance applications.
• Always back pagination queries with the right indexes for efficiency.