Pagination, Filtering and Sorting

The endpoint that will fall over

[HttpGet]
public async Task<ActionResult<List<Book>>> GetAll() => await db.Books.ToListAsync();

This is fine with fifty books. At half a million it loads every row into memory, serializes megabytes of JSON, and takes the API down.

Every collection endpoint needs paging from the first day. Adding it later is a breaking change.

Offset pagination

The familiar approach. Skip a number of rows, take the next batch:

var items = await db.Books
    .OrderBy(b => b.Title)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

GET /api/books?page=2&pageSize=20

Simple, and it lets a client jump to any page. Two limits, one small and one large.

The client controls the size. Clamp it, or someone asks for a million:

pageSize = Math.Clamp(pageSize, 1, 100);
page = Math.Max(page, 1);

OrderBy is not optional. Without it, SQL Server does not guarantee a stable order, so page 2 can repeat rows from page 1. If your sort field is not unique, add a tiebreaker:

.OrderBy(b => b.Title).ThenBy(b => b.Id)

Shaping the response

The client needs the total to draw page numbers. Two ways to send it.

An envelope, which is the most common:

public class PagedResult<T>
{
    public IReadOnlyList<T> Items { get; init; } = Array.Empty<T>();
    public int Page { get; init; }
    public int PageSize { get; init; }
    public int TotalCount { get; init; }

    public int TotalPages => (int)Math.Ceiling(TotalCount / (double)PageSize);
    public bool HasPrevious => Page > 1;
    public bool HasNext => Page < TotalPages;
}

{
  "items": [ { "id": 1, "title": "Clean Code" } ],
  "page": 2,
  "pageSize": 20,
  "totalCount": 1204,
  "totalPages": 61,
  "hasPrevious": true,
  "hasNext": true
}

Headers, keeping the body a clean array:

Response.Headers["X-Total-Count"] = total.ToString();
Response.Headers["X-Page"] = page.ToString();

Remember WithExposedHeaders in your CORS policy, or the browser hides them.

Either works. The envelope is easier for clients and is what most APIs do. Pick one and use it for every collection.

Two queries, one round trip each

You need the page and the total:

IQueryable<Book> query = db.Books.Where(b => b.Year > 2000);

int total = await query.CountAsync(ct);              // SELECT COUNT(*)

