.NET API Pagination Explained: Offset vs Keyset vs Cursor Strategies

Pagination is one of those features that seems simple until you have to implement it at scale. You start with Skip(20).Take(10) and everything works fine… until your table has millions of rows and your API starts timing out.

In this article, we’ll explore three pagination strategies for .NET Web APIs: offset-based, keyset-based, and cursor-based. You’ll learn when to use each, how to implement them with Entity Framework Core, and how to build a clean, reusable pagination response DTO using C# records.

Why Pagination Matters

Without pagination, you’re loading entire datasets into memory. That means:

Slow API responses
High memory usage
Poor database performance
Terrible user experience

Pagination solves this by loading data in chunks. But how you paginate matters enormously.

Strategy 1: Offset-Based Pagination

This is the most common approach. The client specifies a page number and page size:

GET /api/products?page=3&pageSize=20

Under the hood, this translates to SQL’s SKIP and TAKE (or OFFSET and LIMIT):

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

Implementation with EF Core

public class ProductsController : ControllerBase
{
    private readonly AppDbContext _context;

    public ProductsController(AppDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<PaginatedResponse<ProductDto>>> GetProducts(
        [FromQuery] int page = 1,
        [FromQuery] int pageSize = 20)
    {
        if (page < 1) page = 1;
        if (pageSize < 1) pageSize = 20;
        if (pageSize > 100) pageSize = 100; // Prevent abuse

        var query = _context.Products
            .OrderBy(p => p.Id)
            .AsNoTracking();

        var totalCount = await query.CountAsync();
        var totalPages = (int)Math.Ceiling(totalCount / (double)pageSize);

        var items = await query
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(p => new ProductDto
            {
                Id = p.Id,
                Name = p.Name,
                Price = p.Price
            })
            .ToListAsync();

        return Ok(new PaginatedResponse<ProductDto>
        {
            Items = items,
            Page = page,
            PageSize = pageSize,
            TotalCount = totalCount,
            TotalPages = totalPages
        });
    }
}

The PaginatedResponse Record DTO

Using C# records makes this clean and immutable:

public record PaginatedResponse<T>
{
    public IReadOnlyList<T> Items { get; init; } = Array.Empty<T>();
    public int Page { get; init; }
    public int PageSize { get; init; }
    public int TotalCount { get; init; }
    public int TotalPages { get; init; }
    public bool HasPrevious => Page > 1;
    public bool HasNext => Page < TotalPages;
}

The record keyword gives you:

Immutability by default
Value-based equality
Clean syntax with init properties
Pattern matching support

Pros and Cons of Offset Pagination

Pros:

Simple to implement and understand
Easy to jump to any page
Predictable page numbers

Cons:

Performance degrades at scale: SKIP(1000000) scans and discards 1 million rows
Inconsistent results: If items are added/deleted between requests, you might see duplicates or skip items
Requires COUNT query: The TotalCount query can be expensive on large tables

When to Use Offset Pagination

Use offset pagination when:

Your dataset is small to medium (< 100k rows)
Users need to jump to specific pages
Data is relatively static
You need page numbers in the UI

Strategy 2: Keyset Pagination (aka Seek Method)

Instead of page numbers, keyset pagination uses the last seen value as a starting point:

GET /api/products?afterId=12345&pageSize=20

The query becomes:

SELECT * FROM Products
WHERE Id > 12345
ORDER BY Id
FETCH NEXT 20 ROWS ONLY

No OFFSET means no wasted scanning.

Implementation with EF Core

[HttpGet]
public async Task<ActionResult<KeysetPaginatedResponse<ProductDto>>> GetProductsKeyset(
    [FromQuery] int? afterId = null,
    [FromQuery] int pageSize = 20)
{
    if (pageSize < 1) pageSize = 20;
    if (pageSize > 100) pageSize = 100;

    var query = _context.Products
        .AsNoTracking()
        .OrderBy(p => p.Id);

    if (afterId.HasValue)
    {
        query = query.Where(p => p.Id > afterId.Value);
    }

    // Fetch one extra to determine if there's a next page
    var items = await query
        .Take(pageSize + 1)
        .Select(p => new ProductDto
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price
        })
        .ToListAsync();

    var hasNext = items.Count > pageSize;
    if (hasNext)
    {
        items = items.Take(pageSize).ToList();
    }

    var lastId = items.Any() ? items[^1].Id : (int?)null;

    return Ok(new KeysetPaginatedResponse<ProductDto>
    {
        Items = items,
        PageSize = pageSize,
        NextCursor = hasNext ? lastId : null
    });
}

Keyset Response DTO

public record KeysetPaginatedResponse<T>
{
    public IReadOnlyList<T> Items { get; init; } = Array.Empty<T>();
    public int PageSize { get; init; }
    public int? NextCursor { get; init; }
    public bool HasNext => NextCursor.HasValue;
}

