Building Runtime LINQ Filters for Complex .NET Applications

Complex filtering is where a conventional where clause starts to creak. An order screen might combine customer region, status, product category, an AUD amount range and a date window, with each rule optional and each group governed by all or any semantics. A dynamic LINQ query builder turns those changing requirements into runtime-generated predicates while keeping the application’s data access logic in C#.

That flexibility must fit the way Australian software teams build and operate products. Many teams serve customers from Sydney and Melbourne while development and support are distributed through Brisbane and Perth. For workloads using Azure Australia East or Australia Southeast, query design still affects round-trip time and database load. Where the Privacy Act 1988 and Australian Privacy Principles apply, filter metadata and diagnostic logs also need careful handling.

The approach presented at the C# Corner Annual Conference 2020 centres on expression trees, IQueryable, and provider-aware translation. It covers how to model filter fields, compose nested conditions, reject unsafe operations and verify the SQL that ultimately reaches the database. The goal is a flexible filter contract that remains predictable as APIs, datasets and users evolve.

Runtime filters are a product feature, not a SQL string

A search page and an internal reporting tool rarely need the same fixed query, yet both benefit from shared filter concepts. The useful abstraction is a small filter language: a field identifier, an operator, a typed value and optional grouping. ASP.NET Core can deserialize that language into a request object, after which a builder validates it and creates an expression tree over the queryable entity.

Hard-coding one lambda per combination creates combinatorial growth, while concatenating SQL from request values creates injection and authorisation risks. Parsing arbitrary expression strings offers more freedom, but mistakes become difficult to diagnose and some valid-looking expressions translate poorly. A structured builder occupies the middle ground: data callers select known fields and operators, while the C# implementation controls how those choices become predicates.

Modelling fields with expression trees

An Expression<Func<T, bool>> describes a predicate as a tree rather than executable code supplied as text. Leaf nodes identify properties, constants hold filter values, and nodes such as Equal, GreaterThan and AndAlso define their relationships. When the source is an IQueryable<T> over Entity Framework Core, that tree can be translated into SQL instead of being run as an in-memory delegate.

Start with a field registry that maps public names to strongly typed accessors. It can record the property type, supported operators, whether null is meaningful and any database-specific constraints. The same registry then supports request validation, documentation and UI filter controls, so the accepted field list is defined once rather than repeated in controllers, queries and tests.

var parameter = Expression.Parameter(typeof(Order), "order");
var member = Expression.Property(parameter, nameof(Order.Status));
var value = Expression.Constant(OrderStatus.Pending);
var body = Expression.Equal(member, value);

var predicate =
    Expression.Lambda<Func<Order, bool>>(body, parameter);

This deliberately small example still contains the important boundary: the caller chooses a registered status, while the builder chooses the property and comparison node. For values of different types, use Expression.Convert only when the conversion is registered and suitable for the domain; converting arbitrary strings can turn malformed input into surprising predicates.

Composing predicates for nested filter groups

Most real filter UIs need boolean composition rather than a long chain of conditions. A PredicateBuilder-style helper can start with a predicate of true for and groups or false for or groups, then combine expressions with Expression.AndAlso or Expression.OrElse. Nested groups require explicit tree construction because ordinary lambda chaining loses the structure users selected in the interface.

Keep the grouping intact when producing SQL. Expanding A AND (B OR C) into a distributive form can increase expression size quickly and makes accidental precedence bugs more likely. A canonical filter model should also preserve whether an empty any group matches everything and whether an empty all group matches nothing.

Values belong in expression constants or captured variables, never inside a property path or method name. This lets Entity Framework Core parameterise literal values during SQL generation and keeps user text out of the query’s structural syntax. Null comparison also needs deliberate handling because == null, != null and methods such as Equals can have different translation and null semantics.

Translating rules into provider-friendly queries

A dynamic expression is only useful if the provider can translate it. Entity Framework Core handles common comparisons, null checks, boolean combinations and selected string operations, but provider support varies. An in-memory Enumerable.Contains may become client evaluation and transfer rows before filtering. Inspecting generated SQL through query logging or ToQueryString() exposes that difference before the code reaches production.

Design expressions around provider capabilities and give dates and amounts precise boundaries. A common pattern is a half-open range, created >= start AND created < end, which avoids missing records at the final millisecond. Use AsNoTracking for read-only paths, project only required columns after applying filters and run asynchronous materialisation and count operations. A stable query shape also makes query plans easier to compare across releases.

The practical choice depends on who controls the input. Public APIs benefit from an explicit field registry; internal tools can use a constrained dynamic LINQ parser; fixed tenant predicates can be composed separately with PredicateBuilder. A hybrid architecture often keeps trusted business rules independent from the customer-controlled filter tree.

Guarding untrusted filter input

Treat every filter document as untrusted input. Resolve field names through an allow-list, restrict each field to supported operators and convert values to the property’s exact type before building the expression. Australian interfaces often display dates as dd/MM/yyyy and prices as AUD with GST treatment, but APIs should accept an unambiguous format and use invariant parsing rather than current culture. Decimals should remain decimals throughout.

Apply hard limits to filter count, nesting depth, page size and execution time. Combine a server-controlled tenant predicate with the user expression using an explicit top-level AND so an OR group cannot escape its authorised scope. Keep values parameterised, whitelist sortable fields and reject attempts to reference navigations, functions or unknown members. For large data sets, cancellation and provider-side execution are safer than materialising a query in memory.

Testing filter behaviour and query plans

Unit tests should inspect the expression tree and filter model rather than relying only on returned rows. Cover null values, enums, empty groups, mixed and and or logic, boundary dates and the maximum permitted complexity. Integration tests should run against the same database provider used in production, because SQLite or an in-memory store can accept expressions that a SQL Server or Azure SQL deployment will translate differently.

Operational evidence should show which filter shape was requested, how long it took and how many rows it returned, while sensitive values remain out of logs. A correlation ID, normalised field and operator names, elapsed time and query-plan information are usually more useful than raw JSON. CI performance tests can compare representative datasets before a release, and Azure Monitor or OpenTelemetry can surface regressions after deployment. For Australian organisations subject to the Privacy Act and Australian Privacy Principles, logging policy should cover filter values as carefully as any other customer data.

Optimising latency, memory and database load

Database indexes should reflect common filter combinations, not every field a product exposes. Composite indexes follow realistic predicates and selective leading columns, while projection and pagination reduce the amount of data crossing the network. Avoid unbounded ToList calls; use server-side paging and asynchronous APIs. Compiled query paths can help a stable hot query, while a dynamic builder is better suited to genuinely changing filter combinations.

Cache field metadata and validated expression plans where appropriate, but do not share cached results across tenants or permission boundaries. Measure plan changes, row counts and parameter patterns after each release, then adjust indexes or query structure based on evidence. A disciplined expression-based filter builder gives .NET teams a stable boundary between flexible user input and deliberate data access. It supports the adaptability required by Australian products while keeping query shape, tenant boundaries and execution cost visible and testable.