List<BookListItem> items = await query
    .OrderBy(b => b.Title).ThenBy(b => b.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .Select(b => new BookListItem(b.Id, b.Title, b.Price))
    .ToListAsync(ct);

Note the filter is applied before the count, so the total reflects the filter. Getting that backwards gives clients wrong page numbers.

CountAsync on a very large table is itself slow. If it hurts, either cache an approximate total or switch to cursor pagination.

The deep page problem

Skip(100000) makes the database read and discard a hundred thousand rows. Page 1 is instant, page 5000 is not.

There is also a correctness problem. If a row is inserted while a user pages through, rows shift and one gets skipped or repeated.

For an admin screen where nobody goes past page 10, offset paging is fine. For an infinite scroll feed, use a cursor.

Cursor pagination

Instead of “skip 100000 rows”, say “give me rows after this one”:

IQueryable<Book> query = db.Books.OrderBy(b => b.Id);

if (cursor is not null)
{
    int afterId = DecodeCursor(cursor);
    query = query.Where(b => b.Id > afterId);        // an index seek, not a scan
}

List<Book> items = await query.Take(pageSize + 1).ToListAsync(ct);

bool hasMore = items.Count > pageSize;
if (hasMore) items.RemoveAt(items.Count - 1);

string? nextCursor = hasMore ? EncodeCursor(items[^1].Id) : null;

{
  "items": [ ... ],
  "nextCursor": "eyJpZCI6MTIzfQ==",
  "hasMore": true
}

Taking pageSize + 1 is the trick for knowing whether more exists without a second query.

Cursor paging is constant time at any depth and stable when rows are inserted. The cost is that you cannot jump to page 50, only forward and back. For feeds and infinite scroll, that is exactly the right trade.

Encode the cursor as an opaque string so clients treat it as a token and you can change its contents later:

static string EncodeCursor(int id) =>
    Convert.ToBase64String(Encoding.UTF8.GetBytes($"{{\"id\":{id}}}"));

Filtering

Compose conditions onto the query. Nothing runs until you materialise it:

IQueryable<Book> query = db.Books.AsNoTracking();

if (!string.IsNullOrWhiteSpace(filter.Title))
    query = query.Where(b => b.Title.Contains(filter.Title));

if (filter.MinYear is not null)
    query = query.Where(b => b.Year >= filter.MinYear);

if (filter.MaxPrice is not null)
    query = query.Where(b => b.Price <= filter.MaxPrice);

if (filter.AuthorId is not null)
    query = query.Where(b => b.AuthorId == filter.AuthorId);

if (filter.Tags?.Length > 0)
    query = query.Where(b => b.Tags.Any(t => filter.Tags.Contains(t.Name)));

Group the parameters into a class rather than listing eight arguments:

public class BookFilter
{
    public string? Title { get; set; }
    public int? MinYear { get; set; }
    public int? MaxPrice { get; set; }
    public int? AuthorId { get; set; }
    public string[]? Tags { get; set; }
}

[HttpGet]
public async Task<ActionResult<PagedResult<BookListItem>>> Search(
    [FromQuery] BookFilter filter, [FromQuery] PageQuery paging, CancellationToken ct)

A note on case insensitive search. Contains maps to SQL LIKE, which is case insensitive under most default collations. Do not write b.Title.ToLower().Contains(term.ToLower()), because that stops the database using an index. If you need explicit control, set the column collation instead.

Sorting, safely

A column name cannot be a SQL parameter, so a client controlled sort field is an injection risk with raw SQL and a runtime error with LINQ. Use an allow list:

private static readonly Dictionary<string, Expression<Func<Book, object>>> SortMap =
    new(StringComparer.OrdinalIgnoreCase)
    {
        ["title"] = b => b.Title,
        ["year"] = b => b.Year,
        ["price"] = b => b.Price,
        ["created"] = b => b.CreatedAt
    };

private static IQueryable<Book> ApplySort(IQueryable<Book> query, string? sort)
{
    // "price:desc" or just "price"
    string[] parts = (sort ?? "title").Split(':');
    string field = parts[0];
    bool descending = parts.Length > 1 && parts[1].Equals("desc", StringComparison.OrdinalIgnoreCase);

    if (!SortMap.TryGetValue(field, out var selector))
        selector = SortMap["title"];                 // safe default

    query = descending ? query.OrderByDescending(selector) : query.OrderBy(selector);

    return query.ThenBy(b => b.Id);                  // stable tiebreaker
}

An unknown field falls back to a default rather than throwing. That is friendlier than a 400 and avoids leaking which columns exist.

Making it reusable

Once you have written this twice, extract it:

public static class QueryableExtensions
{
    public static async Task<PagedResult<TDto>> ToPagedResultAsync<TEntity, TDto>(
        this IQueryable<TEntity> query,
        Expression<Func<TEntity, TDto>> selector,
        int page,
        int pageSize,
        CancellationToken ct = default)
    {
        page = Math.Max(page, 1);
        pageSize = Math.Clamp(pageSize, 1, 100);

        int total = await query.CountAsync(ct);

        List<TDto> items = await query
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(selector)
            .ToListAsync(ct);

        return new PagedResult<TDto>
        {
            Items = items,
            Page = page,
            PageSize = pageSize,
            TotalCount = total
        };
    }
}

return await query
    .OrderBy(b => b.Title).ThenBy(b => b.Id)
    .ToPagedResultAsync(b => new BookListItem(b.Id, b.Title, b.Price), page, size, ct);

Every collection endpoint now behaves identically, and the clamping cannot be forgotten.

Indexes

Paging and filtering are only fast with the right indexes. Index the columns you filter and sort on:

builder.HasIndex(b => b.Title);
builder.HasIndex(b => new { b.AuthorId, b.Year });
builder.HasIndex(b => b.CreatedAt);

A composite index helps when the leading columns match your query. (AuthorId, Year) serves a filter on AuthorId alone, and one on both, but not Year alone.

Check what actually runs by logging the SQL, and read the query plan when something is slow. Guessing at indexes wastes time.

Quick recap

• Never return an unbounded collection. Page from the first version.
• Clamp the page size server side. The client does not decide how much work you do.
• Always OrderBy with a unique tiebreaker, or pages overlap.
• Return an envelope with items and a total, or send the total in headers, and be consistent.
• Apply filters before counting, so the total matches the filter.
• Deep Skip is slow and unstable. Use cursor pagination for feeds and infinite scroll.
• Take pageSize + 1 rows to detect more without a second query.
• Compose filters onto IQueryable. Nothing runs until you materialise it.
• Avoid ToLower() in a filter, because it defeats the index.
• Sort fields come from an allow list, with a safe default for unknown values.
• Index whatever you filter and sort on.