Without pagination:
  GET /orders → returns 2 million rows → OOM, timeout, or DoS

With pagination:
  GET /orders?page=1&pageSize=20 → 20 rows, fast
  GET /orders?cursor=abc123      → 20 rows, fast at any depth

// Query and result types
public record GetOrdersQuery(int Page = 1, int PageSize = 20) : IRequest<PagedResult<OrderDto>>;

public record PagedResult<T>(
    IReadOnlyList<T> Items,
    int Page,
    int PageSize,
    int TotalCount,
    int TotalPages,
    bool HasNextPage,
    bool HasPreviousPage);

// EF Core implementation
public class GetOrdersHandler(AppDbContext context)
    : IRequestHandler<GetOrdersQuery, PagedResult<OrderDto>>
{
    public async Task<PagedResult<OrderDto>> Handle(GetOrdersQuery q, CancellationToken ct)
    {
        var query = context.Orders
            .Where(o => o.Status != "Deleted")
            .OrderByDescending(o => o.CreatedAt);

        // Count before pagination — required for TotalPages
        var totalCount = await query.CountAsync(ct);

        var items = await query
            .Skip((q.Page - 1) * q.PageSize)
            .Take(q.PageSize)
            .Select(o => new OrderDto(o.Id, o.CustomerId, o.Total, o.Status))
            .AsNoTracking()
            .ToListAsync(ct);

        return new PagedResult<OrderDto>(
            Items:           items,
            Page:            q.Page,
            PageSize:        q.PageSize,
            TotalCount:      totalCount,
            TotalPages:      (int)Math.Ceiling(totalCount / (double)q.PageSize),
            HasNextPage:     q.Page * q.PageSize < totalCount,
            HasPreviousPage: q.Page > 1);
    }
}

// API endpoint
[HttpGet("orders")]
[ProducesResponseType<PagedResult<OrderDto>>(200)]
public async Task<IActionResult> GetOrders(
    [FromQuery] int page     = 1,
    [FromQuery] int pageSize = 20,
    CancellationToken ct = default)
{
    pageSize = Math.Clamp(pageSize, 1, 100);   // cap to prevent abuse
    var result = await mediator.Send(new GetOrdersQuery(page, pageSize), ct);
    return Ok(result);
}

// Response
{
  "items": [...],
  "page": 2,
  "pageSize": 20,
  "totalCount": 450,
  "totalPages": 23,
  "hasNextPage": true,
  "hasPreviousPage": true
}

Offset pagination problems:
  - SKIP 10000 TAKE 20: database still scans 10,020 rows — O(n) cost
  - Page drift: a new item inserted at page 1 shifts all rows — page 2 shows a duplicate
  - COUNT(*) is expensive on large tables
  - Not suitable for > 100,000 rows

// Cursor encodes the last seen row's sort key values
public record CursorPageQuery(
    string? Cursor   = null,   // base64-encoded cursor from previous page
    int     PageSize = 20)
    : IRequest<CursorPageResult<OrderDto>>;

public record CursorPageResult<T>(
    IReadOnlyList<T> Items,
    string?          NextCursor,     // null if last page
    bool             HasNextPage);

// Cursor = the sort key of the last item on the previous page
// Encoded as base64 JSON to keep the API opaque

public record OrderCursor(DateTime CreatedAt, int Id);