Ordering by Multiple Columns

For non-unique columns, combine them with a unique tiebreaker:

public record ProductCursor
{
    public decimal Price { get; init; }
    public int Id { get; init; }
}

[HttpGet]
public async Task<ActionResult<KeysetPaginatedResponse<ProductDto>>> GetProductsByPrice(
    [FromQuery] decimal? afterPrice = null,
    [FromQuery] int? afterId = null,
    [FromQuery] int pageSize = 20)
{
    var query = _context.Products
        .AsNoTracking()
        .OrderBy(p => p.Price)
        .ThenBy(p => p.Id);

    if (afterPrice.HasValue && afterId.HasValue)
    {
        query = query.Where(p =>
            p.Price > afterPrice.Value ||
            (p.Price == afterPrice.Value && p.Id > afterId.Value));
    }

    var items = await query
        .Take(pageSize + 1)
        .Select(p => new ProductDto
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price
        })
        .ToListAsync();

    var hasNext = items.Count > pageSize;
    if (hasNext)
    {
        items = items.Take(pageSize).ToList();
    }

    ProductCursor? nextCursor = null;
    if (hasNext && items.Any())
    {
        var last = items[^1];
        nextCursor = new ProductCursor { Price = last.Price, Id = last.Id };
    }

    return Ok(new KeysetPaginatedResponse<ProductDto, ProductCursor>
    {
        Items = items,
        PageSize = pageSize,
        NextCursor = nextCursor
    });
}

public record KeysetPaginatedResponse<T, TCursor>
{
    public IReadOnlyList<T> Items { get; init; } = Array.Empty<T>();
    public int PageSize { get; init; }
    public TCursor? NextCursor { get; init; }
    public bool HasNext => NextCursor is not null;
}

Pros and Cons of Keyset Pagination

Pros:

Much faster: No offset scanning, uses index seeks
Consistent results: New items don’t cause duplicates/skips
Scales to millions of rows

Cons:

Can’t jump to arbitrary pages
Requires indexed column(s)
Harder to implement with complex sorting

When to Use Keyset Pagination

Use keyset pagination when:

You have large datasets (100k+ rows)
Performance is critical
Data changes frequently
“Load more” or infinite scroll UI pattern
You can sort by an indexed column

Strategy 3: Cursor-Based Pagination

Cursor pagination is keyset pagination with opaque cursors. Instead of exposing raw IDs, you encode them:

GET /api/products?after=eyJpZCI6MTIzNDV9&pageSize=20

The cursor is base64-encoded JSON (or another encoding scheme).

Implementation with Cursors

public class CursorHelper
{
    public static string Encode<T>(T value)
    {
        var json = JsonSerializer.Serialize(value);
        var bytes = Encoding.UTF8.GetBytes(json);
        return Convert.ToBase64String(bytes);
    }

    public static T? Decode<T>(string? cursor)
    {
        if (string.IsNullOrEmpty(cursor)) return default;

        try
        {
            var bytes = Convert.FromBase64String(cursor);
            var json = Encoding.UTF8.GetString(bytes);
            return JsonSerializer.Deserialize<T>(json);
        }
        catch
        {
            return default;
        }
    }
}

[HttpGet]
public async Task<ActionResult<CursorPaginatedResponse<ProductDto>>> GetProductsCursor(
    [FromQuery] string? after = null,
    [FromQuery] int pageSize = 20)
{
    if (pageSize < 1) pageSize = 20;
    if (pageSize > 100) pageSize = 100;

    var cursor = CursorHelper.Decode<ProductCursor>(after);

    var query = _context.Products
        .AsNoTracking()
        .OrderBy(p => p.Id);

    if (cursor is not null)
    {
        query = query.Where(p => p.Id > cursor.Id);
    }

    var items = await query
        .Take(pageSize + 1)
        .Select(p => new ProductDto
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price
        })
        .ToListAsync();

    var hasNext = items.Count > pageSize;
    if (hasNext)
    {
        items = items.Take(pageSize).ToList();
    }

    string? nextCursor = null;
    if (hasNext && items.Any())
    {
        var lastCursor = new ProductCursor { Id = items[^1].Id };
        nextCursor = CursorHelper.Encode(lastCursor);
    }

    return Ok(new CursorPaginatedResponse<ProductDto>
    {
        Items = items,
        PageSize = pageSize,
        NextCursor = nextCursor,
        HasNext = hasNext
    });
}

public record CursorPaginatedResponse<T>
{
    public IReadOnlyList<T> Items { get; init; } = Array.Empty<T>();
    public int PageSize { get; init; }
    public string? NextCursor { get; init; }
    public bool HasNext { get; init; }
}

