Dynamic LINQ Queries in EF Core

Most applications start with simple database queries. You retrieve a list of records, filter by a specific field, and return the results. This works well until users start asking for more flexible search options.

Consider an employee management system where users can search by name, department, employment status, or hiring date. Some users might apply one filter, while others combine several filters. They may also want to change the sorting order.

Writing a separate query for every possible combination quickly becomes difficult to maintain.

This is where dynamic LINQ queries become useful. With Entity Framework Core (EF Core), we can build queries based on the filters provided at runtime without manually constructing SQL statements.

Building Queries Dynamically

The main idea is to start with an IQueryable<T> and apply conditions only when they are needed.

Unlike IEnumerable<T>, which is commonly used for working with data in memory, IQueryable<T> allows EF Core to build an expression tree that its database provider can translate into SQL.

Suppose we have an Employee entity with the following properties:

C#
public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public int DepartmentId { get; set; }
    public bool IsActive { get; set; }
    public DateTime HireDate { get; set; }
}

We want to retrieve employees based on optional search criteria.

The code examples in this article are simplified for demonstration purposes and are not taken from an actual production project.

C#
public async Task<List<Employee>> SearchEmployeesAsync(
    string? name,
    int? departmentId,
    bool? isActive)
{
    IQueryable<Employee> query = dbContext.Employees
        .AsNoTracking();

    if (!string.IsNullOrWhiteSpace(name))
    {
        query = query.Where(e => e.Name.Contains(name));
    }

    if (departmentId.HasValue)
    {
        query = query.Where(e =>
            e.DepartmentId == departmentId.Value);
    }

    if (isActive.HasValue)
    {
        query = query.Where(e =>
            e.IsActive == isActive.Value);
    }

    return await query.ToListAsync();
}

Notice that we are not executing the database query every time we add a Where condition.

Each condition modifies the query expression. EF Core executes the combined query when ToListAsync() is called.

This behavior is known as deferred execution.

For example, if the user provides a department ID and active status but leaves the name empty, only those two filters are applied.

EF Core translates the resulting expression into SQL, allowing the database to perform the filtering.

Adding Dynamic Sorting

Filtering is only part of the requirement. Many applications also allow users to sort results by different columns.

A common mistake is accepting a column name from the client and passing it directly into a dynamically constructed SQL statement.

A safer approach is to explicitly define which fields can be used for sorting.

C#
public static IQueryable<Employee> ApplySorting(
    IQueryable<Employee> query,
    string? sortBy,
    bool descending)
{
    return (sortBy?.ToLowerInvariant(), descending) switch
    {
        ("name", false) => query.OrderBy(e => e.Name),
        ("name", true) => query.OrderByDescending(e => e.Name),

        ("hiredate", false) => query.OrderBy(e => e.HireDate),
        ("hiredate", true) => query.OrderByDescending(e => e.HireDate),

        _ => query.OrderBy(e => e.Id)
    };
}

This approach keeps sorting predictable and limits it to supported fields.

It also avoids introducing unnecessary complexity through reflection or manually constructed expression trees.

For applications with a small number of sortable columns, a simple switch expression is often enough.

When Expression Trees Become Useful

The previous examples work because we already know which properties users can filter.

But what happens when the application needs a generic filtering component that supports different entities and operators?

This is where expression trees become relevant.

An expression tree represents code as a structure that can be inspected and combined. EF Core uses these expressions to understand what should be translated into database operations.

For example:

C#
Expression<Func<Employee, bool>> filter =
    employee => employee.IsActive;

This is different from a compiled Func<Employee, bool> delegate.

The expression preserves the structure of the condition, allowing EF Core to translate it into SQL.

We can also construct expressions programmatically using the System.Linq.Expressions API.

However, dynamic expression construction introduces additional complexity. Property types, nullable values, supported operators, and database translation all need proper handling.

For most business applications with known search fields, composing ordinary LINQ queries is easier to understand and maintain.

Performance Considerations

Dynamic queries are flexible, but we still need to pay attention to how they execute.

One important rule is to apply filtering before materializing the results.

Consider this example:

C#
var employees = await dbContext.Employees.ToListAsync();

var filtered = employees
    .Where(e => e.IsActive)
    .ToList();

Here, all employee records are retrieved before filtering happens in memory.

For large datasets, this can result in unnecessary database traffic and memory usage.

Instead, keep the query as IQueryable until the necessary conditions have been applied.

Other considerations include using AsNoTracking() for read-only queries, adding pagination for large result sets, and reviewing database indexes for frequently filtered columns.

It is also important to remember that not every .NET method can be translated into SQL. Unsupported expressions may cause EF Core to throw an exception during query execution.

For more complex queries, inspecting the generated SQL using ToQueryString() can help identify unexpected joins, filtering behavior, or inefficient query patterns.

Keeping Dynamic Queries Maintainable

As search requirements grow, it can be tempting to build a generic query engine that supports every possible filtering and sorting combination.

That flexibility comes with additional maintenance costs.

A practical approach is to start with ordinary LINQ composition and introduce reusable filtering components only when multiple features genuinely need them.

Keep supported filters explicit, validate user input, and avoid exposing unrestricted entity properties through public APIs.

For applications serving multiple tenants, tenant isolation and authorization conditions must remain mandatory regardless of which optional filters the user supplies.

Dynamic LINQ should make queries easier to maintain, not make their behavior harder to understand.

Final Thoughts

Dynamic LINQ queries in EF Core do not always require expression builders, reflection, or additional libraries.

In many cases, starting with IQueryable, applying conditional filters, and executing the query once is enough.

The important part is understanding how EF Core builds and translates queries. Once that behavior is clear, it becomes easier to support flexible search requirements while keeping the implementation readable and predictable.

Sources

Assi Arai
Assi Arai
Articles: 64