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

In this post I will be looking at the best way to re-factor a data access query which is used on the add ingredients page. Initially the query was written quickly with no consideration given to the time it would take to execute. It is not currently a problem as the database is very small but in future the page could be very slow to load.

    ingredientsModel.MatchingIngredients = IngredientRepository.GetAll()
        .Where(i => i.Name.ToLower().Contains(ingredientsModel.SearchIngredient.ToLower())).ToList();

This is a poorly optimised query as the generic Sharp Architecture repository GetAll() method call retrieves the entire data set from the database. It is then filtered in memory to get the actual matching ingredients. For larger datasets, it would be much quicker if the data is filtered before it is retrieved from the database. The query could be rewritten to use the FindAll(IDictionary propertyValuePairs) method like this:

    ingredientsModel.MatchingIngredients = IngredientRepository
        .FindAll(new Dictionary { { "Name", ingredientsModel.SearchIngredient.ToLower()} });

This does solve the optimisation issue as the resulting database SQL query uses a Where clause to pre-filter the data. However, the query is limited to retrieving exact matches to the search string. It is unlikely that users will input the exact ingredient they are searching for, so it would be better if the query retrieved all ingredients that contain the search term.

Unit Testing the Custom Repository

In order to do this I will use a custom repository and add my own query method. Much credit is due to the Sharp Architecture example projects, which provide a good basis for the techniques I’ve used here. First, I’ll write a unit test for the new repository method. The test class is shown below and each section is explained after the class:

    [TestFixture]
    public class IngredientRepositoryTests : RepositoryTestsBase
    {
        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);
        }

        private IIngredientRepository ingredientRepository = new IngredientRepository();

        private Ingredient CreatePersistedIngredient(int ingredientId, string ingredientName, FoodGroup foodGroup)
        {
            Ingredient ingredient = new Ingredient(ingredientName, foodGroup);
            ingredientRepository.SaveOrUpdate(ingredient);
            FlushSessionAndEvict(ingredient);
            return ingredient;
        }

        [Test]
        public void CanFindIngredientsByName()
        {
            List<Ingredient> ingredients = ingredientRepository.FindByName("chicken").ToList();
            ingredients.ShouldNotBeNull();
            ingredients.Count.ShouldEqual(2);
        }
    }

• The unit test inherits from the RepositoryTestsBase (namespace SharpArch.Testing.NUnit.NHibernate) which enables test data to be easily loaded into the in-memory database by overriding the the LoadTestData method.
• The ingredientRepository property is the custom repository data access class
• The CreatePersistedIngredient method is used in the LoadTestData method to save a single ingredient entity to the in-memory database. Note the call to FlushSessionAndEvict is used to persist the saved entity to the database
• The CanFindIngredientsByName method is our test method which runs the repository FindByName method 
• The test method asserts that the ingredients list is populated with 2 Ingredient entities

Implementing the Custom Repository

Now we are ready to implement the custom repository for the accessing ingredient data. Again, the class is shown below with explanations following it:

    public class IngredientRepository : Repository<Ingredient>, IIngredientRepository
    {        
        public IEnumerable<Ingredient> FindByName(string ingredientName)
        {
            return Session.Linq<Ingredient>().Where(a => a.Name.Contains(ingredientName));
        }
    }

• The custom repository inherits from the generic sharp architecture Repository class, ensuring that all the standard access methods are available
• It implements the IIngredientRepository interface. This enables IoC injection of the repository into the controllers.
• The FindByName method implements a Linq to NHibernate query using the Where method. This will be transformed into a SQL query using a Where clause to prefilter the data before it is retrieved.

The custom repository method passes the unit test and returns the expected data. The original query is now optimised and will perform much better against larger data sets. I’ve improved the efficiency of the data access, however I’d also like to improve the functionality. So in the next post I will be looking at extending the ingredient repository with a new dynamic query. This will enable searching by an arbitrary number of keywords.