Learnixo
Back to blog
Backend Systemsintermediate

Advanced Filtering, Sorting & Searching — Build a Query Engine for Your API

Design a reusable, composable query engine for ASP.NET Core REST APIs — multi-field filtering, dynamic sorting, full-text search, cursor-based pagination, and how to translate it all to efficient EF Core SQL.

LearnixoApril 15, 20267 min read
.NETC#RESTASP.NET CoreEF CoreFilteringPaginationLINQPerformance
Share:𝕏

Every list endpoint needs filtering, sorting, and pagination. Most implementations hardcode them per-endpoint and duplicate the same boilerplate 20 times. This lesson builds a reusable query engine that handles all three generically, translates to efficient SQL, and stays maintainable.

The Standard Query Parameters

Settle on a consistent convention before writing any code:

GET /api/products?
  category=Electronics           ← field filter
  &minPrice=100&maxPrice=500      ← range filter
  &search=widget                  ← full-text search
  &sortBy=price&sortDir=asc       ← sort field + direction
  &page=2&pageSize=20             ← offset pagination
  &after=cursor_value             ← cursor pagination (alternative)
  &fields=id,name,price           ← field selection (sparse fieldsets)

Pick a convention and enforce it everywhere. Clients hate inconsistency.

The Query Object

Bind everything from the query string into a single object:

C#
public record ProductQuery(
    string?  Category  = null,
    decimal? MinPrice  = null,
    decimal? MaxPrice  = null,
    bool?    InStock   = null,
    string?  Search    = null,
    string   SortBy    = "createdAt",
    string   SortDir   = "desc",
    int      Page      = 1,
    int      PageSize  = 20
)
{
    // Hard cap — never let a client request more than 100 rows
    public int ClampedPageSize => Math.Min(PageSize, 100);
    public int Skip => (Page - 1) * ClampedPageSize;
}
C#
[HttpGet]
public async Task<ActionResult<PagedResult<ProductDto>>> GetAll(
    [FromQuery] ProductQuery query, CancellationToken ct)
{
    // ...
}

ASP.NET Core binds all query parameters automatically to the record's properties.

Building the Query — Composable IQueryable

Apply each filter only when it's present. Don't generate a WHERE clause for null values:

C#
private IQueryable<Product> ApplyFilters(IQueryable<Product> query, ProductQuery q)
{
    if (!string.IsNullOrWhiteSpace(q.Category))
        query = query.Where(p => p.Category == q.Category);

    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 => q.InStock.Value ? p.Stock > 0 : p.Stock == 0);

    if (!string.IsNullOrWhiteSpace(q.Search))
        query = query.Where(p =>
            p.Name.Contains(q.Search) ||
            p.Description.Contains(q.Search));

    return query;
}

Each .Where() call adds an AND condition. The query isn't executed until .ToListAsync().

Dynamic Sorting

Mapping user-supplied sort fields to IQueryable expressions:

C#
private IQueryable<Product> ApplySort(IQueryable<Product> query, ProductQuery q)
{
    var ascending = q.SortDir.Equals("asc", StringComparison.OrdinalIgnoreCase);

    return q.SortBy.ToLower() switch
    {
        "name"      => ascending ? query.OrderBy(p => p.Name)       : query.OrderByDescending(p => p.Name),
        "price"     => ascending ? query.OrderBy(p => p.Price)      : query.OrderByDescending(p => p.Price),
        "stock"     => ascending ? query.OrderBy(p => p.Stock)      : query.OrderByDescending(p => p.Stock),
        "createdat" => ascending ? query.OrderBy(p => p.CreatedAt)  : query.OrderByDescending(p => p.CreatedAt),
        _           => query.OrderByDescending(p => p.CreatedAt),    // default
    };
}

Whitelist allowed sort fields — never pass user input directly to a dynamic LINQ library without validation. A client could attempt to sort by a navigation property and cause an unexpected JOIN or error.

The Full Endpoint

