Cursor-Based Pagination & Sorting

In the previous presentation, you built dynamic query filters. Now you will add cursor-based pagination to those queries, implement configurable sorting, and set up response caching headers. By the end, your search endpoint will return paginated results with full navigation metadata.

The Pagination Response DTO

Define a generic container for paginated results that works with any entity type:

csharp
1public class PagedResult<T>
2{
3    public List<T> Items { get; set; } = [];
4    public int TotalCount { get; set; }
5    public int PageSize { get; set; }
6    public string? Cursor { get; set; }
7    public string? NextCursor { get; set; }
8    public bool HasNextPage { get; set; }
9}

This DTO gives clients everything they need to render results and navigate pages:

• Items: The current page of results
• TotalCount: Total number of matching records (useful for showing "1-20 of 1,547")
• PageSize: The actual page size used (after clamping)
• Cursor: The cursor representing the start of the current page
• NextCursor: The cursor to fetch the next page (null if no more pages)
• HasNextPage: A convenience boolean so clients do not need to null-check NextCursor

How Cursor-Based Pagination Works

Cursor pagination uses a value from the last item on the current page to determine where the next page starts. The cursor encodes enough information to uniquely identify a position in the sorted result set.

The Cursor Value

For a query sorted by CreatedAt DESC, the cursor must encode:

1. The CreatedAt value of the last item on the current page
2. The Id of the last item (as a tiebreaker for items with the same timestamp)

Encoding both values ensures a unique cursor even when multiple parcels share the same CreatedAt value.

Encoding and Decoding

Use Base64 encoding to create opaque cursor strings. The client treats the cursor as an opaque token and passes it back unchanged.

csharp
1public static class CursorHelper
2{
3    public static string Encode(DateTime sortValue, Guid id)
4    {
5        var raw = $"{sortValue:O}|{id}";
6        return Convert.ToBase64String(Encoding.UTF8.GetBytes(raw));
7    }
8
9    public static (DateTime SortValue, Guid Id) Decode(string cursor)
10    {
11        var raw = Encoding.UTF8.GetString(
12            Convert.FromBase64String(cursor));
13        var parts = raw.Split('|');
14
15        return (
16            DateTime.Parse(parts[0], null,
17                System.Globalization.DateTimeStyles.RoundtripKind),
18            Guid.Parse(parts[1])
19        );
20    }
21}

A cursor looks like MjAyNS0wMi0xNVQxMDozMDowMC4wMDAwMDAwWnw0YTNiLi4u. The client cannot interpret it, which gives you freedom to change the encoding later without breaking the API contract.

Applying the Cursor to the Query

When the client provides a cursor, add a WHERE clause that skips all rows before the cursor position:

csharp
1private static IQueryable<Parcel> ApplyCursor(
2    IQueryable<Parcel> query,
3    ParcelSearchParams searchParams)
4{
5    if (string.IsNullOrEmpty(searchParams.Cursor))
6        return query;
7
8    var (sortValue, lastId) = CursorHelper.Decode(searchParams.Cursor);
9
10    if (searchParams.SortDescending)
11    {
12        // For DESC order: get rows with smaller sort values,
13        // or same sort value but smaller Id
14        query = query.Where(p =>
15            p.CreatedAt < sortValue ||
16            (p.CreatedAt == sortValue && p.Id.CompareTo(lastId) < 0));
17    }
18    else
19    {
20        // For ASC order: get rows with larger sort values,
21        // or same sort value but larger Id
22        query = query.Where(p =>
23            p.CreatedAt > sortValue ||
24            (p.CreatedAt == sortValue && p.Id.CompareTo(lastId) > 0));
25    }
26
27    return query;
28}

This is the key advantage of cursor pagination: the WHERE clause uses an indexed comparison (< or >) instead of OFFSET. The database can seek directly to the right position using an index, regardless of how deep into the result set you are.

Implementing Sorting

Support multiple sort fields with a fallback to the default sort order. The sort field determines which column the cursor references.

csharp
1private static IQueryable<Parcel> ApplySorting(
2    IQueryable<Parcel> query,
3    ParcelSearchParams searchParams)
4{
5    var sortBy = searchParams.SortBy?.ToLower() ?? "createdat";
6
7    query = (sortBy, searchParams.SortDescending) switch
8    {
9        ("createdat", true) => query
10            .OrderByDescending(p => p.CreatedAt)
11            .ThenByDescending(p => p.Id),
12        ("createdat", false) => query
13            .OrderBy(p => p.CreatedAt)
14            .ThenBy(p => p.Id),
15        ("estimateddeliverydate", true) => query
16            .OrderByDescending(p => p.EstimatedDeliveryDate)
17            .ThenByDescending(p => p.Id),
18        ("estimateddeliverydate", false) => query
19            .OrderBy(p => p.EstimatedDeliveryDate)
20            .ThenBy(p => p.Id),
21        ("status", true) => query
22            .OrderByDescending(p => p.Status)
23            .ThenByDescending(p => p.Id),
24        ("status", false) => query
25            .OrderBy(p => p.Status)
26            .ThenBy(p => p.Id),
27        _ => query
28            .OrderByDescending(p => p.CreatedAt)
29            .ThenByDescending(p => p.Id)
30    };
31
32    return query;
33}

