2026-04-05

Why pagination and filtering matter

  • Filtering trims the dataset to only what the client needs.
  • Pagination breaks results into pages to keep responses fast and small.
  • Sorting ensures stable, predictable results.

In this post, you’ll build a real ASP.NET Core + EF Core endpoint that supports filtering, sorting, and offset pagination. You’ll also see how to add keyset (cursor) pagination for very large datasets.

Project setup (minimal API/controller)

You can use any DB provider you like; this example uses EF Core and a simple controller.

# Create a Web API project
dotnet new webapi -n CatalogApi
cd CatalogApi

# Add EF Core + provider (choose one)
# For SQLite (simple local dev)
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

# Or for SQL Server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

# Add design tools if you’ll use migrations
dotnet add package Microsoft.EntityFrameworkCore.Design

Program.cs (minimal wiring):

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddDbContext<AppDbContext>(opt =>
{
    // SQLite for example; replace with your provider
    opt.UseSqlite("Data Source=app.db");
});

var app = builder.Build();

app.MapControllers();

app.Run();

Data model and DbContext

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
    public decimal Price { get; set; }
    public bool InStock { get; set; }
    public string Category { get; set; } = default!;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}

    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Helpful composite index for common queries
        modelBuilder.Entity<Product>()
            .HasIndex(p => new { p.Category, p.Price, p.CreatedAt });
    }
}

Optional seeding (at startup) to have data to test:

public static class SeedData
{
    public static async Task InitializeAsync(AppDbContext db)
    {
        await db.Database.EnsureCreatedAsync();
        if (await db.Products.AnyAsync()) return;

        var rng = new Random(42);
        var categories = new[] { "Books", "Electronics", "Toys", "Home" };
        var now = DateTime.UtcNow;

        var items = Enumerable.Range(1, 1000).Select(i => new Product
        {
            Name = $"Item {i}",
            Price = Math.Round((decimal)rng.NextDouble() * 200m, 2),
            InStock = rng.Next(0, 2) == 1,
            Category = categories[rng.Next(categories.Length)],
            CreatedAt = now.AddMinutes(-i)
        }).ToList();

        await db.Products.AddRangeAsync(items);
        await db.SaveChangesAsync();
    }
}

Call seeding from Program.cs right after building the app:

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await SeedData.InitializeAsync(db);
}

Designing the query contract

Expose only the knobs you want clients to use. Keep defaults and caps to protect your API.

public class ProductQuery
{
    private const int MaxPageSize = 100;

    public int PageNumber { get; set; } = 1; // 1-based

    private int pageSize = 20;
    public int PageSize
    {
        get => pageSize;
        set => pageSize = Math.Min(MaxPageSize, Math.Max(1, value));
    }

    public string? NameLike { get; set; } // partial name match
    public decimal? MinPrice { get; set; }
    public decimal? MaxPrice { get; set; }
    public bool? InStock { get; set; }
    public string? Category { get; set; }

    // Whitelisted sort fields
    public string SortBy { get; set; } = "CreatedAt"; // Name | Price | CreatedAt
    public string SortDir { get; set; } = "desc";     // asc | desc
}

A robust paginated response type:

public record PagedResult<T>(IReadOnlyList<T> Items, int TotalCount, int PageNumber, int PageSize)
{
    public int TotalPages => (int)Math.Ceiling(TotalCount / (double)PageSize);
    public bool HasPrevious => PageNumber > 1;
    public bool HasNext => PageNumber < TotalPages;
}

public record ProductDto(int Id, string Name, decimal Price, bool InStock, string Category, DateTime CreatedAt);

Building the endpoint (filtering + sorting + offset pagination)

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly AppDbContext _db;
    public ProductsController(AppDbContext db) => _db = db;

    [HttpGet]
    public async Task<ActionResult<PagedResult<ProductDto>>> Get([FromQuery] ProductQuery q, CancellationToken ct)
    {
        // Start with IQueryable for server-side composition
        IQueryable<Product> query = _db.Products.AsNoTracking();

        // Filtering
        if (!string.IsNullOrWhiteSpace(q.NameLike))
        {
            var term = q.NameLike.Trim().ToLower();
            query = query.Where(p => p.Name.ToLower().Contains(term));
            // For SQL Server: use EF.Functions.Like(p.Name, $"%{term}%") with ToLower() if needed
        }
        if (q.MinPrice.HasValue) query = query.Where(p => p.Price >= q.MinPrice.Value);
        if (q.MaxPrice.HasValue) query = query.Where(p => p.Price <= q.MaxPrice.Value);
        if (q.InStock.HasValue) query = query.Where(p => p.InStock == q.InStock.Value);
        if (!string.IsNullOrWhiteSpace(q.Category)) query = query.Where(p => p.Category == q.Category);

        // Sorting (whitelist fields only)
        var asc = q.SortDir.Equals("asc", StringComparison.OrdinalIgnoreCase);
        query = (q.SortBy.ToLowerInvariant()) switch
        {
            "name"      => asc ? query.OrderBy(p => p.Name).ThenBy(p => p.Id)
                                : query.OrderByDescending(p => p.Name).ThenByDescending(p => p.Id),
            "price"     => asc ? query.OrderBy(p => p.Price).ThenBy(p => p.Id)
                                : query.OrderByDescending(p => p.Price).ThenByDescending(p => p.Id),
            "createdat" => asc ? query.OrderBy(p => p.CreatedAt).ThenBy(p => p.Id)
                                : query.OrderByDescending(p => p.CreatedAt).ThenByDescending(p => p.Id),
            _            => query.OrderByDescending(p => p.CreatedAt).ThenByDescending(p => p.Id) // default
        };

        // Total count BEFORE paging (can be expensive on very large tables)
        var total = await query.CountAsync(ct);

        // Pagination (offset)
        var skip = (q.PageNumber - 1) * q.PageSize;
        var items = await query
            .Skip(skip)
            .Take(q.PageSize)
            .Select(p => new ProductDto(p.Id, p.Name, p.Price, p.InStock, p.Category, p.CreatedAt))
            .ToListAsync(ct);

        var result = new PagedResult<ProductDto>(items, total, q.PageNumber, q.PageSize);
        return Ok(result);
    }
}

