2026-04-05

Why filtering matters

Filtering turns big data sets into the exact rows you need. In C#, the easiest way to filter is LINQ. For apps backed by a database (EF Core), the difference between filtering in-memory vs. in the database can make or break performance.

This post shows practical patterns you can paste into your code today: simple filters, optional filters, dynamic filters with expressions, case-insensitive search, date/number ranges, nested collections, and EF Core-friendly approaches.

IEnumerable vs IQueryable (know your target)

  • IEnumerable<T>: in-memory LINQ to Objects. All operations happen in your process.
  • IQueryable<T>: query provider (e.g., EF Core) translates your LINQ into SQL. Keep filters as IQueryable as long as possible to let the database do the heavy lifting.

Rule of thumb: Apply Where/Select/OrderBy before calling ToList/AsEnumerable.

Sample model

public sealed class Product
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public decimal Price { get; set; }
    public bool InStock { get; set; }
    public string Category { get; set; } = "";
    public DateTime CreatedUtc { get; set; }
}

Basics: simple filters with LINQ

var products = new List<Product>
{
    new() { Id = 1, Name = "Keyboard", Price = 49.99m, InStock = true, Category = "Peripherals" },
    new() { Id = 2, Name = "Monitor", Price = 199.99m, InStock = false, Category = "Displays" },
    new() { Id = 3, Name = "Mouse", Price = 24.99m, InStock = true, Category = "Peripherals" },
};

// Price and stock
var midPricedInStock = products
    .Where(p => p.Price >= 25m && p.Price <= 150m && p.InStock)
    .ToList();

// Membership
var allowedCategories = new[] { "Peripherals", "Laptops" };
var inAllowedCategories = products
    .Where(p => allowedCategories.Contains(p.Category))
    .ToList();

// Any/All on nested collections
public sealed class Order { public int Id { get; set; } public List<Product> Items { get; set; } = new(); }
var orders = new List<Order> { new() { Id = 10, Items = products } };
var ordersWithExpensiveItems = orders.Where(o => o.Items.Any(i => i.Price > 100m));

Null-safe and case-insensitive text filtering

  • In-memory (LINQ to Objects):
string? keyword = "key";
var match = products.Where(p =>
    p.Name is not null &&
    p.Name.Contains(keyword!, StringComparison.OrdinalIgnoreCase));

// Alternative (works on older targets):
var match2 = products.Where(p =>
    p.Name is not null &&
    p.Name.IndexOf(keyword!, StringComparison.OrdinalIgnoreCase) >= 0);
  • EF Core (server-side): avoid ToLower/ToUpper when you care about indexes.
    • SQL Server: default collation is often case-insensitive already.
    • PostgreSQL (Npgsql): use ILike for case-insensitive.
// SQL Server or provider-agnostic pattern-dependent on collation
var q1 = context.Products.Where(p => EF.Functions.Like(p.Name!, $"%{keyword}%"));

// SQL Server force CI collation if needed
var q2 = context.Products.Where(p => EF.Functions.Like(
    EF.Functions.Collate(p.Name!, "SQL_Latin1_General_CP1_CI_AS"), $"%{keyword}%"));

// PostgreSQL (Npgsql)
var q3 = context.Products.Where(p => EF.Functions.ILike(p.Name!, $"%{keyword}%"));

Date and numeric ranges

DateTime from = DateTime.UtcNow.AddDays(-30);
DateTime to   = DateTime.UtcNow;

var recent = context.Products.Where(p => p.CreatedUtc >= from && p.CreatedUtc < to);

// Inclusive bounds helper
bool InRange(decimal value, decimal? min, decimal? max) =>
    (!min.HasValue || value >= min) && (!max.HasValue || value <= max);

var inMemoryRange = products.Where(p => InRange(p.Price, 10m, 100m));

Optional filters (apply only when present)

public sealed class ProductFilter
{
    public string? Query { get; set; }
    public decimal? MinPrice { get; set; }
    public decimal? MaxPrice { get; set; }
    public bool? InStock { get; set; }
    public string? Category { get; set; }
}

IQueryable<Product> Apply(IQueryable<Product> q, ProductFilter f)
{
    if (!string.IsNullOrWhiteSpace(f.Query))
        q = q.Where(p => EF.Functions.Like(p.Name!, $"%{f.Query}%"));

    if (f.MinPrice.HasValue)
        q = q.Where(p => p.Price >= f.MinPrice.Value);

    if (f.MaxPrice.HasValue)
        q = q.Where(p => p.Price <= f.MaxPrice.Value);

    if (f.InStock.HasValue)
        q = q.Where(p => p.InStock == f.InStock.Value);

    if (!string.IsNullOrWhiteSpace(f.Category))
        q = q.Where(p => p.Category == f.Category);

    return q;
}

Composing dynamic predicates (EF Core-friendly)

When you need to build a single Expression<Func<T,bool>> predicate from parts (to pass once into Where, Count, etc.), avoid Expression.Invoke which EF often cannot translate. Use parameter replacement instead.

using System.Linq.Expressions;

public static class PredicateBuilder
{
    private sealed class ReplaceVisitor : ExpressionVisitor
    {
        private readonly ParameterExpression _from;
        private readonly ParameterExpression _to;
        public ReplaceVisitor(ParameterExpression from, ParameterExpression to)
        { _from = from; _to = to; }
        protected override Expression VisitParameter(ParameterExpression node)
            => node == _from ? _to : base.VisitParameter(node);
    }