public class GetOrdersCursorHandler(AppDbContext context)
    : IRequestHandler<CursorPageQuery, CursorPageResult<OrderDto>>
{
    public async Task<CursorPageResult<OrderDto>> Handle(CursorPageQuery q, CancellationToken ct)
    {
        var cursor = DecodeCursor(q.Cursor);

        var query = context.Orders.AsNoTracking();

        if (cursor is not null)
        {
            // Items after the cursor — keyset filter
            // (CreatedAt, Id) < (cursor.CreatedAt, cursor.Id) for descending order
            query = query.Where(o =>
                o.CreatedAt < cursor.CreatedAt ||
                (o.CreatedAt == cursor.CreatedAt && o.Id < cursor.Id));
        }

        // Fetch one extra to detect if there is a next page
        var items = await query
            .OrderByDescending(o => o.CreatedAt)
            .ThenByDescending(o => o.Id)
            .Take(q.PageSize + 1)
            .Select(o => new { o.Id, o.CustomerId, o.Total, o.Status, o.CreatedAt })
            .ToListAsync(ct);

        var hasNextPage = items.Count > q.PageSize;
        if (hasNextPage)
            items.RemoveAt(items.Count - 1);   // remove the extra sentinel item

        var nextCursor = hasNextPage
            ? EncodeCursor(new OrderCursor(items.Last().CreatedAt, items.Last().Id))
            : null;

        return new CursorPageResult<OrderDto>(
            Items:       items.Select(o => new OrderDto(o.Id, o.CustomerId, o.Total, o.Status)).ToList(),
            NextCursor:  nextCursor,
            HasNextPage: hasNextPage);
    }

    private static OrderCursor? DecodeCursor(string? cursor)
    {
        if (cursor is null) return null;
        var json = Encoding.UTF8.GetString(Convert.FromBase64String(cursor));
        return JsonSerializer.Deserialize<OrderCursor>(json);
    }

    private static string EncodeCursor(OrderCursor cursor)
    {
        var json = JsonSerializer.Serialize(cursor);
        return Convert.ToBase64String(Encoding.UTF8.GetBytes(json));
    }
}

// API endpoint
[HttpGet("orders/cursor")]
public async Task<IActionResult> GetOrdersCursor(
    [FromQuery] string? cursor   = null,
    [FromQuery] int     pageSize = 20,
    CancellationToken   ct       = default)
{
    pageSize = Math.Clamp(pageSize, 1, 100);
    var result = await mediator.Send(new CursorPageQuery(cursor, pageSize), ct);
    return Ok(result);
}

// Response
{
  "items": [...],
  "nextCursor": "eyJDcmVhdGVkQXQiOiIyMDI2LTA1LTIxVDEwOjMwOjAwWiIsIklkIjo0MjF9",
  "hasNextPage": true
}

// Next request:
// GET /orders/cursor?cursor=eyJDcmVhdGVkQXQiOiIyMDI2...

Keyset pagination advantages:
  - O(1) regardless of depth — uses index seek, not scan
  - No page drift — new items don't affect cursor position
  - No COUNT(*) needed

Keyset pagination limitations:
  - No random access (cannot jump to page 5)
  - Cursor is tied to the sort order — changing sort requires new cursor
  - More complex implementation

// Offset with Dapper
public async Task<PagedResult<OrderDto>> GetPagedAsync(int page, int pageSize, CancellationToken ct)
{
    using var conn = dbFactory.CreateReadConnection();

    const string dataSql = """
        SELECT Id, CustomerId, Total, Status
        FROM Orders
        WHERE Status != 'Deleted'
        ORDER BY CreatedAt DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
        """;

    const string countSql = "SELECT COUNT(*) FROM Orders WHERE Status != 'Deleted'";

    var items      = (await conn.QueryAsync<OrderDto>(dataSql, new { Offset = (page-1)*pageSize, PageSize = pageSize })).ToList();
    var totalCount = await conn.ExecuteScalarAsync<int>(countSql);

    return new PagedResult<OrderDto>(items, page, pageSize, totalCount,
        (int)Math.Ceiling(totalCount / (double)pageSize),
        page * pageSize < totalCount, page > 1);
}

// Keyset with Dapper
public async Task<List<OrderDto>> GetAfterCursorAsync(DateTime? afterDate, int? afterId, int pageSize)
{
    using var conn = dbFactory.CreateReadConnection();

    const string sql = """
        SELECT Id, CustomerId, Total, Status
        FROM Orders
        WHERE (@AfterDate IS NULL OR CreatedAt < @AfterDate
               OR (CreatedAt = @AfterDate AND Id < @AfterId))
        ORDER BY CreatedAt DESC, Id DESC
        LIMIT @PageSize
        """;

    return (await conn.QueryAsync<OrderDto>(sql, new { AfterDate = afterDate, AfterId = afterId, PageSize = pageSize })).ToList();
}

                   Offset          Keyset/Cursor
Implementation     Simple          Moderate
Random access      Yes             No
Performance        O(n) for deep   O(1) always
Page drift         Yes             No
Total count        Yes             No
Sort flexibility   Any column      Must match cursor
Best for           Small tables    Large tables, infinite scroll
                   Admin pages     Real-time feeds, APIs