dynamic queries with LINQ

10 Mar

So, I’m a master C# programmer having been using it for a grand total of 4 weeks! My learning process began by ignoring the provincial and proverbial “Hello World” program, and instead I decided to learn the language features that are particular to C#.

As it happens, C# has quite a few modern language features that Java is sadly lacking (I won’t go into this here, but a comparison between the two is something I plan to blog about in the future). Along with many other new features, C# 3.0 gave us LINQ, which will be the topic of this post.

LINQ, I think, is reasonably straightforward to both understand and comprehend. Basic queries are extremely simple to create, and LINQ almost gives C# a functional programming feel. An example LINQ query is shown below:

from s in students where s.classyear == "2008" && s.courseyear == "2" select s;

That said, not everything is plain sailing with LINQ. The application I was writing required the where condition in my LINQ statement be dynamic. That is, a column specified in the where condition is only known at runtime. Achieving this functionality using the syntax shown above is not possible (which if you try it out I’m sure it will be clear why). To achieve this functionality the learning curve soon ramped up.

If you have searched on Google for dynamic queries in LINQ, you probably found these two articles, ScottGu’s Dynamic Linq and tomasp.net’s Building LINQ Queries at Runtime in C# - there were others, but few proved to be much help. ScottGu’s article was very good, and in essence solved my problem, but didn’t really help me understand the problem. The second article just went way over my head. Why? Well it came down to another new feature of C# that I didn’t know about, which was Expressions and Expression Trees, which, as it turns out, proved to be the key to solving this problem.

In addition to using the query syntax with LINQ (as shown above), you can also use query expressions. Query expressions are simply static methods that allow you to express a LINQ query. The example given above can be rewritten as follows using a query expression:

student.Where(s => s.courseyear == "2008" && s.classyear == "2");

On first looking at this example, it is still not obvious how runtime queries can be generated using query expressions. However, by taking a look at the type of the argument passed to Where helps us out:

Expression<Func<TSource, bool>> predicate

After some thought, this begins to make sense. The Where method takes a lambda expression whose parameter is of type TSource and returns a boolean. Therefore, surely we can create a lambda expression dynamically by building an expression tree for it? And yes, this is exactly what you are supposed to do.

C# experts are probably howling at me now and can’t understand why it took me so long to get there. However, this article is not aimed at you; it’s aimed at a newbie like me, and is simply trying to detail my thought process.

So now it was time to learn about Expression Trees.

Although I understood the concept of Expression Trees, they took a bit of getting used to – just don’t give up if you are struggling with it. In order to show how we can use Expression Trees to create dynamic queries, I will jump straight in with the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public Expression<Func<student, bool>> GetWhereLambda(string courseyear,
                                                      string classyear,
                                                      string myDynaColumn)
{
    ParameterExpression param = Expression.Parameter(typeof(student), "s");
 
    Expression courseExpr = GetEqualsExpr(param, "courseyear", courseyear);
    Expression classExpr = GetEqualsExpr(param, "classyear", classyear);
    Expression cond = Expression.And(courseExpr, classExpr);
 
    // This is where we create the expression for the dynamic column.
    // Obviously the value could have been dynamic as well but it 
    // saves a little visual complexity this way.
    cond = Expression.And(cond, GetEqualsExpr(param, myDynaColumn, "YES"));
 
    return Expression.Lambda<Func<student, bool>>(cond, param);
}
 
private Expression GetEqualsExpr(ParameterExpression param,
                                 string property,
                                 string value)
{
     Expression prop = Expression.Property(param, property);
     Expression val = Expression.Constant(value);
     return Expression.Equal(prop, val);
}

Seeing the code makes this look pretty simple, but there are quite a few concepts that a programmer new to C# has to get the hang of. However, let’s just step through the code – well one clause anyway, the rest follows from that.

