Archive | LINQ RSS feed for this section

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.