Creating a Dynamic LINQ Query Builder for User-Defined Filters

Business software rarely knows every filter users will need in advance. A warehouse manager may want orders above a particular value, while a customer service team may need overdue tickets from a selected suburb. Hard-coding each combination quickly produces brittle controllers, duplicated SQL, and a user interface that cannot keep pace with operational needs.

A dynamic LINQ query builder for user-defined filters provides a structured way to translate filter choices into executable C# expressions. With a careful design, the same approach can support Entity Framework Core, in-memory collections, reporting screens, and APIs while protecting the database from unsafe input. It is particularly useful for Australian organisations managing varied datasets across retail, logistics, finance, education, and public services.

Model filters as data before building expressions

The first design decision is to represent a filter as data rather than accepting raw LINQ or SQL from a browser. A useful filter object can contain a field name, comparison operator, value, and optional group information:

public sealed class FilterRule
{
    public string Field { get; set; } = "";
    public string Operator { get; set; } = "";
    public string? Value { get; set; }
}

A request might contain rules such as Status equals Active, Total greaterThan 500, or CreatedAt greaterThanOrEqual 2024-01-01. The server should map each public field name to an approved model property. That prevents a caller from requesting hidden columns, navigation properties, or arbitrary members through reflection.

Simple rules become more powerful when the model supports groups. For example, a customer might need orders where the status is Paid and the total exceeds $500, or where the delivery suburb is Richmond or Footscray. A group structure can carry AND and OR logic without forcing the user interface to understand expression tree syntax.

A production filter contract should also include paging, sorting, and a maximum number of rules. Values should arrive in a predictable culture-independent format, especially for dates and decimals. Australian users may enter dates as 31/12/2024, but APIs should generally transport dates as ISO 8601 values and convert them deliberately at the boundary.

Create expression trees with strongly typed properties

LINQ providers execute a predicate such as x => x.Status == "Active" in different ways. With Entity Framework Core, the expression is usually translated into SQL; with an in-memory list, it is compiled and run by the application. Expression trees bridge those execution models.

The builder begins with a parameter representing the entity and then resolves the requested property. A simplified equality expression looks like this:

var parameter = Expression.Parameter(typeof(Order), "order");
var property = Expression.Property(parameter, "Status");
var constant = Expression.Constant("Active");
var body = Expression.Equal(property, constant);
var predicate = Expression.Lambda<Func<Order, bool>>(body, parameter);

A general implementation needs to convert the incoming string to the property’s underlying type. Nullable values require special handling, as do enums, Guid, DateTime, and decimal amounts. TypeDescriptor.GetConverter, Enum.Parse, and explicit conversion routines can help, but parsing should return a controlled validation error rather than allowing an exception to escape from a request handler.

String operations deserve careful treatment. A contains operator may map to string.Contains, while startsWith can use StartsWith. Null checks should be generated when a property is optional. For case-insensitive searches, the chosen behaviour should be explicit because database collation differs between providers and installations. This matters when an application runs in Sydney, Perth, or a cloud region outside Australia with a different default configuration.

Combine rules safely for real-world logic

A query builder becomes useful when it can combine individual predicates. Expression.AndAlso is suitable for conditions that must all match, while Expression.OrElse handles alternatives. These operators preserve short-circuit semantics and produce the logical structure most LINQ providers expect.

A common pattern is to start with a null predicate and add rules progressively:

Expression<Func<Order, bool>>? current = null;

foreach (var rule in rules)
{
    var next = BuildRule<Order>(rule);

    if (current is null)
        current = next;
    else
        current = Combine(current, next, Expression.AndAlso);
}

The Combine method must replace parameters so that both expression bodies refer to the same parameter instance. Libraries such as LinqKit can simplify predicate composition, but a small custom expression visitor is often easier to audit when the supported operators are limited.

Grouping deserves attention in the user interface and the server contract. Consider (Status = Paid OR Status = Shipped) AND Total > 500. If the API flattens all rules into one list, it may accidentally produce Status = Paid OR (Status = Shipped AND Total > 500), returning incorrect records. A recursive filter node with Group and Rule variants preserves parentheses and makes the intended logic clear.