Let’s look at how the clause (s.courseyear == "2008"), from out original where condition, converts to an Expression. We first create a parameter expression (Line 5), that is, the argument (parameter) supplied to the lambda expression (this parameter must be used for all sub-expressions that make up the lambda expression). Next, we need to create an expression to represent the right hand-side (s.courseyear) and left hand-side ("2008") of our clause. Since courseyear is a property of student (property as in C# property – think neat getters and setters) we create a property expression, i.e. prop = Expression.Property(param, "courseyear"), and as "2008" is really a constant, we create constant expression using value = Expression.Constant("2008"). Finally, we want to express the fact that the property should be equal to the constant in our query, we use the Expression.Equal(prop,value). That’s it. Essentially what expressions do is model our code as data.

Now it becomes obvious how we can represent a column in the where clause that is supplied at runtime – we simply create a property expression, as shown at Line 20, where property is the dynamic column name specified at runtime.

To combine the clauses in the where condition we simply use Expression.And, as shown in Lines 9 and 11. The resulting expression and the parameter is then used to create a lambda expression, which is in turn passed as an argument to Where, e.g.

Expression<Func<student, bool>> myLambda = GetWhereLambda("2008", "2", "active");
IEnumerable<student> filtered = students.Where(myLambda);

There you have it. It’s not that straightforward for those new to C# like me, but hopefully this article at least gives some code that can easily be used by people in the same position as I was.

14 Responses to “dynamic queries with LINQ”

  1. Aerusen March 12, 2009 at 2:18 pm #

    Thanks for this post – i am using the ideas you have to create a dynamic filter on my web site. I am currently trying to add dynamic casting of value types based upon the property type – so i can pass in integers via a string ‘value’ parameter and cast as int if the property is an integer etc… although now i type that there may be a far simpler way to do it. I’ll post it if i figure it out!

  2. Aerusen March 12, 2009 at 2:49 pm #

    How about this?: (obviously needs a try catch around the conversion – will add later)

    object valueCast;
    Expression prop = Expression.Property(param, property);
    valueCast = Convert.ChangeType(value,Type.GetType(prop.Type.FullName));
    Expression val = Expression.Constant(valueCast);

  3. Gregg March 12, 2009 at 9:12 pm #

    Hi Aerusen. Thanks for commenting. I’m not sure if there is a simpler way to do it – I’m still at the newbie stage with all this C# stuff. If I’m getting what you are saying correct I take it you are essentially trying to perform what CONVERT does in an SQL statement in code? Or am I pick you up wrong?

  4. Aerusen March 12, 2009 at 9:34 pm #

    No that’s right – in my code i am taking field names and values from a web page so everything is hitting the code as strings – and i want to be able to use your code to loop through each filed they are trying to filter with – but it fails if i try and pass a string representaion of (for example) “4″ into an expression where the base field is an integer. It is nice that ‘Expression’ lets you then query what the DB field type is.

    The code I added to your code basically lets you pass in any field and value and it will sort out the type matching at run time – so we can pass the following and it be ok:

    [Field name (type) -- value]
    FirstName (varchar(50)) — “Amye”
    LastName (varchar(50)) — “Couves”
    Age (Int32) — “30″ (note here that this would normally cause the code to fail but the ChangeType function changes “30″ to 30 at runtime.

    As I said – i think some try {} catch {} is needed around the convert as it would throw an exception on converting “bob” to an integer (the user might put the wrong data in a search field by mistake) but this is easy to add and can then be passed back to the front end as an error.

    Thanks again!

  5. Gregg March 12, 2009 at 10:43 pm #

    Ah right. I was a little slow to catch on to what you were doing – for some reason I was thinking you were doing this for the Select method rather than the Where (probably because I was looking at something similar earlier). So yeah, what you have done seems good, I would be surprised if there was another, simpler, way to achieve this. Hopefully others that visit with similar problems can make use of this. Good stuff, thanks.

  6. Charley April 21, 2009 at 3:08 pm #

    Hi – I am trying to create a dynamic query with an arbitrary number of OR conditions, and I am getting really confused.

    My user is going to hand me an ArrayList (let’s call it input) with tags/categories, along with an integer for the ID

    so I get values of input[0]=”foo”, input[1]=”bar”, input[2]=”baz”

    I want to construct a query that does:

    query = (from c in db.theTable
    where c.ID == inputID
    && c.tags ==

    //This is where I run into trouble – I want to loop through the //arraylist with a foreach (or for or whatever) and construct a series //of OR conditions to put into the query here) – I need to be able to //have an arbitrary number of conditions

    select c);

  7. Ferry Wijaya May 14, 2009 at 11:41 am #

    your function is to check equality of field and value. hm .. how about for checking a series of character in field (contains) statement ?.

    Tuhank’s

  8. Ben June 9, 2010 at 4:33 pm #

    Hi

    I tried your code and figured out that to finally use the expression in the Where-Method I had to call the Compile-Method.

    The final line should therefore look like this:

    IEnumerable filtered = students.Where(myLambda.Compile());

    Hopefully this helps other people facing the same trouble.

  9. George October 18, 2010 at 3:01 pm #

    Why not to use Predicate Builder http://www.albahari.com/nutshell/predicatebuilder.aspx ?

  10. Kenan Bek October 11, 2011 at 4:52 am #

    also you can use CodeDOM and Dynamic Library for creating LINQ queries at runtime. more detailed about this approachs you can read here http://kenanbek.com/site/post/dynamic-linq-queries-build-linq-queries-from-text-at-runtime/.

  11. DanJosef March 17, 2012 at 10:24 am #

    I’ve been looking into this for 3 months and this is the first article that started to make sense.

    I think that you can make it even more powerful using Type parameters for the Type of object you are filtering… So, e.g., the GetEquals method becomes something like:

    private Expression GetEqualsExpr(ParameterExpression param,
    String property,
    TColumn value)
    {
    Expression prop = Expression.Property(param, property);
    Expression val = Expression.Constant(value);
    return Expression.Equal(prop, val);
    }

    signature for GetWhereLambda becomes something like:

    public static Expression<Func> GetWhereAndLambda

    Thank you!

  12. DanJosef March 17, 2012 at 10:26 am #

    Strange… The code I pasted is missing the Type parameters… Trying again..

    private static Expression GetEqualsExpr(ParameterExpression param,
    String property,
    TColumn value)
    {
    Expression prop = Expression.Property(param, property);
    Expression val = Expression.Constant(value);
    return Expression.Equal(prop, val);
    }

    public static Expression<Func> GetWhereAndLambda(Dictionary filters)

  13. DanJosef March 17, 2012 at 10:28 am #

    Yeah it’s removing the Type parameters for some reason…

    Sorry about that — feel free to email me at the associated address if it’s not clear.

  14. Tamoj January 30, 2014 at 5:03 am #

    Thanks for the post.

Leave a Reply