Applications are becoming more complex and data-driven over time. Indeed, developing the ability to create and modify dynamic queries at runtime is becoming a critical skill for developers.
In this article, we’ll explore the basics of building dynamic query expression trees in C# and show how we can use them to build flexible and adaptable systems that enhance user experience and provide valuable insights into customer behavior.
Let’s start.
What are Dynamic Queries?
In C#, dynamic queries refer to the ability to construct queries at runtime, allowing flexibility in filtering, sorting, and retrieving data based on user input and where the structure or conditions of the query are not known in advance or can be dynamically changed.
Unlike static queries, which are defined at compile time and remain constant, dynamic queries allow developers to build more adaptable systems that can handle multiple use cases and provide a more versatile user experience.
Creating the Initial Project
For this article, we are going to use a simple console application in .NET 7 version and install Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer libraries through the NuGet Package Manager Console:
PM> Install-Package Microsoft.EntityFrameworkCore
and:
PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer
For the examples, we are going to start with two model classes:
public class Person
{
[Key]
public int Id { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public int Age { get; set; }
public Address? Address { get; set; }
}
public class Address
{
public string? Country { get; set; }
public string? State { get; set; }
public string? City { get; set; }
public string? AddressLine { get; set; }
}
Then, in the next step, we’ll create a derived class that inherits from DbContext and define a DbSet property to represent the table in the database:
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
public virtual DbSet<Person> Persons { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Person>().OwnsOne(c => c.Address);
}
}
Now we will instantiate a DbContextOptionsBuilder to help configure options for the DbContext:
var options = new DbContextOptionsBuilder<ApplicationDbContext>()
.UseSqlServer(@"Data Source=(localdb)\mssqllocaldb;Initial Catalog=test;Integrated Security=True")
.Options;
var context = new ApplicationDbContext(options);
context.Database.EnsureCreated();
Finally, we call the EnsureCreated method to verify that the database associated with the ApplicationDbContext is created. If the database already exists, this method will do nothing. However, if the database does not exist, it will create it based on the configuration defined in the ApplicationDbContext.
What Are Expression Trees?
Expression trees are data structure that represents a code expression as a hierarchical structure that can be modified and executed at runtime.
We commonly use Expression trees in LINQ to actively translate queries into a format executable against various data sources. The translation process involves transforming the declarative syntax of the query expression into a series of method calls. This process is more formally known as query expression translation.
We can also use Expression trees in scenarios where we need to use runtime code generation and manipulation, such as building dynamic queries.
Typically, we create Expression trees using the Expression class in C#. It contains a set of static methods that we can use to create all kinds of expressions, including arithmetic, logical, comparison, and method call expressions.
If you’d like to know more, check out our related article about Expression Trees in C#.
Using Lambda Expressions
Lambda expressions are a shorthand notation for defining small functions or methods that we can use inline in code. In fact, we often use expression trees with LINQ queries and other functional programming constructs to create powerful and flexible data processing pipelines.
The syntax for a lambda expression consists of the input parameters, followed by the => operator, and then the body of the expression. The body can be a single statement or a block of statements enclosed in braces.
Here is an example of a LINQ query that uses a lambda expression to apply a query condition:
DbSet<Person> persons = context.Persons; var query = persons.Where(p => p.FirstName == "Manoel")ToQueryString(); Console.WriteLine(query)
In this case, we call the Where method on the Persons to filter a sequence of elements.
The lambda takes a single input parameter "p" that represents each Person and returns a Boolean value indicating if the FirstName property of that object equals the constant "Manoel".
This LINQ query returns a query variable of type IQueryable. This is an interface in C# that defines a query that can be executed (but has not been yet!) against a data source that supports querying. This means that we run queries directly against the data source rather than in application memory, which can lead to better performance when working with large amounts of data.
After executing the ToList method, the code is first translated into an SQL statement and then executed on the SQL Server side.
SELECT
[p].[Id],
[p].[Age],
[p].[FirstName],
[p].[Gender],
[p].[IsMarried],
[p].[LastName],
[p].[Address_AddressLine],
[p].[Address_City],
[p].[Address_Country],
[p].[Address_State]
FROM [Persons] AS [p]
WHERE [p].[FirstName] = N'Manoel'
Using the Expression Tree API
In C#, the Expression API is a powerful library that allows us to dynamically create and manipulate expressions at runtime.
The Expression API is part of System.Linq.Expressions and provides a set of classes and methods that allow us to create and manipulate expressions programmatically.
This provides a way for us to generate dynamic code at runtime, using methods such as Expression.Property, Expression.Constant, Expression.Equal, and Expression.Call.
Create Equal Expression
We can rewrite the previous scenario using the Expression class:
public static Expression<Func<Person, bool>> CreateEqualExpression(string propertyName, object value)
{
var param = Expression.Parameter(typeof(Person), "p");
var member = Expression.Property(param, propertyName);
var constant = Expression.Constant(value);
var body = Expression.Equal(member, constant);
return Expression.Lambda<Func<Person, bool>>(body, param);
}
Let’s break down what we’re going here. In this case, we first use theExpression.Parameter(typeof(Person), "p") method to create a new ParameterExpression that represents the input parameter of the lambda expression. In this case, it represents a Person object named "p".
Then we call the Expression.Property(param, propertyName) method, which returns a MemberExpression representing a property of the input parameter "p". The property name is specified by the propertyName parameter.
When we call Expression.Constant(value) we create an expression that represents a constant value with which to compare the actual value specified by the value parameter.
Using Expression.Equal(member, val), we create a BinaryExpression that will perform an equality comparison between the member and the constant value.
Finally, we call Expression.Lambda<Func<Person, bool>>(body, param) to generate a LambdaExpression that defines a lambda function. This function takes a Person object as input and returns a Boolean value that indicates whether the object satisfies the filter condition. The body parameter is the body of the lambda expression, which contains the comparison logic between the object’s properties and the search criteria. The param parameter represents the input parameter of the lambda expression, which is the Person object being compared.
We can verify that everything is working as it should using the expression created as a parameter of the Where method:
var expression = CreateEqualExpression("FirstName", "Manoel");
var query = persons.Where(expression).ToQueryString();
After running this code, we’ll have the following result (for the remainder of the article, the full list of fields has been shortened to SELECT * for simplicity as it will be unchanged):
SELECT * FROM [Persons] AS [p] WHERE [p].[FirstName] = N'Manoel'
We can perform other types of comparisons by replacing the Equal method with any other available ExpressionType. For example, we could use NotEqual, GreaterThan, GreaterThanOrEqual, LessThan, or LessThanOrEqual, depending on the specific comparison we want to perform.
Join Expressions With Conjunction
The conjunction is a type of logic that we use to combine multiple search conditions into a single search query. This approach allows us to construct complex queries using a combination of logical AND and OR operators.
Let’s make some changes to our code:
public static Expression<Func<Person, bool>> CreateEqualExpression(IDictionary<string, object> filters)
{
var param = Expression.Parameter(typeof(Person), "p");
Expression? body = null;
foreach (var pair in filters)
{
var member = Expression.Property(param, pair.Key);
var constant = Expression.Constant(pair.Value);
var expression = Expression.Equal(member, constant);
body = body == null ? expression : Expression.AndAlso(body, expression);
}
return Expression.Lambda<Func<Person, bool>>(body, param);
}
Through the Equal expressions, we use the Expression.AndAlso method to create a single expression that represents the conjunction of all filter conditions.
After these modifications, let’s use the method informing the filters that will be applied:
var filters = new Dictionary<string, object>();
filters.Add("FirstName", "Manoel");
filters.Add("LastName", "Nobrega");
expression = CreateEqualExpression(filters);
query = persons.Where(expression).ToQueryString();
When we run the method, we’ll see the output:
SELECT * FROM[Persons] AS[p] WHERE[p].[FirstName] = N'Manoel' AND [p].[LastName] = N'Nobrega'
We generated the AND operator by using theExpression.AndAlso. Alternatively, we can generate an OR operator by using Expression.OrElse:
body = body == null ? expression : Expression.OrElse(body, expression);
Calling Method in Expression
The Expression.Call method can be used to create a new expression tree representing a method call with a specific set of parameters to be executed at runtime.
Let’s create a new expression method:
public static Expression<Func<Person, bool>> CreateContainsExpression(string propertyName, string value)
{
var param = Expression.Parameter(typeof(Person), "p");
var member = Expression.Property(param, propertyName);
var constant = Expression.Constant(value);
var body = Expression.Call(member, "Contains", Type.EmptyTypes, constant);
return Expression.Lambda<Func<Person, bool>>(body, param);
}
Through the Expression.Call method we create an expression whose result is a call to the Contains method.
The Contains method is a built-in method to check if a substring is present in a larger string.
When we use the expression:
expression = CreateContainsExpression("FirstName", "Man");
query = persons.Where(expression).ToQueryString();
We’ll have the following result:
SELECT * FROM [Persons] AS [p] WHERE [p].[FirstName] LIKE N'%Man%'
Another scenario appropriate for using Contains would be to check if a collection (such as an array or a list) contains a specified value:
public static Expression<Func<Person, bool>> CreateInExpression(string propertyName, object value)
{
var param = Expression.Parameter(typeof(Person), "p");
var member = Expression.Property(param, propertyName);
var propertyType = ((PropertyInfo)member.Member).PropertyType;
var constant = Expression.Constant(value);
var body = Expression.Call(typeof(Enumerable), "Contains", new[] { propertyType }, constant, member);
return Expression.Lambda<Func<Person, bool>>(body, param);
}
As we can see, the only difference to the previous method is that the Contains method is being obtained from an Enumerable. In this case, the Contains method determines whether the collection has a given element.
Let’s use the method:
expression = CreateInExpression("Id", new int[] { 1, 2, 3 });
query = persons.Where(expression).ToQueryString();
Now, the result includes the IN operator:
SELECT * FROM [Persons] AS [p] WHERE [p].[Id] IN (1, 2, 3)
Create Expression With Nested Properties
Sometimes we can improve our filters through nested properties, or object-based child properties.
Let’s do this by creating a new method:
public static Expression<Func<Person, bool>> CreateNestedExpression(string propertyName, object value)
{
var param = Expression.Parameter(typeof(Person), "p");
Expression member = param;
foreach (var namePart in propertyName.Split('.'))
{
member = Expression.Property(member, namePart);
}
var constant = Expression.Constant(value);
var body = Expression.Equal(member , constant);
return Expression.Lambda<Func<Person, bool>>(body, param);
}
From the propertyName in dot notation, we can see that a chain of property access expressions is being created.
Calling the split method, we split the string propertyName into an array of strings using the . character as a separator.
For each iteration over the string array, we call the Expression.Property method, passing the previous expression as the first argument to each subsequent call.
Now, we can use the expression:
expression = CreateNestedExpression("Address.Country", "USA");
query = persons.Where(expression).ToQueryString();
And the result is:
SELECT * FROM [Persons] AS [p] WHERE [p].[Address_Country] = N'USA'
Create Own Expression
A very common filter we use is Between, which is not built into the Expression class. We use the filter to represent a range-based comparison, where we want to check whether a value falls within a specified scope of values.
Let’s create a custom expression:
public static Expression<Func<Person, bool>> CreateBetweenExpression(
string propertyName,
object lowerValue,
object upperValue
)
{
var param = Expression.Parameter(typeof(Person), "p");
var property = Expression.Property(param, propertyName);
var body = Expression.AndAlso(
Expression.GreaterThanOrEqual(member, Expression.Constant(lowerValue)),
Expression.LessThanOrEqual(member, Expression.Constant(upperValue))
);
return Expression.Lambda<Func<Person, bool>>(body, param);
}
As we can see, we use the Expression.AndAlso method to combine two comparison expressions with the AND operator. The Expression.GreaterThanOrEqual comparison expression checks if the value is greater than or equal to the lower bound of the range, and the Expression.LessThanOrEqual checks if the value is less than or equal to the upper bound of the range.
Now let’s use the method:
expression = CreateBetweenExpression("Age", 18, 25);
query = persons.Where(expression).ToQueryString();
And the result:
SELECT * FROM [Persons] AS [p] WHERE [p].[Age] >= 18 AND [p].[Age] <= 25
Dynamic Queries Best Practices
To ensure efficient and maintainable dynamic queries using expression trees in C#, there are some best practices we can follow.
Cache Compiled Expressions
Compiling expressions can be a computationally expensive operation, especially if we are creating and compiling expressions frequently. To improve performance, we might consider caching compiled expressions for reuse rather than recompiling them every time:
public static class ExpressionCacheHelper
{
private static readonly Dictionary<int, Delegate> Cache = new Dictionary<int, Delegate>();
public static Func<T, bool> GetPredicate<T>(Expression<Func<T, bool>> expression)
{
var key = expression.GetHashCode();
if (Cache.TryGetValue(key, out var cachedDelegate))
{
return (Func<T, bool>)cachedDelegate;
}
var compiledDelegate = expression.Compile();
Cache[key] = compiledDelegate;
return compiledDelegate;
}
}
Here’s how we can use the ExpressionCacheHelper class:
var expression = CreateEqualExpression("FirstName", "Manoel");
var predicate = ExpressionCacheHelper.GetPredicate(expression);
var enumerable = persons.Where(predicate);
The GetPredicate method takes an expression as input and returns a compiled delegate that represents the expression. The Func<T> is just a pointer to an ordinary delegate that has been compiled down to IL (intermediate language). It is typically used for in-memory computations and operations within the application itself.
Handle Null Values
When we are working with expression trees, it’s important to handle null values appropriately to avoid null reference exceptions and unexpected behavior:
public static Expression<Func<Person, bool>> CreateEqualExpression(string propertyName, object value)
{
var param = Expression.Parameter(typeof(Person), "p");
var member = Expression.Property(param, propertyName);
var nullCheck = Expression.Equal(member, Expression.Constant(null));
var constant = Expression.Constant(value);
var body = Expression.Equal(member, constant);
var condition = Expression.Condition(
nullCheck,
Expression.Constant(false),
body
);
return Expression.Lambda<Func<Person, bool>>(condition, param);
}
In this example, the nullCheck checks if the property expression is null by comparing it to Expression.Constant(null). If the property expression is null, the condition expression returns false to exclude the person from the filter.
The condition is constructed using Expression.Condition. It evaluates the nullCheckas the condition and provides the corresponding expressions for the true and false branches. In the true branch, we return false to exclude the person. In the false branch, we compare the property value with the specified value using Expression.Equal.
Let’s use the method:
expression = CreateEqualExpression("FirstName", "Manoel");
query = persons.Where(expression).ToQueryString();
And the result:
SELECT *
FROM [Persons] AS [p]
WHERE CASE
WHEN [p].[FirstName] IS NULL THEN CAST(0 AS bit)
ELSE CASE
WHEN [p].[FirstName] = N'Manoel' AND ([p].[FirstName] IS NOT NULL) THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END
END = CAST(1 AS bit)
As we can see, now we have a conditional expression that checks if the FirstName column of the Persons table is equal to the string 'Manoel' and is not null. If both conditions are true, the expression returns 1 as a bit value, otherwise, it returns 0.
Document and Comment
Documenting our code and providing comments is crucial when working with expression trees to enhance maintainability and facilitate collaboration. By their nature, expression trees add a layer of abstraction that can obfuscate their actions at first glance, so in addition to a standard method signature summary, we add comments with sample code for quick recognition by future code maintainers:
/// <summary>
/// Constructs an expression tree representing an equality comparison between property and value.
/// </summary>
/// <param name="propertyName">The property of the object.</param>
/// <param name="value">>The value to compare.</param>
/// <returns>An expression tree representing the equality comparison.</returns>
public static Expression<Func<Person, bool>> CreateEqualExpression(string propertyName, object value)
{
// Create the parameter expression: p
var param = Expression.Parameter(typeof(Person), "p");
// Create the property expression: p."param"
var member = Expression.Property(param, propertyName);
// Create the constant expression: value
var constant = Expression.Constant(value);
// Create the binary expression: p."param" == value
var body = Expression.Equal(member, constant);
// Create the expression lambda p => p."param" == value
return Expression.Lambda<Func<Person, bool>>(body, param);
}
Validate Expressions
Validating lambda expressions involves checking that the input parameters or expressions have the expected types before performing any operations or evaluations. This helps ensure type safety and prevents runtime errors or unexpected behavior:
public static Expression<Func<Person, bool>> CreateEqualExpression(string propertyName, object value)
{
var param = Expression.Parameter(typeof(Person), "p");
var member = Expression.Property(param, propertyName);
var typeCheck = Expression.TypeEqual(Expression.Constant(value), typeof(string));
var constant = Expression.Constant(value, typeof(object));
var condition = Expression.Condition(
typeCheck,
Expression.Equal(member, constant),
Expression.Constant(false)
);
return Expression.Lambda<Func<Person, bool>>(condition, param);
}
As we can see, using the Expression.TypeEqual method, we build an expression that checks if the parameter is of type string. Then we create a conditional expression where if the type check is true it evaluates to an Expression.Equal; otherwise it evaluates to an Expression.Constantwith false value.
Now let’s validate the expression using a different value than expected:
var expression = CreateEqualExpression("FirstName", 1);
query = persons.Where(expression).ToQueryString();
And the output:
SELECT * FROM [Persons] AS [p] WHERE 0 = 1
When we invoke a method with an integer input, it returns false, indicating that the input is not of type string.
Dynamic Queries Performance
To make a quick comparison between dynamic and lambda queries, let’s run a benchmark available in the repository of this article for comparing the different approaches:
BenchmarkDotNet.Running.BenchmarkRunner.Run<CompareBenchmark>();
After running the benchmark we can inspect the result:
| Method | Mean | Error | StdDev | Median | |----------------------- |---------:|--------:|---------:|---------:| | EqualDynamic | 160.0 us | 6.02 us | 17.38 us | 158.9 us | | EqualLambda | 158.6 us | 7.15 us | 20.52 us | 155.7 us | | EqualConjuctionDynamic | 154.9 us | 5.57 us | 16.34 us | 156.5 us | | EqualConuctionLambda | 155.6 us | 5.21 us | 15.10 us | 156.3 us | | ContainsDynamic | 148.3 us | 6.95 us | 20.51 us | 152.4 us | | ContainsLambda | 117.8 us | 2.31 us | 6.17 us | 116.2 us | | InDynamic | 117.4 us | 2.30 us | 4.03 us | 117.2 us | | Inlambda | 142.1 us | 2.84 us | 5.47 us | 140.4 us | | NestedDynamic | 121.4 us | 2.40 us | 6.49 us | 119.0 us | | NestedLambda | 118.9 us | 2.36 us | 5.56 us | 116.9 us | | BetweenDynamic | 117.9 us | 2.26 us | 4.62 us | 116.4 us | | BetweenLambda | 120.9 us | 2.42 us | 6.49 us | 118.5 us |
Analyzing these benchmark results, we can see that we have practically no difference between the use of dynamic expressions and lambda expressions.
Conclusion
Throughout this article, we’ve covered the process of creating dynamic queries with expression trees in C#.
We’ve demonstrated how we can apply many of the different built-in expressions, as well as how to join them through conjunction. We also learned how to create our own custom expressions.
Finally, we’ve also shown some of the best practices for creating dynamic queries using lambda expressions.
Hopefully what we’ve learned will make us more comfortable with building complex expressions, and will allow us to craft flexible and powerful queries that can adapt to changing requirements and user needs.