    public static Expression<Func<T, bool>> True<T>() => x => true;
    public static Expression<Func<T, bool>> False<T>() => x => false;

    public static Expression<Func<T, bool>> AndAlso<T>(
        this Expression<Func<T, bool>> left,
        Expression<Func<T, bool>> right)
    {
        var param = Expression.Parameter(typeof(T), "x");
        var leftBody = new ReplaceVisitor(left.Parameters[0], param).Visit(left.Body)!;
        var rightBody = new ReplaceVisitor(right.Parameters[0], param).Visit(right.Body)!;
        return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(leftBody, rightBody), param);
    }

    public static Expression<Func<T, bool>> OrElse<T>(
        this Expression<Func<T, bool>> left,
        Expression<Func<T, bool>> right)
    {
        var param = Expression.Parameter(typeof(T), "x");
        var leftBody = new ReplaceVisitor(left.Parameters[0], param).Visit(left.Body)!;
        var rightBody = new ReplaceVisitor(right.Parameters[0], param).Visit(right.Body)!;
        return Expression.Lambda<Func<T, bool>>(Expression.OrElse(leftBody, rightBody), param);
    }
}

Use it to assemble optional filters into one expression:

Expression<Func<Product, bool>> predicate = PredicateBuilder.True<Product>();

if (!string.IsNullOrWhiteSpace(filter.Query))
    predicate = predicate.AndAlso(p => EF.Functions.Like(p.Name!, $"%{filter.Query}%"));

if (filter.MinPrice.HasValue)
    predicate = predicate.AndAlso(p => p.Price >= filter.MinPrice.Value);

if (filter.MaxPrice.HasValue)
    predicate = predicate.AndAlso(p => p.Price <= filter.MaxPrice.Value);

if (filter.InStock.HasValue)
    predicate = predicate.AndAlso(p => p.InStock == filter.InStock.Value);

var page = 1; var pageSize = 20;
var query = context.Products.AsNoTracking().Where(predicate);
var total = await query.CountAsync();
var items = await query
    .OrderBy(p => p.Price)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToListAsync();

Filtering nested collections

// Orders that ONLY contain in-stock items
var strict = context.Set<Order>().Where(o => o.Items.All(i => i.InStock));

// Orders that contain at least one product in a category
var anyCat = context.Set<Order>().Where(o => o.Items.Any(i => i.Category == "Peripherals"));

// Flatten to filter over all items
var items = context.Set<Order>().SelectMany(o => o.Items).Where(i => i.Price > 100m);

End-to-end example: API endpoint with dynamic filters + pagination

// Minimal API style (Program.cs)
app.MapGet("/products", async (
    [AsParameters] ProductFilter filter,
    int page = 1,
    int pageSize = 20,
    MyDbContext context) =>
{
    // Compose once
    Expression<Func<Product, bool>> predicate = PredicateBuilder.True<Product>();

    if (!string.IsNullOrWhiteSpace(filter.Query))
        predicate = predicate.AndAlso(p => EF.Functions.Like(p.Name!, $"%{filter.Query}%"));

    if (filter.MinPrice.HasValue)
        predicate = predicate.AndAlso(p => p.Price >= filter.MinPrice.Value);

    if (filter.MaxPrice.HasValue)
        predicate = predicate.AndAlso(p => p.Price <= filter.MaxPrice.Value);

    if (filter.InStock.HasValue)
        predicate = predicate.AndAlso(p => p.InStock == filter.InStock.Value);

    if (!string.IsNullOrWhiteSpace(filter.Category))
        predicate = predicate.AndAlso(p => p.Category == filter.Category);

    var baseQuery = context.Products.AsNoTracking().Where(predicate);

    var total = await baseQuery.CountAsync();

    var data = await baseQuery
        .OrderByDescending(p => p.CreatedUtc)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(p => new ProductDto(p.Id, p.Name!, p.Price, p.InStock))
        .ToListAsync();

    return Results.Ok(new { total, page, pageSize, data });
});

public sealed record ProductDto(int Id, string Name, decimal Price, bool InStock);

Performance checklist for filtering

  • Keep queries as IQueryable to push work to the database.
  • Apply Where before Select when both are present; project only needed columns.
  • Use AsNoTracking for read-only lists.
  • Avoid ToLower/ToUpper on columns in the predicate; prefer collations or provider functions (Like/ILike).
  • When filtering by a list of IDs, using a local List/array in Contains translates to SQL IN. Example:
var ids = request.Ids.ToList();
var q = context.Products.Where(p => ids.Contains(p.Id)); // becomes IN (...)
  • Index columns you filter on frequently (Price, Category, CreatedUtc, etc.).
  • Paginate with Skip/Take; fetch total count with the same predicate.
  • Beware client-eval: don't call methods in predicates that EF can't translate.
  • Consider compiled queries for hot paths in EF Core (EF.CompileQuery / EF.CompileAsyncQuery).

Common pitfalls

  • Calling ToList() too early moves filtering to memory and loads unnecessary rows.
  • Using Regex or custom C# methods inside an EF Core Where — not translatable; either move to in-memory after narrowing or use SQL functions.
  • Time zones: store in UTC; filter in UTC, convert at the edge.
  • Nulls in string filters: guard with p.Name != null or coalesce: (p.Name ?? "")

Takeaways

  • Prefer building composable predicates (optional filters) and keep them IQueryable.
  • Use provider functions for text search and let indexes work.
  • Test filters with both small in-memory sets and integration tests against your DB provider.

Copy the PredicateBuilder and optional filter patterns into your project and you'll have flexible, high-performance filtering with clean code.