The builder should reject unsupported combinations rather than silently changing them. For instance, applying greaterThan to a text field or contains to a numeric column should produce a useful validation message. This is important for Australian businesses with compliance reporting, where a subtly incorrect result can affect payroll, GST records, or customer communications.

Connect the builder to Entity Framework Core

A dynamic predicate should be applied as early as possible in the query pipeline:

IQueryable<Order> query = db.Orders.AsNoTracking();

if (predicate is not null)
    query = query.Where(predicate);

query = query
    .OrderByDescending(order => order.CreatedAt)
    .Skip(page * pageSize)
    .Take(pageSize);

Filtering before projection, pagination, and materialisation gives the database a chance to use indexes and return fewer rows. Calling ToList() before applying the predicate defeats that advantage and can cause an avoidable memory and latency problem. This risk is significant for distributed operations moving stock between Melbourne, Brisbane, and regional centres.

Every permitted filter field should have a corresponding database strategy. Columns frequently used for equality or range searches may need indexes, while leading-wildcard searches can remain expensive even when the expression is valid. Query logs and execution plans reveal whether a flexible reporting feature is producing efficient SQL or creating table scans.

A robust API should cap page size, reject excessively deep group trees, and impose a sensible limit on the number of conditions. Timeouts and cancellation tokens should be passed through to Entity Framework Core. Sorting deserves the same allow-list treatment as filtering; accepting a client-supplied property name directly in OrderBy can expose fields or cause translation failures.

Security extends beyond SQL injection. Dynamic queries can become a denial-of-service vector when they generate expensive joins, huge Contains lists, or wildcard searches across large tables. Tenant isolation must be applied as a server-owned predicate, never as a rule the user can remove. This is relevant to Australian SaaS providers handling customer records under privacy obligations and hosting workloads through services such as Azure Australia East.

Design a usable filter experience

A filter builder is easier to use when it offers field-specific controls. Dates should use a date picker, numeric columns should display numeric inputs, and enumerations should appear as a select list. A free-text operator menu should not expose options that the backend cannot evaluate consistently.

The client can fetch field metadata from an endpoint containing a display label, data type, permitted operators, and available values. The server remains authoritative, so metadata improves usability without becoming a security boundary. Labels such as “Order total” and “Dispatch date” are clearer than internal names such as TotalAmount and CreatedAt.

User-defined filters often become saved views. A saved view should retain the filter definition, selected columns, sort order, and ownership details. Versioning the definition helps when a field is renamed or removed. Instead of failing mysteriously, the application can mark an obsolete rule and ask the user to select a replacement.

Accessibility and local conventions matter in enterprise applications. Keyboard navigation, readable contrast, clear validation, and screen-reader labels support a wider workforce, including users working in busy warehouses or call centres. Currency formatting should display Australian dollars when appropriate, and date labels should avoid ambiguity even though the backend uses an invariant representation.

Teams looking for broader examples of cloud, data, and .NET engineering topics can browse the C# Corner conference materials alongside their own implementation work. The underlying principle is the same: a useful technical feature must connect code, operations, and the people who rely on the result.

Test, compare, and operate the query system

Testing should cover the translation process and the result set. Unit tests can verify that each operator creates the expected expression, while integration tests against the real database provider confirm that Entity Framework Core can translate it. An expression that works with List<T> may still fail when sent to SQL, particularly when it uses custom methods or unsupported string operations.

Property-based tests are useful for combinations of nullable values, empty strings, dates, decimals, and nested groups. Security tests should attempt unknown fields, malformed values, oversized lists, deep nesting, and predicates that try to escape a tenant boundary. The expected behaviour should be a clear client error, not a server crash or a silently broadened query.

Observability completes the feature. Log a normalised representation of the filter, execution duration, result count, and database command metrics, while excluding personal information and confidential values. A correlation ID makes it easier to trace a slow report from an Australian office through an API and into the database. Monitoring can then identify popular filters that deserve indexes or redesigned screens.

The following comparison highlights practical implementation choices for a maintainable filter service:

A well-structured implementation separates the request contract, field metadata, value conversion, expression construction, and data-access execution. That separation allows a web application to evolve from a small search form into a sophisticated reporting tool without placing query logic in controllers or exposing the database model to end users. It also gives development teams a clear foundation for future additions such as full-text search, reusable filter templates, and role-specific reporting permissions.