Skip to content
Prague
Improve this page

Page an API

Goal: a GET /customers/{id}/orders?skip=&take= endpoint that returns a stable page, the total, and nothing that points into a pooled buffer.

Prerequisite: an OrderCache (see Quick Start). The code is in PageAnApi.cs; every block runs as a test.

1. Pick a comparer that never returns 0 for two different rows

Paging over a sort that has ties is where duplicates and gaps come from: rows with equal keys can land on either side of a page boundary from one call to the next. Add the primary key as the last tie-break.

/// <summary>Newest first; OrderId breaks ties, so two distinct orders never compare equal.</summary>
public readonly struct ByCreatedAtDescThenId : IComparer<Order> {
    public int Compare(Order? x, Order? y) {
        var byDate = y!.CreatedAtUnixMs.CompareTo(x!.CreatedAtUnixMs);
        return byDate != 0 ? byDate : string.CompareOrdinal(x.OrderId, y.OrderId);
    }
}

A readonly struct comparer is passed by value, so the sort step is devirtualized. Order also declares [DataCacheSort("ByCreatedAt", "CreatedAtUnixMs:-1", "OrderId:1")], which generates the equivalent OrderCache.ByCreatedAtComparer; use that when the sort is a fixed property of the entity, write a struct when it is a property of the endpoint.

2. Define the DTOs

Rows from ExecutePooled are the cache's own instances and the buffer that holds them is on loan. Copy what the response needs before the buffer goes back.

public sealed record OrderSummary(string OrderId, long CreatedAtUnixMs, decimal Total);

public sealed record OrderPage(OrderSummary[] Items, int TotalCount, string? NextCursor);

3. Offset page with SortBounded

SortBounded(comparer).ExecutePooled(skip, take) selects the page with a bounded top-k pass; the rows outside the window are never sorted. TotalCount is the match count before paging.

using var page = orders.Query()
    .WithCustomerId(42)
    .SortBounded(new ByCreatedAtDescThenId())           // top-k: only the page is sorted
    .ExecutePooled(skip, take);

var response = new OrderPage(
    CustomerOrderPages.ToSummaries(page),               // copy into DTOs while the buffer is alive
    page.TotalCount,                                    // matches before paging: "showing 5 of 25"
    NextCursor: null);

When you need the whole result sorted, use Sort: it is the better plan when there is no page to bound.

using var all = orders.Query()
    .WithCustomerId(42)
    .Sort(OrderCache.ByCreatedAtComparer)               // the generated comparer: "CreatedAtUnixMs:-1", "OrderId:1"
    .ExecutePooled();                                   // no page: Sort is the right plan

The difference between the two, and when each wins, is measured in Sorted Paging.

4. Cursor page

An offset page shifts when rows are inserted ahead of it. A cursor page encodes the last row's sort key (CreatedAtUnixMs, OrderId) and asks for everything strictly after it in sort order. The Range lane on CreatedAtUnixMs narrows candidates; the Where drops the tie rows already served.

// Cursor = the last row's (CreatedAtUnixMs, OrderId). Everything strictly after it in sort order is the next page.
private static OrderPage CursorPage(OrderCache orders, string? after, int take) {
    var query = orders.Query().WithCustomerId(42);

    if (after is null) {
        using var firstPage = query.SortBounded(new ByCreatedAtDescThenId()).ExecutePooled(0, take);
        return ToPage(firstPage, take);
    }

    var cut = after.IndexOf('|');
    var cursorTs = long.Parse(after[..cut]);
    var cursorId = after[(cut + 1)..];
    using var page = query
        .WithCreatedAtUnixMs(q => q.Lte(cursorTs))          // Range lane: everything at or before the cursor time
        .Where(o => o.CreatedAtUnixMs < cursorTs || string.CompareOrdinal(o.OrderId, cursorId) > 0)   // skip the tie rows already served
        .SortBounded(new ByCreatedAtDescThenId())
        .ExecutePooled(0, take);
    return ToPage(page, take);
}

private static OrderPage ToPage(QueryResults<Order> rows, int take) {
    var items = CustomerOrderPages.ToSummaries(rows);
    var last = rows.Count == take ? rows[^1] : null;
    return new OrderPage(items, rows.TotalCount, last is null ? null : $"{last.CreatedAtUnixMs}|{last.OrderId}");
}

The test walks three cursor pages over 25 rows with five-way ties on the timestamp and gets 25 distinct ids.

5. Freeze it for the hot path

The endpoint asks the same shape every time, with only the customer and the window changing. Prepare<TArgs>() with a struct argument plans it once; ExecutePooled(in args, skip, take) costs no allocation per call.

public sealed class CustomerOrderPages {
    public readonly record struct Args(int CustomerId);                 // TArgs must be a struct

    private readonly FrozenQuery<Args, Order> _byCustomer;

    public CustomerOrderPages(OrderCache orders) {
        _byCustomer = orders.Prepare<Args>()
            .WithCustomerId(static a => a.CustomerId)
            .SortBounded(new ByCreatedAtDescThenId())                      // total comparer: pages identical to eager
            .BuildFrozen();
    }

    public OrderPage Get(int customerId, int skip, int take) {
        using var rows = _byCustomer.ExecutePooled(new Args(customerId), skip, take);   // 0 B per call
        return new OrderPage(ToSummaries(rows), rows.TotalCount, NextCursor: null);
    }

    internal static OrderSummary[] ToSummaries(QueryResults<Order> rows) {
        var items = new OrderSummary[rows.Count];
        for (var i = 0; i < rows.Count; i++)
            items[i] = new OrderSummary(rows[i].OrderId, rows[i].CreatedAtUnixMs, rows[i].Total);
        return items;
    }
}

PreparedPageMatchesEager asserts the frozen page is byte-for-byte the eager page. That holds because the comparer is total; an unsorted frozen query has no row-order guarantee at all (see Prepared Queries).

Pitfalls

  • A non-total comparer. TiesWithoutATieBreakAreUnspecifiedUnderSort shows the symptom: the set of a page is right, the rows inside a tie group are in encounter order, which is not a contract.
  • Returning QueryResults<T> or a ReadOnlySpan<T> over it from the handler. The buffer is disposed when the using ends; the serializer reads it after that. Return DTOs.
  • skip beyond TotalCount. You get an empty page and the correct TotalCount, not an exception.
  • Sort + take for small pages over large matches. It sorts everything. Use SortBounded.
Prague documentation