The Tiebreaker Column

Every sort configuration includes .ThenBy(p => p.Id) or .ThenByDescending(p => p.Id). This tiebreaker is mandatory for cursor-based pagination because:

1. Multiple parcels can share the same CreatedAt value
2. Without a tiebreaker, the cursor cannot uniquely identify a position
3. The Id column (a GUID or auto-increment) is always unique

Handling Different Sort Fields with Cursors

When the sort field changes, the cursor encoding must match. A more robust cursor implementation encodes the sort field along with the value:

csharp
1public static class CursorHelper
2{
3    public static string Encode(string sortField, string sortValue, Guid id)
4    {
5        var raw = $"{sortField}|{sortValue}|{id}";
6        return Convert.ToBase64String(Encoding.UTF8.GetBytes(raw));
7    }
8
9    public static (string SortField, string SortValue, Guid Id)
10        Decode(string cursor)
11    {
12        var raw = Encoding.UTF8.GetString(
13            Convert.FromBase64String(cursor));
14        var parts = raw.Split('|', 3);
15        return (parts[0], parts[1], Guid.Parse(parts[2]));
16    }
17}

This approach ensures a cursor generated for a CreatedAt sort is not accidentally used with an EstimatedDeliveryDate sort. If the sort field in the cursor does not match the current request, you can either ignore the cursor (start from the beginning) or return a validation error.

Building the Paged Result

After executing the query, construct the response with pagination metadata:

csharp
1private PagedResult<ParcelDto> BuildPagedResult(
2    List<Parcel> parcels,
3    ParcelSearchParams searchParams,
4    int totalCount)
5{
6    // We fetched pageSize + 1 to detect if there's a next page
7    var hasNextPage = parcels.Count > searchParams.PageSize;
8
9    // Take only the requested page size
10    var items = parcels
11        .Take(searchParams.PageSize)
12        .Select(p => MapToDto(p))
13        .ToList();
14
15    string? nextCursor = null;
16    if (hasNextPage && items.Count > 0)
17    {
18        var lastItem = parcels[searchParams.PageSize - 1];
19        nextCursor = CursorHelper.Encode(
20            lastItem.CreatedAt, lastItem.Id);
21    }
22
23    return new PagedResult<ParcelDto>
24    {
25        Items = items,
26        TotalCount = totalCount,
27        PageSize = searchParams.PageSize,
28        Cursor = searchParams.Cursor,
29        NextCursor = nextCursor,
30        HasNextPage = hasNextPage
31    };
32}

The "Fetch One Extra" Trick

Notice that the query fetches pageSize + 1 items:

csharp
1var parcels = await query
2    .Take(searchParams.PageSize + 1)
3    .ToListAsync();

If you get pageSize + 1 rows back, there are more results after this page. If you get pageSize or fewer rows, this is the last page. This avoids a separate query to check whether a next page exists.

The extra item is not included in the response. Only the first pageSize items are returned.

Complete Search Endpoint

Here is the full controller action combining filters, sorting, pagination, and cache headers:

csharp
1[ApiController]
2[Route("api/[controller]")]
3public class ParcelsController : ControllerBase
4{
5    private readonly ParcelTrackingDbContext _context;
6
7    public ParcelsController(ParcelTrackingDbContext context)
8    {
9        _context = context;
10    }
11
12    [HttpGet]
13    [ResponseCache(Duration = 30, VaryByQueryKeys = ["*"])]
14    public async Task<ActionResult<PagedResult<ParcelDto>>> SearchParcels(
15        [FromQuery] ParcelSearchParams searchParams)
16    {
17        // Validate and clamp page size
18        searchParams.PageSize = Math.Clamp(
19            searchParams.PageSize, 1, 100);
20
21        // Build base query
22        IQueryable<Parcel> query = _context.Parcels
23            .Include(p => p.ShipperAddress)
24            .Include(p => p.RecipientAddress);
25
26        // Apply dynamic filters
27        query = ApplyFilters(query, searchParams);
28
29        // Get total count (before pagination)
30        var totalCount = await query.CountAsync();
31
32        // Apply sorting
33        query = ApplySorting(query, searchParams);
34
35        // Apply cursor
36        query = ApplyCursor(query, searchParams);
37
38        // Fetch one extra to detect next page
39        var parcels = await query
40            .Take(searchParams.PageSize + 1)
41            .ToListAsync();
42
43        // Build response
44        var result = BuildPagedResult(
45            parcels, searchParams, totalCount);
46
47        return Ok(result);
48    }
49}

The Execution Order Matters

The operations must happen in this specific order:

1. Filter - Narrow down the dataset
2. Count - Get total matching records (needed in response metadata)
3. Sort - Establish the order for pagination
4. Cursor - Skip to the correct position
5. Take - Limit to page size + 1
6. Materialize - Execute the query and load results

If you count after applying the cursor, you get the count of remaining items, not the total count. If you sort after applying the cursor, the cursor position is meaningless.

Adding Response Cache Headers

The [ResponseCache] attribute adds Cache-Control headers to the response. For more control, set headers manually:

csharp
1[HttpGet]
2public async Task<ActionResult<PagedResult<ParcelDto>>> SearchParcels(
3    [FromQuery] ParcelSearchParams searchParams)
4{
5    // ... query logic ...
6
7    Response.Headers.CacheControl = "public, max-age=30";
8    Response.Headers.Vary = "Accept, Accept-Encoding";
9
10    return Ok(result);
11}

Response Caching Middleware

To enable server-side caching, add the response caching middleware in Program.cs:

csharp
1builder.Services.AddResponseCaching();
2
3var app = builder.Build();
4
5app.UseResponseCaching();

This middleware caches responses based on the Cache-Control headers and Vary settings. Subsequent identical requests (same URL, same query parameters, same Accept header) are served from the cache without hitting the database.

VaryByQueryKeys

The [ResponseCache] attribute supports VaryByQueryKeys to create separate cache entries for each unique combination of query parameters:

csharp
1[ResponseCache(Duration = 30, VaryByQueryKeys = ["*"])]

The "*" wildcard means every distinct query string gets its own cache entry. A request for ?status=InTransit&pageSize=10 is cached separately from ?status=Delivered&pageSize=20.

Page Size Clamping

Always enforce page size boundaries on the server side:

csharp
1searchParams.PageSize = Math.Clamp(searchParams.PageSize, 1, 100);

Math.Clamp keeps the value between 1 and 100:

This protects the server from clients requesting excessively large pages while being lenient about input.

An Example Response

A typical paginated search response looks like this:

json
1{
2  "items": [
3    {
4      "id": "a1b2c3d4-...",
5      "trackingNumber": "PKG-20250215-X7K9M2",
6      "status": "InTransit",
7      "serviceType": "Express",
8      "shipperCity": "New York",
9      "recipientCity": "Berlin",
10      "createdAt": "2025-02-15T10:30:00Z",
11      "estimatedDeliveryDate": "2025-02-17T18:00:00Z"
12    },
13    {
14      "id": "e5f6g7h8-...",
15      "trackingNumber": "PKG-20250215-B3N5P8",
16      "status": "InTransit",
17      "serviceType": "Express",
18      "shipperCity": "Chicago",
19      "recipientCity": "Munich",
20      "createdAt": "2025-02-15T09:15:00Z",
21      "estimatedDeliveryDate": "2025-02-18T12:00:00Z"
22    }
23  ],
24  "totalCount": 347,
25  "pageSize": 20,
26  "cursor": null,
27  "nextCursor": "MjAyNS0wMi0xNVQwODo0NTowMC4wMDAwMDAwWnxmOWUxYTIz...",
28  "hasNextPage": true
29}

The client renders the 20 items and uses nextCursor to fetch the next page:

GET /api/parcels?status=InTransit&serviceType=Express&cursor=MjAyNS0wMi0xNVQwODo0NTowMC4wMDAwMDAwWnxmOWUxYTIz...

Common Pitfalls

Forgetting the Tiebreaker Sort

Without .ThenBy(p => p.Id), parcels with the same CreatedAt can appear on multiple pages or be skipped entirely. Always include a unique tiebreaker column.

Counting After Cursor

If you run CountAsync() after applying the cursor WHERE clause, you count only the remaining items, not the total. Always count before applying the cursor.

Inconsistent Sort and Cursor Direction

If the sort is DESC but the cursor uses > instead of <, the cursor skips in the wrong direction. Match the comparison operator to the sort direction.

Cursor Tampering

Clients might send invalid or malformed cursors. Wrap the decode in a try-catch and return the first page if the cursor is invalid:

csharp
1try
2{
3    var (sortValue, lastId) = CursorHelper.Decode(searchParams.Cursor);
4    // apply cursor filter
5}
6catch
7{
8    // Invalid cursor, start from the beginning
9}

Summary

In this presentation, you learned:

• Cursor-based pagination uses a WHERE clause with indexed comparisons instead of OFFSET
• The cursor encodes the sort field value and the record ID for unique positioning
• Base64 encoding creates opaque cursor tokens that clients pass back unchanged
• Fetching pageSize + 1 detects the next page without a separate count query
• Sorting requires a tiebreaker column (Id) for deterministic ordering
• The execution order is: filter, count, sort, cursor, take, materialize
• Math.Clamp enforces page size boundaries without rejecting requests
• Response caching with Cache-Control and VaryByQueryKeys reduces database load
• Always handle invalid cursors gracefully by falling back to the first page