GraphQL-Style Edges and Nodes

For a more GraphQL-like API:

public record Edge<T>
{
    public string Cursor { get; init; } = string.Empty;
    public T Node { get; init; } = default!;
}

public record PageInfo
{
    public bool HasNextPage { get; init; }
    public bool HasPreviousPage { get; init; }
    public string? StartCursor { get; init; }
    public string? EndCursor { get; init; }
}

public record Connection<T>
{
    public IReadOnlyList<Edge<T>> Edges { get; init; } = Array.Empty<Edge<T>>();
    public PageInfo PageInfo { get; init; } = new();
    public int TotalCount { get; init; }
}

[HttpGet("graphql-style")]
public async Task<ActionResult<Connection<ProductDto>>> GetProductsConnection(
    [FromQuery] string? after = null,
    [FromQuery] int first = 20)
{
    var cursor = CursorHelper.Decode<ProductCursor>(after);

    var query = _context.Products.AsNoTracking().OrderBy(p => p.Id);

    if (cursor is not null)
    {
        query = query.Where(p => p.Id > cursor.Id);
    }

    var items = await query
        .Take(first + 1)
        .Select(p => new ProductDto { Id = p.Id, Name = p.Name, Price = p.Price })
        .ToListAsync();

    var hasNext = items.Count > first;
    if (hasNext) items = items.Take(first).ToList();

    var edges = items.Select(item => new Edge<ProductDto>
    {
        Cursor = CursorHelper.Encode(new ProductCursor { Id = item.Id }),
        Node = item
    }).ToList();

    var pageInfo = new PageInfo
    {
        HasNextPage = hasNext,
        HasPreviousPage = cursor is not null,
        StartCursor = edges.FirstOrDefault()?.Cursor,
        EndCursor = edges.LastOrDefault()?.Cursor
    };

    var totalCount = await _context.Products.CountAsync();

    return Ok(new Connection<ProductDto>
    {
        Edges = edges,
        PageInfo = pageInfo,
        TotalCount = totalCount
    });
}

Pros and Cons of Cursor Pagination

Pros:

All benefits of keyset pagination
Opaque cursors prevent client manipulation
Works well with GraphQL conventions
Supports bidirectional pagination

Cons:

More complex to implement
Encoded cursors are longer URLs
Need to handle cursor validation

When to Use Cursor Pagination

Use cursor pagination when:

Building GraphQL APIs
You want opaque, tamper-resistant pagination
Need bidirectional navigation (next/previous)
Working with real-time/streaming data

Building a Generic Pagination Service

For reusability, create an extension method:

public static class PaginationExtensions
{
    public static async Task<PaginatedResponse<T>> ToPaginatedListAsync<T>(
        this IQueryable<T> query,
        int page,
        int pageSize,
        CancellationToken cancellationToken = default)
    {
        if (page < 1) page = 1;
        if (pageSize < 1) pageSize = 20;

        var totalCount = await query.CountAsync(cancellationToken);
        var totalPages = (int)Math.Ceiling(totalCount / (double)pageSize);

        var items = await query
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync(cancellationToken);

        return new PaginatedResponse<T>
        {
            Items = items,
            Page = page,
            PageSize = pageSize,
            TotalCount = totalCount,
            TotalPages = totalPages
        };
    }
}

// Usage
var products = await _context.Products
    .AsNoTracking()
    .OrderBy(p => p.Id)
    .Select(p => new ProductDto { Id = p.Id, Name = p.Name, Price = p.Price })
    .ToPaginatedListAsync(page, pageSize);

Performance Comparison

Let’s say you have 1 million products:

Strategy	Page 1	Page 100	Page 10,000	Notes
Offset	~10ms	~50ms	~500ms	Degrades linearly
Keyset	~10ms	~10ms	~10ms	Constant time
Cursor	~10ms	~10ms	~10ms	Same as keyset

The difference is dramatic at scale.

Which Strategy Should You Use?

Use Offset when:

Small datasets (< 100k rows)
Users need page numbers
Simple admin panels

Use Keyset when:

Large datasets
Performance matters
Mobile apps with infinite scroll

Use Cursor when:

Building GraphQL APIs
Need opaque pagination tokens
Real-time feeds

Most modern APIs are moving toward keyset/cursor pagination for performance reasons.

Conclusion

Pagination isn’t one-size-fits-all. Offset pagination is simple but doesn’t scale. Keyset and cursor pagination scale beautifully but require more thought in implementation.

Key takeaways:

Start with offset for simplicity, migrate to keyset at scale
Always validate and limit pageSize to prevent abuse
Use C# records for clean, immutable DTOs
Index your pagination columns
Consider your UI patterns when choosing a strategy

The complete example code with all three strategies is available on GitHub. Choose the right tool for your scale, and your users will thank you.