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.