In part 1 of this mini series I described a solution which involved adding the System.Linq.Dynamic namespace provided as an extra download by Microsoft. While it is a perfectly legal way of solving a dynamic predicates problem in LINQ, it actually comes at a cost: You're losing some of LINQ's type safeness and explicit beauty.
So in this second part I cover another way of achieving the same goal based on LINQ's Expression tree capabilities. In a nutshell Expression trees allow you to compose complex expression programmatically which you can then use as an input for LINQ queries. The framework classes to deal with Expression trees can be found in the System.Linq.Expressions namespace. Expression trees are a concept found in many functional languages and it took quite a while until I've been able to wrap my brain around them. Don't worry if you do not get everything exactly right from the very beginning.
Let's start with reiterating the problem:
Suppose you've got a list of keywords stored as strings in a collection. You want to use LINQ to SQL along with the sample Northwind database to select all customers where the ContactName contains at least one of the keywords. In "static" LINQ you would possibly write the following query:
code
var query = from c in db.Customers
where (c.ContactName.Contains("Maria")) || (c.ContactName.Contains("Pedro"))
select c;
But how do you handle a situation where the list of search terms ("Maria", "Pedro", etc.) is unknown at compile time?
You might feel tempted to compose the following LINQ query:
code
var query = from c in db.Customers
where c.ContactName.Contains("Maria")
where c.ContactName.Contains("Pedro")
select c;
Unfortunately this query will select all rows where ContactName contains "Maria" and "Pedro", as the generated SQL reveals:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[ContactName] LIKE '%Pedro%') AND ([t0].[ContactName] LIKE '%Maria%')
To solve the problem in a reusable way what we need is a mechanism to dynamically compose a Lambda expression at runtime based on a given list of search terms.
I've added a class PredicateExtensions to my solution:
code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
namespace www24100net.PlayingWithLinq
{
public static class PredicateExtensions
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expression1,
Expression<Func<T, bool>> expression2)
{
var invokedExpression = Expression.Invoke(expression2, expression1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.Or(expression1.Body, invokedExpression), expression1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expression1,
Expression<Func<T, bool>> expression2)
{
var invokedExpression = Expression.Invoke(expression2, expression1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.And(expression1.Body, invokedExpression), expression1.Parameters);
}
}
}
It implements two static methods True() and False() which merely serve as a point to start with once you construct a PredicateExtension and two extension methods Or() and And(). Please note the "this" keyword in the parameter list which marks Or() and And() as extension methods on the Expression type implemented inside the System.Linq.Expressions namespace.
And here is a sample use of the new extension methods:
code
NorthwindDataContext db = new NorthwindDataContext();
string[] searchTerms = new string[] { "Maria", "Pedro" };
var predicate = PredicateExtensions.False<Customer>();
foreach (string searchTerm in searchTerms)
{
string temp = searchTerm;
predicate = predicate.Or(c => c.ContactName.Contains(temp));
}
dataGridView1.DataSource = db.Customers.Where(predicate);
Note how inside the foreach loop we are dynamically composing a Lambda expression based on the added Or() extension method!
The Expression tree created at runtime and handed to the LINQ to SQL parser looks as follows:
predicate = {f => ((False Or Invoke(c => c.ContactName.Contains(value(www24100net.PlayingWithLinq.MainForm+<>c__DisplayClass0).temp),f)) Or Invoke(c => c.ContactName.Contains(value(www24100net.PlayingWithLinq.MainForm+<>c__DisplayClass0).temp),f))}
Wow... pretty wild code, or? Fortunately the LINQ to SQL stack knows how to parse this into highly efficient SQL. The SQL command created by LINQ to SQL based on the above tree:
'SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[ContactName] LIKE @p0) OR ([t0].[ContactName] LIKE @p1)',N'@p0 nvarchar(7),@p1 nvarchar(7)',@p0=N'%Maria%',@p1=N'%Pedro%'
That's exactly what we were looking for!
I'm planning to create an in-depth series about Expression trees where I start with the basics and move forward to advanced dynamic Expression tree composition. In the meanwhile I hope this helps moving forward.