Catalog My learning Blog
Full-Stack Developer Course: React, .NET and SQL Server · Phase 3 · Lesson 24 · 6 min read

ASP.NET Core Pagination, Filtering and Sorting

On this page
  1. Learning Objective
  2. Concept Explanation
  3. Worked Example: Keyset Paging with a Sort Allowlist and a Capped Page Size
  4. Real-World Examples
  5. Review Questions & Answers

Learning Objective

Implement offset and keyset pagination, explain when each is appropriate, enforce maximum page sizes, accept filters and sort fields through an allowlist, and design paged responses a React client can consume, while understanding the abuse cases unbounded queries create.

Concept Explanation

Any endpoint that returns a collection must decide how much to return. An endpoint that returns every row is fine with 20 test records and disastrous with 2 million, and, worse, it is an easy denial-of-service vector: a single request asks the server to read, serialize, and transmit an enormous result. Pagination bounds the work. Every collection endpoint needs a default page size and a hard maximum that the server enforces, so a client asking for a page size of one million is silently clamped to the limit, or rejected with a 400.

Offset pagination uses page and pageSize, or skip and take, and translates to OFFSET and FETCH in SQL. It is simple and lets users jump to page 7, so it suits small or medium datasets with page-number navigation. It has two weaknesses. Deep pages are slow because the database must still walk past all skipped rows, and results shift when rows are inserted or deleted between requests, so a user paging through a busy feed sees duplicates or misses items. It also usually needs a total count, which on a large table is itself an expensive query.

Keyset pagination, also called cursor pagination, fixes both. Instead of skipping N rows, the client asks for the rows after the last one it saw, for example created before a timestamp with an id tie-breaker, and the database uses an index to seek straight to that position. Performance stays constant on deep pages, and inserts do not cause duplicates. The trade-offs are that you cannot jump to an arbitrary page, sorting must be on a stable, indexed, unique combination of columns, and the cursor should be treated as opaque by the client. Infinite scroll and feeds suit cursors, while an admin table with page numbers suits offsets.

Filtering and sorting deserve the same discipline as anything else that touches a query. Never build sort or filter expressions by concatenating client strings into SQL or dynamic LINQ; map the sort parameter through an allowlist of permitted fields to known expressions, and reject anything else. Restrict filters to indexed columns that make sense, limit the number of values in a list filter, and remember that filtering must happen after applying authorization, so a filter cannot be used to probe records the caller may not see. Finally, describe the paging in the response, with the items, a nextCursor or total, and optionally a Link header, so the React side can drive TanStack Query's infinite queries directly.

ApproachStrengthsWeaknessesBest for
Offset (page, pageSize)Simple, page numbers, jump aheadSlow deep pages, unstable under changes, count costSmall to medium tables, admin grids
Keyset (cursor)Constant speed, stable resultsNo random access, needs unique sort keyFeeds, timelines, large tables
Page size capBounds work per requestNoneAlways
Sort allowlistPrevents injection and unindexed sortsSlightly more codeAlways
Total countEnables page number UIExtra query on big tablesOnly when the UI needs it

Worked Example: Keyset Paging with a Sort Allowlist and a Capped Page Size

The code implements a task list with an opaque cursor, a capped size, and an allowlisted sort. It uses in-memory data so the logic is visible; the same expressions work with Entity Framework.

C#
public record PageQuery(string? Cursor, int PageSize = 25, string Sort = "created");
public record Page<T>(IReadOnlyList<T> Items, string? NextCursor);

public static class Paging
{
    public const int MaxPageSize = 100;

    // Allowlist: the client picks a name, never an expression.
    private static readonly Dictionary<string, Func<IQueryable<TaskEntity>, IOrderedQueryable<TaskEntity>>> Sorts = new()
    {
        ["created"] = q => q.OrderByDescending(t => t.CreatedAt).ThenByDescending(t => t.Id),
        ["due"]     = q => q.OrderBy(t => t.DueDate).ThenBy(t => t.Id),
    };

