Pagination, Filtering and Sorting

Page-and-size against cursor pagination, why offsets get slow, and sorting safely through an allow-list.

SyntaxGET /books?page=2&pageSize=20&sort=title | GET /books?after=42&pageSize=20

A collection endpoint that returns everything works until the collection is large. Then one request reads a million rows, serialises them and times out. Pagination is returning a slice and telling the client how to ask for the next one.

Page and size

?page=2&pageSize=20 skips the first 20 rows and takes 20. It is easy to implement and easy for a client to use: page numbers in a footer come out of it directly. It has two problems. The first is drift. If a row is inserted while the client is reading page 1, page 2 begins one row earlier than it should, so one row is seen twice and one is never seen. The second is cost. To skip 100,000 rows a database must still find and discard 100,000 rows, so page 5,000 is far slower than page 1.

Cursor pagination

A cursor is a marker saying where the last page ended. The client asks for rows after that marker, so there is nothing to skip and the query uses an index. Page 5,000 costs what page 1 costs, and an insert cannot shift the window. The price is that you cannot jump to an arbitrary page, only forwards and backwards. Use page-and-size for admin screens over small tables, and a cursor for feeds, exports and anything a machine will walk to the end.

Sorting safely

A sort field arrives as text from a stranger. Never put it into a query. Map the allowed names to real keys and reject anything else.

Example

Example · csharp
List<Book> books =
[
    new(1, "Dune"), new(2, "Emma"), new(3, "Hamlet"),
    new(4, "Ivanhoe"), new(5, "Kim")
];

// Page and size: skip (page - 1) * size rows, then take size rows.
int page = 2;
int size = 2;
List<Book> byPage = books.OrderBy(b => b.Id).Skip((page - 1) * size).Take(size).ToList();
Console.WriteLine("page 2: " + string.Join(", ", byPage.Select(b => b.Title)));

// Cursor: "the rows after id 3". Nothing is skipped, so nothing is scanned
// and discarded. The next cursor is the last id in this slice.
int after = 3;
List<Book> byCursor = books.OrderBy(b => b.Id).Where(b => b.Id > after).Take(size).ToList();
Console.WriteLine("after 3: " + string.Join(", ", byCursor.Select(b => b.Title)));

// Sorting: the request sends a name. Only these names mean anything.
Dictionary<string, Func<Book, object>> allowedSorts = new()
{
    ["id"] = b => b.Id,
    ["title"] = b => b.Title
};

string requestedSort = "title";
if (allowedSorts.TryGetValue(requestedSort, out Func<Book, object>? key))
{
    Console.WriteLine("sorted: " + string.Join(", ", books.OrderBy(key).Select(b => b.Title)));
}

// Output:
// page 2: Hamlet, Ivanhoe
// after 3: Ivanhoe, Kim
// sorted: Dune, Emma, Hamlet, Ivanhoe, Kim

record Book(int Id, string Title);

When to use it

  • Fixing a list endpoint that returns 200,000 rows and times out in production.
  • Giving a sync job a way to walk a whole table once without missing rows that were inserted mid-walk.
  • Accepting ?sort=title from a client without letting it sort by, or inject into, a column you never exposed.
  • Telling a mobile client whether another page exists without running a COUNT over the full table.

More examples

A page endpoint that validates its own query string

Returning a bare array leaves the client guessing when to stop. The envelope carries the total so a UI can draw a page count. Math.Clamp is the whole defence against pageSize=1000000. Use this shape for an admin list where users expect numbered pages.

Example · csharp
// A record groups the query parameters. [AsParameters] tells the framework
// to bind each property from the query string instead of the body.
public record PageQuery(int Page = 1, int PageSize = 20, string Sort = "id");

// The envelope the client needs to know whether another page exists.
public record Page<T>(List<T> Items, int Page, int PageSize, int TotalCount);

app.MapGet("/books", ([AsParameters] PageQuery query, IBookStore store) =>
{
    // Clamp rather than reject: a hostile pageSize becomes a sane one.
    int page = Math.Max(query.Page, 1);
    int pageSize = Math.Clamp(query.PageSize, 1, 100);

    List<Book> all = store.All();
    List<Book> items = all
        .OrderBy(b => b.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToList();

    return Results.Ok(new Page<Book>(items, page, pageSize, all.Count));
});

Cursor pagination with the next cursor in the response

The take-one-extra trick answers "is there more?" without a second COUNT query over the whole table. NextCursor being null is the end of the data, which gives a client walking the feed a clear stopping condition. Use this for exports, sync jobs and infinite-scroll feeds.

Example · csharp
public record CursorPage<T>(List<T> Items, int? NextCursor);

app.MapGet("/feed", (int? after, int pageSize, IBookStore store) =>
{
    int size = Math.Clamp(pageSize, 1, 100);

    // Ask for one more row than requested. If it comes back, there is a
    // next page, and the extra row is not returned to the client.
    List<Book> rows = store.All()
        .OrderBy(b => b.Id)
        .Where(b => after is null || b.Id > after.Value)
        .Take(size + 1)
        .ToList();

    bool hasMore = rows.Count > size;
    List<Book> items = rows.Take(size).ToList();

    return Results.Ok(new CursorPage<Book>(
        items,
        hasMore ? items[^1].Id : null));
});

An allow-list that also covers the direction

The dictionary is the contract: a name not in it cannot reach the database, so there is no string to inject into and no way to sort by a column you never meant to expose. Building the sort clause by concatenating the parameter into SQL is the bug this prevents. Use it on every endpoint that accepts a sort parameter.

Example · csharp
public static class BookSorting
{
    // The only sort fields that exist, as far as the API is concerned.
    private static readonly Dictionary<string, Func<Book, object>> Keys = new()
    {
        ["id"] = b => b.Id,
        ["title"] = b => b.Title,
        ["author"] = b => b.Author
    };

    public static IEnumerable<Book> Sort(IEnumerable<Book> books, string? sort)
    {
        // "title" or "-title"; the leading minus means descending.
        bool descending = sort is not null && sort.StartsWith('-');
        string name = descending ? sort![1..] : sort ?? "id";

        if (!Keys.TryGetValue(name, out Func<Book, object>? key))
        {
            // Unknown field: fall back, or throw and return 400. Never
            // pass the text through to the query.
            key = Keys["id"];
            descending = false;
        }

        return descending ? books.OrderByDescending(key) : books.OrderBy(key);
    }
}

Related

dotnet linq filter projectdotnet linq order groupdotnet rest designdotnet model bindingdotnet validation

Discussion

  • Be the first to comment on this lesson.