Optimising Data Access Queries (Part 2)
In part 1 of this post I implemented a custom repository method to find all ingredients containing a keyword. The obvious improvement for this query was to extend the search to multiple keywords. As usual the first step was to write a unit test for the new method:
protected override void LoadTestData()
{
CreatePersistedIngredient(1, "Chicken thigh", FoodGroup.Meat);
CreatePersistedIngredient(2, "Chicken breast", FoodGroup.Meat);
CreatePersistedIngredient(3, "Red Pepper", FoodGroup.Vegetable);
CreatePersistedIngredient(4, "Jalapeno Chilli", FoodGroup.Vegetable);
}
[Test]
public void CanFindIngredientsByKeywords()
{
List<Ingredient> ingredients = ingredientRepository.FindByKeywords(new string[] { "chilli", "pepper" }).ToList();
ingredients.ShouldNotBeNull();
ingredients.Count.ShouldEqual(2);
}
The test expects 2 ingredients to be returned for the search both the Red pepper and Jalapeno Chilli ingredients should be returned.
Predicate functions and Expression trees
I wanted to keep using the Linq-to-NHibernate library for the data access queries as this ensures that the queries inherit all the benefits of Linq. I also find it more intuitive to use than HQL, for simple queries at least! The core problem was how to write a Linq query that could be extended for an arbitrary number of keywords. In HQL this could be achieved by looping over the keywords and building up the query string. However it is not so easy to iteratively build a lambda function.
My first effort involved chaining together predicate functions. However after passing the chained predicate to NHibernate the rather less than impressive result was nothing for any query! Looking at the SQL query produced showed that an incomplete statement was being passed without any of the filter keywords. I was unable to find any documentation to speak of for Linq-to-NHibernate but the NHibernate user group did prove useful. They suggested that I may have more success building up an expression tree and using that as the predicate.
Dynamic Linq-to-NHibernate Query
After reading up on expression trees I was in a position to code up a method to dynamically build the predicate. While researching the best way to tackle this, the internet again reminded me that there are not many original problems left in this world! This article by Joseph Albahari on dynamically composing expression predicates demonstrated how a helper class could be used to farm out the work of building up the predicate. The following class makes use of the PredicateBuilder class provided in the article:
public IEnumerable<Ingredient> FindByKeywords(IEnumerable<string> keywords)
{
Expression<Func<Ingredient, bool>> predicate = PredicateBuilder.False<Ingredient>();
foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or(i => i.Name.Contains(temp));
}
return Session.Linq<Ingredient>().Where(predicate);
}
- The expression predicate is initialised to false
- The predicate is chained together using the Or method of predicate builder class
- The final predicate will return true for any Ingredient that has a Name containing any of the keywords.
The FindByKeywords method passes the unit test at the start of the post. Implementing this method took a while, mostly due to the time taken to read up on the predicate functions and expression trees. However I think it was time worth spending as these language features are here to stay and will most likely increase in use over time. Also I now have a handy way of writing efficient Linq queries with multiple filter parameters :)