Implement a product search whose category, minimum price, maximum price, and in-stock filters are all optional.

Start from Products and return ProductSearchItem(Id, Name, Price, AvailableQuantity). Filter values are nullable, InStockOnly is boolean, and the request has a validated maximum of 100 results. Do not call AsEnumerable, ToList, or a custom CLR method before all database work is expressed.

With category 4, minimum price 20, no maximum, and InStockOnly = true, only category-4 products priced at least 20 with positive available quantity are returned.

• Reject a minimum price greater than the maximum before querying.
• Apply only filters that have values.
• Order by name and id for deterministic output.
• Project before materialization.

I keep an IQueryable<Product> and add one predicate for each supplied filter. This reads like normal branching in C#, but EF sees one final expression tree and translates it as one SQL query. Validation stays outside the query, while all data filtering remains server-side.

public static async Task<List<ProductSearchItem>> SearchProductsAsync(
    AppDbContext db,
    ProductSearch request,
    CancellationToken cancellationToken)
{
    if (request.MinPrice is not null &&
        request.MaxPrice is not null &&
        request.MinPrice > request.MaxPrice)
    {
        throw new ArgumentException("Minimum price cannot exceed maximum price.");
    }

    IQueryable<Product> query = db.Products.AsNoTracking();

    if (request.CategoryId is not null)
        query = query.Where(product => product.CategoryId == request.CategoryId);

    if (request.MinPrice is not null)
        query = query.Where(product => product.Price >= request.MinPrice);

    if (request.MaxPrice is not null)
        query = query.Where(product => product.Price <= request.MaxPrice);

    if (request.InStockOnly)
        query = query.Where(product => product.AvailableQuantity > 0);

    return await query
        .OrderBy(product => product.Name)
        .ThenBy(product => product.Id)
        .Select(product => new ProductSearchItem(
            product.Id,
            product.Name,
            product.Price,
            product.AvailableQuantity))
        .Take(Math.Clamp(request.Limit, 1, 100))
        .ToListAsync(cancellationToken);
}

The database receives one parameterized query containing only active predicates. Different filter combinations may produce different plans, so high-traffic search endpoints should be checked with representative combinations and data distribution rather than one convenient local case.

• No optional filters are supplied.
• Minimum and maximum prices are equal.
• Limit is zero or too large.
• A low-selectivity filter returns many candidates before Take.

• Inspect generated SQL for several filter combinations.
• Assert no materialization occurs before ordering and projection.
• Use a price boundary equal to a product's value.
• Verify the id tie-breaker for duplicate names.

• Calling ToListAsync and filtering the list in memory.
• Using custom CLR predicates that cannot translate.
• Building SQL strings from user-supplied values.
• Leaving the result limit unbounded.