Pagination

GET /api/employees that returns every employee works perfectly with the forty rows in your development database. At forty thousand it returns a response measured in megabytes, holds a database connection while it does, and times out somewhere between your server and the caller.

Paging is the fix, and it belongs in the contract from the first release. Added later it changes the top-level shape of the response, which breaks every caller — the point the first lesson of this course made about bare arrays.

GET /api/employees?page=2&pageSize=20 HTTP/1.1
Host: api.example.com

HTTP/1.1 200 OK
Content-Type: application/json

{
  "items": [
    { "id": 118, "fullName": "Asha Menon", "departmentName": "Support" },
    { "id": 119, "fullName": "Ben Okafor", "departmentName": "Support" }
  ],
  "page": 2,
  "pageSize": 20,
  "totalCount": 431,
  "totalPages": 22
}

• The paging parameters are in the query string, because they narrow a read rather than identifying a resource.
• The response echoes page and pageSize back. If the server clamped what was asked for, that is how the caller finds out.
• totalCount and totalPages let a caller draw page numbers. They cost a second query, which is a real cost on a large table — offering them is a decision, not a default.
• items is nested inside an object. Adding a field to this response later is additive and safe; adding one to a bare array is not possible.

The drift problem is worth making concrete, because it sounds theoretical until it produces a bug report. Employees are ordered by surname. A caller reads page 1, rows 1 to 20. Before it reads page 2, someone hires an employee whose surname sorts into row 5. Every later row shifts down by one. Page 2 now starts with the row that used to end page 1, so the caller sees one row twice and, if a deletion happens instead, misses one entirely.

For a person clicking through an admin screen, a repeated row is a curiosity. For a job exporting every employee into a payroll file, a skipped row is a person who does not get paid. That difference is the whole basis for choosing between the two approaches.

Cursor paging avoids it by asking a different question. Instead of "skip 20 rows", it asks "give me rows after Okafor, id 119". Inserting a row before that position changes nothing about where the next page starts. The technique is also called keyset pagination, and the cursor is usually the sort key plus a unique tiebreaker, encoded so callers treat it as opaque rather than something to construct themselves.

const int DefaultPageSize = 20;
const int MaxPageSize = 100;

app.MapGet("/api/employees", async (
    AppDbContext db, CancellationToken ct,
    int page = 1, int pageSize = DefaultPageSize) =>
{
    var safePage = Math.Max(page, 1);
    var safeSize = Math.Clamp(pageSize, 1, MaxPageSize);

    var query = db.Employees
        .OrderBy(e => e.Surname)
        .ThenBy(e => e.Id);

    var totalCount = await query.CountAsync(ct);

    var items = await query
        .Skip((safePage - 1) * safeSize)
        .Take(safeSize)
        .Select(e => new EmployeeListItem(e.Id, e.FullName, e.Department!.Name))
        .ToListAsync(ct);

    return Results.Ok(new
    {
        items,
        page = safePage,
        pageSize = safeSize,
        totalCount,
        totalPages = (int)Math.Ceiling(totalCount / (double)safeSize)
    });
});

• page and pageSize have defaults, so a caller that sends neither gets the first page rather than everything.
• Math.Clamp is the line that matters most in this sample. Whatever arrives — 0, -5, 50000 — the server uses a value it chose. Without it, pageSize is a caller-controlled instruction about how much work your database does.
• ThenBy(e => e.Id) is not decoration. Surnames repeat, and a database is free to return tied rows in any order, so without a unique tiebreaker two requests for the same page can return different rows even when nothing changed.
• CountAsync is a second query over the whole filtered set. On a large table it can cost more than fetching the page, which is why some APIs drop totalCount or compute it only on the first page.
• Skip translates to OFFSET. The database still has to pass over the skipped rows, so deep pages get slower — the cost that cursor paging removes.