Friday, December 5, 2014

Dynamic query using LINQ

In this article, we will try to understand how we can create a dynamic query in LINQ. In order to do this, we first need to understand the concept of expression trees.
What is Expression tree?

As per MSDN : Expression trees represent code in a tree-like data structure, where each node is an expression, for example, a method call or a binary operation such as x < y
In simple language, an expression tree is a tree data structure, which can be used to represent some executable piece of code, in the form of nodes of this tree. This code is then converted into actual executable code. This kind of property allows you to generate the dynamic linq query. So let's start with the code :
We have the following linq query which returns data of a Customer, based on the CustomerId.
_dbEntities.Customers.Where(cust => cust.CustomerId == 10).FirstOrDefault();
We will now convert this query into dynamic query, by creating an expression tree for it. First, we need to add the namespace System.Linq.Expressions. This class contains various static methods, which we will use to generate the query. These methods include Parameter, Constant, Property etc. We will be using these methods to generate the query, in multiple steps. So let's start
Step 1 : We need to first generate the input parameter which is represented by code on the left of the lamda operator i.e. cust =>. For this, we use the Expression.Parameter method, which will be passed 2 parameters Type and String name. Here
§  Type is the type of entity we are using i.e. Customer entity.
§  String is the any name we use to represent an input to the parameter, which in our case is cust. So our code will be :
ParameterExpression pe = Expression.Parameter(Customer, "cust");
Step 2: Next, we need to use the CustomerId property to make the comparison with a value which is represented by cust.CustomerId in our initial query. So we need to get that property first and we use the Expression.Property method for it. So our code will be:
var _prpToUse = Expression.Property(pe, "CustomerId");
Step 3: Now we need to compare our CustomerId with some value say 10 in our case. So we generate our expression for this using the Expression.Constant method as :
var _cnstToUse = Expression.Constant(10);
Step 4: Next we need to combine the above two expressions, to generate expression cust.CustomerId == 10. For this purpose, we will use the Expression.Equal method. So our code becomes:
var qry = Expression.Equal(_prpToUse, _cnstToUse);
Step 5: Now, we have the expression of the form cust => cust.CustomerId == 10 and need to combine it with the Where extension method, to complete the query. So our following code will pass the above expression to tit, using the MethodCallExpression method.
MethodCallExpression whereExpression = Expression.Call(
new Type[] { lst.ElementType },
Expression.Lambda<Func<Customer,bool>>(qry, new ParameterExpression[] { pe }));
Step 6: Finally, we need to execute the query it using CreateQuery method in the provider on which we need to execute the query.
Here, lstData is the list of customers which we have from the database. Execute the code and see the results. It will be the same that we had at the start of the article.
In all the above steps, except step 6, we have created expressions of code that are combined to create an Expression tree, as a whole, which is nothing but the query that we executed at the start of the article.
Below is the generic implementation of the code that we created

So this was about the use of Expression trees to generate the dynamic linq query. Hope you enjoyed reading it.

No comments:

Post a Comment