    // Cursor = "createdAtTicks:id", base64 encoded. Clients treat it as an opaque string.
    static string Encode(TaskEntity t) =>
        Convert.ToBase64String(Encoding.UTF8.GetBytes($"{t.CreatedAt.UtcTicks}:{t.Id}"));

    static bool TryDecode(string? cursor, out long ticks, out Guid id)
    {
        ticks = 0; id = default;
        if (cursor is null) return false;
        try
        {
            var parts = Encoding.UTF8.GetString(Convert.FromBase64String(cursor)).Split(':');
            return parts.Length == 2 && long.TryParse(parts[0], out ticks) && Guid.TryParse(parts[1], out id);
        }
        catch (FormatException) { return false; }
    }

    public static IResult List(IQueryable<TaskEntity> visibleToCaller, PageQuery q)
    {
        if (!Sorts.TryGetValue(q.Sort, out var order))
            return Results.Problem($"Unsupported sort '{q.Sort}'.", statusCode: 400);

        var size = Math.Clamp(q.PageSize, 1, MaxPageSize);      // enforce the cap
        var query = visibleToCaller;                              // authorization already applied

        if (q.Sort == "created" && TryDecode(q.Cursor, out var ticks, out var lastId))
        {
            var at = new DateTimeOffset(ticks, TimeSpan.Zero);
            query = query.Where(t => t.CreatedAt < at || (t.CreatedAt == at && t.Id.CompareTo(lastId) < 0));
        }

        var rows = order(query).Take(size + 1).ToList();          // fetch one extra to know if more exist
        var hasMore = rows.Count > size;
        var items = rows.Take(size).ToList();
        return Results.Ok(new Page<TaskEntity>(items, hasMore ? Encode(items[^1]) : null));
    }
}

The single extra row fetched beyond the page size tells you whether a next page exists without a separate count query. A malformed cursor is treated as no cursor rather than an error, and because the cursor carries only values already visible to the caller, decoding it reveals nothing. If cursors ever carry anything sensitive, sign them with the Data Protection API so they cannot be forged.

Offset paging is simple but slows down on deep pages and shifts when rows change. Keyset paging seeks from the last seen key, with a hard maximum page size and an allowlisted sort.
Offset paging is simple but slows down on deep pages and shifts when rows change. Keyset paging seeks from the last seen key, with a hard maximum page size and an allowlisted sort.

Real-World Examples

  • GitHub's REST API documents a default page size of 30 and a maximum of 100 items per page for most list endpoints, and returns a Link header with next and last URLs. Enforcing a hard maximum server-side, whatever the client asks for, is the standard way public APIs protect themselves from unbounded requests.
  • Slack migrated its Web API list methods to cursor-based pagination, returning a next_cursor value in response metadata, because offset pagination became unreliable and expensive for large workspaces where data changed constantly. Its documentation recommends cursors for any new client, which reflects the pattern for feed-like data.
Key TakeawaysEvery collection endpoint needs a default page size and a hard maximum, so one request can never ask the database for an unlimited number of rows. Use offset paging for small datasets with page numbers and keyset paging for large or fast-changing ones, accept sort fields through an allowlist of known expressions, apply authorization before filtering, and return a cursor or count that the React client can use directly.

Review Questions & Answers

The database must still read and discard all skipped rows to reach the requested offset, so deep pages cost more and more. And because the offset counts positions rather than identities, an insert or delete between two requests shifts every later row, so the client sees duplicates or misses records. Keyset pagination avoids both by seeking from the last seen key.

Because clients can send anything. Without a server-side ceiling, a caller can request a huge page, forcing the server to query, allocate, serialize, and transmit a large result, which is an inexpensive way to exhaust resources. Clamping or rejecting excessive sizes bounds the cost of any single request.

Passing client-provided names into dynamic queries risks injection, exposure of sensitive columns as sort keys, and sorts on unindexed columns that make queries slow. An allowlist maps friendly names to known, indexed expressions, so only safe and efficient orderings are possible and anything else is rejected with a clear error.

It must be deterministic and unique in combination, usually a column such as a timestamp plus the primary key as a tie-breaker. Without a unique tie-breaker, rows with equal sort values can appear in inconsistent order or be skipped across pages. The key should also be indexed so the seek is fast.