C#
[HttpGet]
public async Task<ActionResult<PagedResult<ProductDto>>> GetAll(
    [FromQuery] ProductQuery query, CancellationToken ct)
{
    var q = _db.Products.AsNoTracking();

    q = ApplyFilters(q, query);

    // Count BEFORE pagination (for totalCount)
    var total = await q.CountAsync(ct);

    q = ApplySort(q, query);

    var items = await q
        .Skip(query.Skip)
        .Take(query.ClampedPageSize)
        .Select(p => new ProductDto(p.Id, p.Name, p.Price, p.Category, p.Stock))
        .ToListAsync(ct);

    return Ok(new PagedResult<ProductDto>(
        Items:      items,
        Page:       query.Page,
        PageSize:   query.ClampedPageSize,
        TotalCount: total,
        TotalPages: (int)Math.Ceiling((double)total / query.ClampedPageSize)
    ));
}

Generated SQL (for ?category=Electronics&minPrice=100&sortBy=price&sortDir=asc&page=2&pageSize=20):

SQL
-- Count query
SELECT COUNT(*) FROM products
WHERE category = 'Electronics' AND price >= 100

-- Data query
SELECT id, name, price, category, stock
FROM products
WHERE category = 'Electronics' AND price >= 100
ORDER BY price ASC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

Both queries are generated from the same IQueryable — no duplication.

Full-Text Search (PostgreSQL)

Contains() translates to LIKE '%term%' — it can't use an index. For real full-text search, use PostgreSQL's tsvector:

SQL
-- Add a generated tsvector column to the table
ALTER TABLE products
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', name || ' ' || coalesce(description, ''))
  ) STORED;

CREATE INDEX idx_products_search ON products USING GIN (search_vector);

EF Core raw SQL for the search filter:

C#
if (!string.IsNullOrWhiteSpace(q.Search))
{
    // Use EF.Functions.ToTsQuery for parameterised full-text search
    query = query.Where(p =>
        EF.Functions.ToTsVector("english", p.Name + " " + p.Description)
            .Matches(EF.Functions.PlainToTsQuery("english", q.Search)));
}

This generates:

SQL
WHERE to_tsvector('english', name || ' ' || description) @@ plainto_tsquery('english', 'widget')

Full-text search uses the GIN index — fast even on millions of rows.

Generic Query Engine (Reusable Across Entities)

For large APIs with many list endpoints, extract a reusable specification pattern:

C#
public abstract class QuerySpecification<T>
{
    public Expression<Func<T, bool>>? Filter  { get; protected set; }
    public Expression<Func<T, object>>? Sort  { get; protected set; }
    public bool SortDescending               { get; protected set; }
    public int Skip                          { get; protected set; }
    public int Take                          { get; protected set; } = 20;
}

public class ProductSpecification : QuerySpecification<Product>
{
    public ProductSpecification(ProductQuery q)
    {
        // Build filter expression
        var param = Expression.Parameter(typeof(Product), "p");
        Expression? body = null;

        if (!string.IsNullOrWhiteSpace(q.Category))
        {
            var catFilter = Expression.Equal(
                Expression.Property(param, nameof(Product.Category)),
                Expression.Constant(q.Category));
            body = body is null ? catFilter : Expression.AndAlso(body, catFilter);
        }

        if (q.MinPrice.HasValue)
        {
            var minFilter = Expression.GreaterThanOrEqual(
                Expression.Property(param, nameof(Product.Price)),
                Expression.Constant(q.MinPrice.Value));
            body = body is null ? minFilter : Expression.AndAlso(body, minFilter);
        }

        Filter = body is null ? null : Expression.Lambda<Func<Product, bool>>(body, param);
        Skip   = (q.Page - 1) * q.ClampedPageSize;
        Take   = q.ClampedPageSize;
    }
}

And the generic repository method:

C#
public async Task<(IReadOnlyList<T> Items, int Total)> GetBySpecAsync(
    QuerySpecification<T> spec, CancellationToken ct = default)
{
    IQueryable<T> query = _set.AsNoTracking();

    if (spec.Filter is not null)
        query = query.Where(spec.Filter);

    var total = await query.CountAsync(ct);

    if (spec.Sort is not null)
        query = spec.SortDescending
            ? query.OrderByDescending(spec.Sort)
            : query.OrderBy(spec.Sort);

    var items = await query.Skip(spec.Skip).Take(spec.Take).ToListAsync(ct);
    return (items, total);
}