Example requests:

  • GET /api/products?PageNumber=1&PageSize=10
  • GET /api/products?NameLike=book&MinPrice=10&MaxPrice=50&InStock=true
  • GET /api/products?Category=Electronics&SortBy=Price&SortDir=asc&PageNumber=2&PageSize=25

Example JSON response (truncated):

{
  "items": [
    { "id": 42, "name": "Item 42", "price": 19.99, "inStock": true, "category": "Books", "createdAt": "2023-12-01T12:34:56Z" }
  ],
  "totalCount": 1000,
  "pageNumber": 1,
  "pageSize": 20,
  "totalPages": 50,
  "hasPrevious": false,
  "hasNext": true
}

When offset pagination hurts: use keyset (cursor) pagination

Offset pagination (Skip/Take) can be slow on huge tables because the DB must scan and discard many rows. Keyset pagination uses the last item’s sort keys as a cursor and is far faster and more stable for infinite scrolling.

We’ll paginate by newest first: ORDER BY CreatedAt DESC, Id DESC. The cursor will be the last item’s (CreatedAt, Id).

Query model:

public class ProductKeysetQuery
{
    private const int MaxLimit = 100;
    private int limit = 20;
    public int Limit
    {
        get => limit;
        set => limit = Math.Min(MaxLimit, Math.Max(1, value));
    }

    // Cursor from previous page (encoded as ISO-8601 time + Id)
    public DateTime? AfterCreatedAt { get; set; }
    public int? AfterId { get; set; }

    // Optional filters (reuse a subset)
    public string? Category { get; set; }
    public bool? InStock { get; set; }
}

Endpoint:

[HttpGet("keyset")]
public async Task<ActionResult<object>> GetKeyset([FromQuery] ProductKeysetQuery q, CancellationToken ct)
{
    IQueryable<Product> query = _db.Products.AsNoTracking();

    if (!string.IsNullOrWhiteSpace(q.Category)) query = query.Where(p => p.Category == q.Category);
    if (q.InStock.HasValue) query = query.Where(p => p.InStock == q.InStock.Value);

    // Stable ordering (DESC)
    query = query.OrderByDescending(p => p.CreatedAt).ThenByDescending(p => p.Id);

    if (q.AfterCreatedAt.HasValue && q.AfterId.HasValue)
    {
        var a = q.AfterCreatedAt.Value;
        var id = q.AfterId.Value;
        // Rows strictly after the cursor (for DESC ordering)
        query = query.Where(p => p.CreatedAt < a || (p.CreatedAt == a && p.Id < id));
    }

    var items = await query
        .Take(q.Limit)
        .Select(p => new ProductDto(p.Id, p.Name, p.Price, p.InStock, p.Category, p.CreatedAt))
        .ToListAsync(ct);

    // Build next cursor
    string? nextCursor = null;
    if (items.Count > 0)
    {
        var last = items[^1];
        nextCursor = $"{last.CreatedAt:o}|{last.Id}"; // You can Base64 if you prefer
    }

    return Ok(new
    {
        items,
        nextCursor
    });
}

Example requests:

  • First page: GET /api/products/keyset?Limit=20
  • Next page (use response’s nextCursor split by "|"): GET /api/products/keyset?Limit=20&AfterCreatedAt=2023-12-01T12:34:56.0000000Z&AfterId=42

Notes:

  • Keyset pagination doesn’t give you totalCount easily. It’s for fast forward-only navigation.
  • Always keep ordering stable and unique (tie-breaker by Id).

Practical tips and pitfalls

  • Cap PageSize/Limit to protect the DB (e.g., 100 max).
  • Always AsNoTracking for read-only queries.
  • Whitelist SortBy fields; never build dynamic OrderBy from raw strings without validation.
  • Ensure a stable, unique ordering (add ThenBy(Id)) to avoid duplicates/missing rows between pages.
  • Consider projecting to lightweight DTOs to reduce payload and avoid N+1.
  • For case-insensitive search portable across providers, normalize both sides (ToLower) or use provider-specific functions.
  • Large offsets are slow. Prefer keyset pagination for endless scroll or very large data.
  • Counting is expensive on huge tables. If you only need “next page?” semantics, you can fetch PageSize + 1 items and infer hasNext without doing Count().

Example: infer hasNext without Count:

var pagePlusOne = await query.Skip(skip).Take(q.PageSize + 1).ToListAsync(ct);
var hasNext = pagePlusOne.Count > q.PageSize;
var items = pagePlusOne.Take(q.PageSize).ToList();

Summary

You built two production-ready patterns:

  • Offset pagination with filtering/sorting and a rich PagedResult response.
  • Keyset pagination for fast, scalable, infinite scrolling.

Start with offset pagination for admin/report screens. Reach for keyset pagination when performance degrades at high offsets or when implementing infinite scroll. Both approaches benefit from careful filtering, stable sorting, and DTO projection.