Dynamic Queries
One question that arises from time to time is: how do I do dynamic queries in LINQ. The problem usually stems from allowing the users to generate search criteria, perhaps through a filter for a list for example. Because we cannot predict the combinations of values that the user will choose, LINQ, where we define our statements at compile time cannot really help us. So in the example below while we can search for all the customers in London, we don’t have any syntax like that in the bottom box, which somehow lets us evaluate an expression at run time.LINQ helps us with compile time expressions, but it is less help with run-time expressions.
So how do we deal with dynamic expressions?
First, LINQ to SQL does have a get out of jail card, it’s called ExecuteSQL and it is provided by DataContext to let you do pass through against the Db. LINQ also helps alleviate some pain here: object materialization is supported, so we can still get LINQ to pull back objects for us.
TestDataContext context = new TestDataContext();
StringBuilder buffer = new StringBuilder(
"select * from Customer where ");
for (int i = 0; i < listDictionary.Count; i++)
{
buffer.AppendFormat("{0} == {1}", listDictionary.Keys[ i ], listDictionary.Values[ i ]);
}
IEnumerable<Customer> customer = context.ExecuteQuery<Customer>(buffer.ToString());
Of course the danger here is that we now have some SQL, which is expressed not in terms of the domain, but of the relational schema. So if we do this, we need to wrap out interaction with the DataContext here within a Data Mapper, or perhaps in this sub-case a service, and push it all the way out into the infrastructure layer.Whatever I say next, a lot of people will take this pragmatic way out. Go for your life.
Specification
NHibernate gives us a little language, HQL, which allows us to query our objects. The advantage is that HQL is expressed as a string, so we can compose it, and thus use it to provide dynamic query support. This gives it an advantage in this instance over LINQ. The downside is that it is never checked by the compiler, so errors are more expensive to find.
There a way to gain the benefits of composition, with the comfort of type checking. One solution is the Specification pattern, which I have blogged about before. To summarize that more lengthy post: a specification is a pattern for expressing a rule which you want to use to test an object. A specification is ultimately used to separate two orthogonal concerns: testing objects and the objects themselves.
What we want is something like below, where we combine criteria into an expression that we use to test items in a repository to produce a filtered result set.
ISpecification<Customer> customersInLondon = new Specification<Customer>(c => c.City == "London");
ISpecification<Customer> customersInUK = new Specification<Customer>(c => c.City == "UK");
ISpecification<Customer> customersInLondonUK = customersInLondon.And(customersInUK); List<Customer> matchingCustomers = customerRepository.FindBySpecification(customersInLondonUK);
We construct individual queries, or specifications, in a strongly typed fashion by using generics and lambda expressions. We make them composable, via the composite pattern, so we can and/or/not expressions as we build them.
public interface ISpecification<T>
{
Expression<Func<T, bool>> Predicate {get;set;}
ISpecification<T> And(ISpecification<T> other);
bool IsSatisfiedBy(T customer);
ISpecification<T> Not();
ISpecification<T> Or(ISpecification<T> other);
}
We define an abstract base class that handles the work of combining specifications. The hard work of combining the lambda expressions depends on some expression tree magic over at my previous blog post:
abstract public class AbstractSpecification<T> : ISpecification<T>
{
protected Expression<Func<T, bool>> predicate;
public abstract bool IsSatisfiedBy(T value);
public ISpecification<T> And(ISpecification<T> other)
{
return new AndSpecification<T>(this, other);
}
}
This combined expression can replace the predicate in the where clause of our LINQ expression, because we are just passing an expression tree through to LINQ to SQL for evaluation:
public IEnumerable<Customer> FindBySpecification(ISpecification<Customer> specification)
{
IQueryable<Customer> customerQuery = from c in Customers select c;
IQueryable<Customer> restrictedCustomerQuery = customerQuery.Where<Customer>(specification.Predicate);
return restrictedCustomerQuery.ToList();
}
But, like I say, a lot of folks will prefer the ExecuteQuery approach over messing with expression trees.
But my second piece of advice would be:
You can use ExecuteQuery to exercise your dynamic queries, but the call should be encapsulated in the infrastructure layer to keep SQL out of the domain. Where you have a significant requirement for dynamic queries, consider implementing the Specification pattern and using Expressions to integrate with LINQ
Next up, we'll talk about how to build from the domain, rather than from the data, and how to markup your domain objects for persistence.
Posted
Tue, Dec 4 2007 11:33 PM
by
Ian Cooper