Cursor-Based Pagination

Offset pagination (OFFSET 1000) gets slow on large datasets — the database still reads 1000 rows to discard them. Cursor-based pagination uses a stable "last seen" value instead:

HTTP
# First page
GET /api/products?pageSize=20

# Response includes cursor for next page
{
  "items": [...],
  "nextCursor": "eyJpZCI6MjB9"  ← base64({"id":20,"createdAt":"..."})
}

# Next page — no OFFSET needed
GET /api/products?pageSize=20&after=eyJpZCI6MjB9
C#
[HttpGet]
public async Task<ActionResult<CursorPagedResult<ProductDto>>> GetAll(
    [FromQuery] string? after, int pageSize = 20, CancellationToken ct = default)
{
    var query = _db.Products.AsNoTracking().OrderBy(p => p.Id);

    if (after is not null)
    {
        var cursor = DecodeCursor(after);  // { id: 20 }
        query = (IOrderedQueryable<Product>)query.Where(p => p.Id > cursor.Id);
    }

    var items = await query
        .Take(pageSize + 1)  // fetch one extra to know if there's a next page
        .Select(p => new ProductDto(p.Id, p.Name, p.Price, p.Category))
        .ToListAsync(ct);

    var hasNext = items.Count > pageSize;
    if (hasNext) items.RemoveAt(items.Count - 1);  // remove the extra item

    var nextCursor = hasNext ? EncodeCursor(items.Last().Id) : null;

    return Ok(new CursorPagedResult<ProductDto>(items, nextCursor));
}

Use cursor pagination when:

  • The dataset is large (>100k rows) and performance matters
  • You need real-time feeds where data changes between pages (avoid "missing" or "duplicate" items)

Use offset pagination when:

  • Users need to jump to a specific page ("go to page 5")
  • Total count is important to display ("87 results, page 2 of 5")

Sparse Fieldsets (Field Selection)

Let clients request only the fields they need — reduces payload size significantly for large objects:

HTTP
GET /api/products?fields=id,name,price
C#
[HttpGet]
public async Task<IActionResult> GetAll(
    [FromQuery] ProductQuery query,
    [FromQuery] string? fields,
    CancellationToken ct)
{
    var items = await _db.Products.AsNoTracking()
        /* filters + sort */
        .Select(p => new ProductDto(p.Id, p.Name, p.Price, p.Category, p.Stock))
        .ToListAsync(ct);

    if (!string.IsNullOrWhiteSpace(fields))
    {
        var requested = fields.Split(',').Select(f => f.Trim().ToLower()).ToHashSet();
        return Ok(items.Select(item => FilterFields(item, requested)));
    }

    return Ok(items);
}

private static Dictionary<string, object?> FilterFields(ProductDto dto, HashSet<string> fields)
{
    return typeof(ProductDto)
        .GetProperties()
        .Where(p => fields.Contains(p.Name.ToLower()))
        .ToDictionary(p => p.Name, p => p.GetValue(dto));
}

Quick Reference

Filter:           ?category=X&minPrice=100&maxPrice=500&inStock=true
Search:           ?search=term → LIKE (simple) or tsvector (performant)
Sort:             ?sortBy=price&sortDir=asc
Offset page:      ?page=2&pageSize=20 → OFFSET 20 LIMIT 20
Cursor page:      ?after=cursor&pageSize=20 → WHERE id > lastId LIMIT 20
Sparse fields:    ?fields=id,name,price
Hard cap:         Math.Min(pageSize, 100) — never let client request unlimited rows
Dynamic sort:     switch on sortBy → OrderBy(expression) — whitelist fields!

Enjoyed this article?

Explore the Backend Systems learning path for more.

Subscribe for weekly tutorials

Found this helpful?

Share:𝕏

Leave a comment

Have a question, correction, or just found this helpful? Leave a note below.