Cursor pagination

Cursor pagination allows clients to navigate large, ordered datasets using a “cursor” that marks a position, rather than a numeric page offset. Instead of requesting “page 3,” clients request items after or before a specific boundary, typically defined by an indexed sort key like a unique identifier or timestamp. This approach improves performance and consistency in dynamic lists where items are frequently inserted or deleted.

A cursor is generally an opaque token (e.g., Base64-encoded JSON) that encodes the sort keys of the last seen item. Clients pass this token to fetch the next slice of data, making infinite scrolling and timeline-style feeds more robust and scalable.

• Performance: Uses index range scans (e.g., WHERE created_at < :boundary) instead of expensive OFFSET skips.
• Consistency: Reduces duplicates or missing items when data changes between requests.
• Scalability: Ideal for high-traffic APIs and infinite scroll UIs.
• Directional control: Supports forward and backward navigation with “after” and “before” cursors.

Tip: When sorting by a non-unique key like CreatedAt, always include a unique tiebreaker (e.g., Id) in both the ORDER BY and the cursor to avoid gaps and duplicates.

• Stable ordering: Choose a unique, immutable, indexed sort key.
• Opaque cursor: Use Base64-encoded JSON or a signed token.
• Limit + 1 fetch: Retrieve one extra item to compute HasMore.
• Consistent filters: Apply the same filters on every page.
• Back/forward navigation: For DESC order, “after” means items strictly smaller than the boundary.

API shape:

• Endpoint: GET /api/posts?limit=20&after={cursor}
• Response: items, pageInfo.hasMore, pageInfo.nextCursor
• Ordering: CreatedAt DESC, Id DESC

Cursor payload:

• Fields: createdAt, id
• Encoding: Base64 URL-safe JSON

Model

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
}

DbContext

public class AppDbContext : DbContext
{
    public DbSet<Post> Posts => Set<Post>();

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasIndex(p => new { p.CreatedAt, p.Id });
    }
}

Cursor Helper

public static class CursorHelper
{
    public static string EncodeCursor(object obj)
    {
        var json = JsonSerializer.Serialize(obj);
        return Convert.ToBase64String(Encoding.UTF8.GetBytes(json));
    }

    public static T? DecodeCursor<T>(string? cursor)
    {
        if (string.IsNullOrWhiteSpace(cursor)) return default;
        try
        {
            var bytes = Convert.FromBase64String(cursor);
            var json = Encoding.UTF8.GetString(bytes);
            return JsonSerializer.Deserialize<T>(json);
        }
        catch
        {
            return default;
        }
    }
}

DTOs

public class PaginationResponse<T>
{
    public IEnumerable<T> Items { get; set; } = Enumerable.Empty<T>();
    public PageInfo PageInfo { get; set; } = new();
}

public class PageInfo
{
    public bool HasMore { get; set; }
    public string? NextCursor { get; set; }
}

public class PostCursor
{
    public DateTime CreatedAt { get; set; }
    public int Id { get; set; }
}

Controller

[ApiController]
[Route("api/[controller]")]
public class PostsController : ControllerBase
{
    private readonly AppDbContext _db;

    public PostsController(AppDbContext db) => _db = db;

    [HttpGet]
    public async Task<ActionResult<PaginationResponse<object>>> Get(
        [FromQuery] int limit = 20,
        [FromQuery] string? after = null)
    {
        limit = Math.Clamp(limit, 1, 100);

        var cursor = CursorHelper.DecodeCursor<PostCursor>(after);

        var query = _db.Posts
            .OrderByDescending(p => p.CreatedAt)
            .ThenByDescending(p => p.Id)
            .AsNoTracking();

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

        var items = await query
            .Take(limit + 1)
            .Select(p => new { p.Id, p.Title, p.CreatedAt })
            .ToListAsync();

        var hasMore = items.Count > limit;
        if (hasMore) items.RemoveAt(items.Count - 1);

        var nextCursor = items.Any()
            ? CursorHelper.EncodeCursor(new PostCursor
              {
                  CreatedAt = items.Last().CreatedAt,
                  Id = items.Last().Id
              })
            : null;

        return Ok(new PaginationResponse<object>
        {
            Items = items,
            PageInfo = new PageInfo { HasMore = hasMore, NextCursor = nextCursor }
        });
    }
}

Client Consumption Example

var http = new HttpClient { BaseAddress = new Uri("https://localhost:5001") };
string? cursor = null;

while (true)
{
    var url = "/api/posts?limit=10" + (cursor is not null ? $"&after={Uri.EscapeDataString(cursor)}" : "");
    var resp = await http.GetFromJsonAsync<ApiResponse>(url);

    foreach (var p in resp!.Items)
        Console.WriteLine($"{p.Id} | {p.CreatedAt:o} | {p.Title}");

    if (!resp.PageInfo.HasMore) break;
    cursor = resp.PageInfo.NextCursor;
}

• Duplicate sort keys: include a secondary unique key.
• Invalid cursor: fail gracefully.
• Filter changes: don’t mix filters between pages.
• Time zones: normalize timestamps to UTC.
• Backward pagination: invert the range for “before” semantics.

-- Descending order (newest first), forward pagination:
SELECT id, title, created_at
FROM posts
WHERE (created_at < :cursor_created_at)
   OR (created_at = :cursor_created_at AND id < :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT :limit_plus_one;

Cursor pagination provides a robust, scalable alternative to offset-based paging for large or dynamic datasets. By anchoring navigation to a stable ordered boundary and returning opaque cursor tokens, your .NET APIs can deliver consistent results, better performance, and smooth infinite scrolling experiences.