Dynamic Queries with Expressions Trees in C#
In the modern programming world, a lot of data is being collected from the users and stored in the companies’ databases. This data is then visualized by the data owners in different formats, either as graphs, tabular data, and so on.
Now, imagine you are creating a visualization tool for your company that can be used across any data in your company. Sounds interesting right? A single tool that can query across your entire organization’s database based on the user's request and return the data.
Now, this is a very generic solution that can be applied across any database in the organization. But, from a developer’s perspective, imagine how difficult it will be to get a dynamic response every time you receive a user's request. Also, how do you create a dynamic filter that can filter the data out of the database and provide required data for the user in a performance effective way?
Now, let us consider a scenario. You are using inline queries in the code. By using the if condition, the where clause data can be added to the query and return the filtered data. However, while using Object Relational Mapping and Mongo Drivers, imagine how the data will be filtered dynamically as well as be retrieved effectively.
Let us understand how dynamic queries can be framed in ORM as well as what expression trees have to do with them using a simple project.
Table of contents
- Introduction to Expression Trees,
- Implementation of dynamic queries with expression trees, and
- Conclusion
Introduction to Expression Trees
When we want to fetch data from the database, we write queries with a ‘where condition’ based on which the data will be filtered and fetched. A database query without a ‘where condition’ would return the entire table records which can turn costly. Similarly, when we work with Entity framework or MongoDb driver, there would be scenarios where we might have to fetch records from a database inside the code to process. In those cases, we need to have those similar ‘where conditions’ come into the foray rather than getting the entire table records which can be achieved by using LINQ expressions.
The condition that is present inside the LINQ expression, gets converted to SQL statements. This restricts the data that is being fetched from the table by adding the where clause to the query. The condition for querying the table is considered as the expression tree.
In the next section, let’s see how to build dynamic queries using the expression tree.
Implementation of dynamic queries with expression trees
To implement dynamic queries with expression trees, we need a .Net solution that connects with a database and holds a table (with more than one column and few records). A solution from a previous blog has been reused here for setting up the base code for today's task.
Let's create a new endpoint that takes up Name as input and retrieves the data for the same. We will be using expression trees first and then dynamic queries to achieve the same.
To do so, create a new method as shown below in the UserController.cs file.
[HttpGet("GetByName/{name}")]
public async Task<UserData> GetUserByName([FromRoute] string name)
{
return await _userService.GetUserByName(name);
}
And in the IUserService.cs file, add the below lines of code.
Task<UserData> GetUserByName(string name);
And in the UserService.cs file, add the below lines of code.
public async Task<UserData> GetUserByName(string name)
{
var dbData = _dbContext.UserData.Where(x => x.Name == name).FirstOrDefault();
return dbData;
}
The above expression tree code provides a query similar to Select * from UserData where name = ‘inputname’, with which the data is filtered in the database and retrieved.
Run the solution and observe the result by visiting the URL - https://localhost:44369/api/user/getbyname/morgan.
Does it return a JSON that has the details of Morgan?
If there is a need to construct queries dynamically, then adding conditional columns inside the where predicate would be cumbersome and tedious to maintain. So, in such cases, constructing the query dynamically would help in making things better. To do so, replace the below code in the GetUserByName.
public async Task<UserData> GetUserByName(string name)
{
var userData = Expression.Parameter(typeof(UserData));
var memberExpression = Expression.PropertyOrField(userData, "name");//Column name in the table
var constantExpression = Expression.Constant(name);//Value to compare
var binaryExpression = Expression.Equal(memberExpression, constantExpression);
Expression<Func<UserData, bool>> lambda = Expression.Lambda<Func<UserData, bool>>(binaryExpression, userData);
return await _dbContext.UserData.Where(lambda).FirstOrDefaultAsync();
}
The above code creates a lambda expression dynamically which is then passed to the predicate of the where condition. Each line represents a node in the expression tree.
And often we tend to have multiple column filters in the where clause. The below code provides an example of how to include multiple conditions dynamically for the expression trees.
public async Task<UserData> GetUserByName(string name)
{
BinaryExpression filter = null;
var data = new Dictionary<string, string>();
data.Add("name", name);
data.Add("technology", ".Net");
var userData = Expression.Parameter(typeof(UserData));
foreach (var toProcess in data)
{
var memberExpression = Expression.PropertyOrField(userData, toProcess.Key);
var constantExpression = Expression.Constant(toProcess.Value);
var binaryExpression = Expression.Equal(memberExpression, constantExpression);
filter = (filter != null) ? Expression.And(filter, binaryExpression) : binaryExpression;
}
Expression<Func<UserData, bool>> lambda = Expression.Lambda<Func<UserData, bool>>(filter, userData);
return await _dbContext.UserData.Where(lambda).FirstOrDefaultAsync();
}
The same ‘GetUserByName’ method has been modified to filter multiple column values. Here, a local variable ‘filter’ is created with a null value initially. Then, for testing purposes, a dictionary that holds the column name and its value is created (In real-time, this data will be decided based on input request). And then the ‘for loop’ goes through each condition and creates the final expression tree which is being used in the ‘where condition’ of the LINQ statement.
Final Thoughts
This blog has given an overview of what expression trees are and how they can be used to build dynamic queries, which then can be used in ORMs.