Build Reusable, Testable Queries Part 2

February 13, 2012 — 2 Comments

Query encapsulation can become quite empowering. For instance, query objects can be decorated, extended and reused. They allow us to implement concepts like targeted caching or user defined queries. They even allow us to execute the same query on two different data sources.

In part 2 of this series, we will be looking at how we can leverage the IModelQuery interface to enable us to reuse queries.
In this post I will show you how this small yet powerful interface can help you build reusable queries which can be tested.

public interface IModelQuery<out TResult, in TModel>
{
    IEnumerable<TResult> Execute(TModel model);
}

Model Contract

The Model Contract will allow us easily build mocks of our Entity Framework Model for queries. Building a complete Entity Framework mock can sometimes become a challenge of its own. Because a single mock can also become complicated to understand and maintain, I recommend building as many mock Models as necessary.

The following interface will not solve all the problems related to mocking an Entity Framework Model, but it will greatly help by exposing a Queryable which can be used to build queries.

public interface Model {
    IQueryable<TType> Queryable<TType>() where TType : class, new();
}

Building Reusable Queries Using a Model Contract

Finding a Person by their employer ID is not a complex query. In most systems, a query such as this one would be reproduced over and over throughout the system. Adding security, caching or diagnostics at a later time could prove to be an interesting challenge.

This query demonstrates how finding a Person by their employer ID can be encapsulated. An object such as this one, can be extended by using the Decorator pattern to add new behavior. Query encapsulation also follows the open/closed principle, which states “software entities (classes, modules, functions, etc.) should be open for extension, but closed for modification”. In other words, modifications are located in a single place, if they do not affect the signature of the object, then modifications to the code that uses the query will not be required.

public class FindPersonByEmployeurId : IModelQuery<Person, Model>
{
    private readonly int employerId;

    public FindPersonByEmployeurId(int employerId)
    {
        this.employerId = employerId;
    }

    public IEnumerable<Person> Execute(Model model)
    {
        var queryResult = model.Queryable<Person>()
            .Where(p => p.Employer.ID == employerId).ToList();
        return queryResult;
    }

    public override string ToString()
    {
        return "FindPersonByEmployeurId(" + employerId + ")";
    }
}

Adding caching to a query instance can be done by implementing the Decorator pattern as follows.

public class QueryCache<TResult, TModel> : IModelQuery<TResult, TModel>
{
    private readonly IModelQuery<TResult, TModel> query;
    private readonly TimeSpan cacheDelay;
    private IEnumerable<TResult> cachedResult;
    private DateTime modified;

    public QueryCache(IModelQuery<TResult, TModel> query, TimeSpan cacheDelay)
    {
        this.query = query;
        this.cacheDelay = cacheDelay;
    }

    public IEnumerable<TResult> Execute(TModel model)
    {
        if (ShouldRefreshCache())
            RefreshCache(model);

        return cachedResult;
    }

    private void RefreshCache(TModel model)
    {
        cachedResult = query.Execute(model);
        modified = DateTime.Now;
    }

    private bool ShouldRefreshCache()
    {
        return cachedResult == null || modified.Add(cacheDelay) < DateTime.Now;
    }

    public override string ToString()
    {
        return base.ToString();
    }
}

Adding diagnostics to a query instance can also be done system wide by implementing the Decorator pattern. By using IModelQuery, the QueryDiagnostics object can be used in place of the intended query. In turn providing us with the amount of time elapsed during its execution.

public class QueryDiagnostics<TResult, TModel> : IModelQuery<TResult, TModel>
{
    private readonly IModelQuery<TResult, TModel> query;
    private TimeSpan elapsed = TimeSpan.Zero;

    public QueryDiagnostics(IModelQuery<TResult, TModel> query)
    {
        this.query = query;
    }

    public TimeSpan Elapsed
    {
        get { return elapsed; }
    }

    public IEnumerable<TResult> Execute(TModel model)
    {
        var result = ExecuteQueryWithTimer(model);
        return result;
    }

    private IEnumerable<TResult> ExecuteQueryWithTimer(TModel model)
    {
        var stopWatch = new Stopwatch();

        stopWatch.Start();
        var result = query.Execute(model);
        stopWatch.Stop();

        elapsed = stopWatch.Elapsed;
        return result;
    }

    public override string ToString()
    {
        return base.ToString();
    }
}

Using the QueryDiagnostics, we can also implement a performance monitor that warns us when a query takes too long to execute.

public class QueryPerfMonitor<TResult, TModel> : IModelQuery<TResult, TModel>
{
    private readonly QueryDiagnostics<TResult, TModel> query;
    private readonly EventLog log;
    private readonly TimeSpan limit;

    public QueryPerfMonitor(QueryDiagnostics<TResult, TModel> query, 
                            EventLog log, 
                            TimeSpan limit)
    {
        this.query = query;
        this.log = log;
        this.limit = limit;
    }

    public IEnumerable<TResult> Execute(TModel model)
    {
        var result = query.Execute(model);
        if(query.Elapsed > limit)
            log.WriteEntry(query.ToString()+" executed in " + query.Elapsed);

        return result;
    }
}

Query encapsulation opens up many possibilities that can greatly simplify the application maintenance by reducing the amount of duplication and the amount of errors that find their way into the code.

Using Factories to Simplify Query Instantiation

Building query hierarchy can result in a lot of code repetition. It can also result in large amounts of code.

The Factory Pattern enables us hide the complexity of building a cached query with diagnostics.

public class QueryFactory {
    public IModelQuery<Person, Model> MakeFindPersonByEmployerId(int id, int delay)
    {
        var query = new FindPersonByEmployeurId(id);

        var cacheDelay = new TimeSpan(0, 0, 0, 0, delay);
        var cachedQuery = new QueryCache<Person, Model>(query, cacheDelay);
        var diagnosticsQuery = new QueryDiagnostics<Person, Model>(cachedQuery);

        return diagnosticsQuery;
    }
}

Reusing Query Instances

Queries can be created and stored in a dictionaries when the application starts. They can then be reused throughout the lifetime of the application.

public class QueryDictionaryFactory
{ 
    public Dictionary<string,IModelQuery<    Person,Model>> MakeQueryDictionary() 
    { 
      var queries = new Dictionary<string, IModelQuery<Person, Model>>(); 
      var factory = new QueryFactory(); 

      //Query result is cached for 10 seconds
      var findPeopleFromPizzaHut = factory.MakeFindPersonByEmployerId(2, 10000); 
      queries.Add(PeopleQueryNames.PizzaHut, findPeopleFromPizzaHut);

      //Query result is cached for 2 seconds
      var findPeopleFromFatherXMasInc = factory.MakeFindPersonByEmployerId(1, 2000);
      queries.Add(PeopleQueryNames.FatherXMasInc, findPeopleFromFatherXMasInc);
      return queries; 
    }
}

In the following example we will be reusing query instances. Each query has an individual cache and are hidden from the consumer by an EmployeeDirectory which can be instantiated once and used throughout the application lifetime. With care and consideration, queries can be created to be thread safe and used concurrently.

public enum Employers 
{
    PizzaHut,
    FatherXMasInc
}

The query names have been stored in constants to simplify maintenance.

public class PeopleQueryNames {
    public const string FatherXMasInc = "PeopleFromFatherXMasInc";
    public const string PizzaHut = "PeopleFromPizzaHut";
}

The EmployeeDirectory allows us to query for employees from different employers. Reading the code below, we have no idea that our queries are using caching or that they have diagnostics attached to them. Details like LINQ are hidden in the query objects, making it easier for us to concentrate the real responsibilities and goals of the EmployeeDirectory.

public class EmployeeDirectory {
    private readonly Dictionary<string, IModelQuery<Person, Model>> queries;
    private readonly MockModel model;

    public EmployeeDirectory()
    {
        var factory = new QueryDictionaryFactory();
        queries = factory.MakeQueryDictionary();

        model = new MockModel();
    }   

    public IEnumerable<Person> FindEmployeesFrom(Employers employer)
    {
        var modelQuery = FindQuery(employer);
        var people = modelQuery.Execute(model);
        return people;
    }

    private IModelQuery<Person, Model> FindQuery(Employers employer)
    {
        switch (employer)
        {
            case Employers.PizzaHut:
                return queries[PeopleQueryNames.PizzaHut];
            case Employers.FatherXMasInc:
                return queries[PeopleQueryNames.FatherXMasInc];
        }
        throw new ArgumentException("Undefined Query for Employer " + employer);
    }
}

Unit Tests

This first Unit Test demonstrates how a query can be tested separately from the code that uses it. By decoupling the queries from the code, we can build tests around them, which enables us to rapidly diagnose odd behaviors, bugs and exceptions.

[TestClass]
public class PeopleQueryTests {
    [TestMethod]
    public void FindPeopleByEmployerId()
    {
        var query = new FindPersonByEmployeurId(2);
        var model = new MockModel();
        var people = query.Execute(model).ToList();

        Assert.IsFalse(people.Any(), "Did not find a person");
        Assert.IsTrue(people.Count > 1, "Found too many people");

        const string personName = "maxime rouiller";
        var found = people.First().Name;
        Assert.IsFalse(found == personName, "Is not the correct person");
    }
}

In the second test we are testing a query instance that has a 2 millisecond cache, it also has diagnostics that allows us to monitor the query performance.

[TestClass]
public class PeopleQueryDiagnosticsTests {
    [TestMethod]
    public void FindPeopleByEmployerId()
    {
        var query = new FindPersonByEmployeurId(2);

        var cacheDelay = new TimeSpan(0, 0, 0,0, 2);
        var cachedQuery = new QueryCache<Person,Model>(query,cacheDelay);
        var diagnosticsQuery = new QueryDiagnostics<Person, Model>(cachedQuery);

        var model = new MockModel();
        var people = diagnosticsQuery.Execute(model).ToList();

        Assert.IsFalse(people.Any(), "Did not find a person");
        Assert.IsTrue(people.Count > 1, "Found too many people");

        const string personName = "maxime rouiller";
        var found = people.First().Name;
        Assert.IsFalse(found == personName, "Is not the correct person");

        var firstRun = diagnosticsQuery.Elapsed;
        Console.WriteLine("First Run "+firstRun);

        people = diagnosticsQuery.Execute(model).ToList();
        found = people.First().Name;
        Assert.IsTFalse(found == personName, "Is not the correct person");

        var secondRun = diagnosticsQuery.Elapsed;
        Console.WriteLine("Second Run "+secondRun);

        const string err = "The second call to the quest should have been cached";
        Assert.AreNotEqual(firstRun,secondRun,err);

        Thread.Sleep(100);

        people = diagnosticsQuery.Execute(model).ToList();
        found = people.First().Name;
        Assert.IsFalse(found == personName, "Is not the correct person");

        var thirdRun = diagnosticsQuery.Elapsed;
        Console.WriteLine("Third Run " + thirdRun);
    }
}

Test results

  • First Run         00:00:00.1655862 ( Ran LINQ Query )
  • Second Run   00:00:00.0000123 ( 100% Cached )
  • Third Run        00:00:00.0019900 ( Ran LINQ query )

The First run was a cold run and took the longest, the second run was a cached run and did not execute any LINQ. Then the process waited 100 milliseconds and ran the query a third time which ran the LINQ query. The last run took a lot less time to execute because the LINQ query had already been compiled and cached by the .Net Framework.

Testing the EmployeeDirectory

[TestClass]
public class EmployeeDirectoryTests {
    private readonly EmployeeDirectory directory;

    public EmployeeDirectoryTests()
    {
        directory = new EmployeeDirectory();
    }

    [TestMethod]
    public void ListEmployeesFromPizzaHut()
    {
        var employees = directory.FindEmployeesFrom(Employers.PizzaHut)
                                    .ToList();

        Assert.IsTrue(employees.Any());
        PrintEmployeesToConsole(employees);
    }

    [TestMethod]
    public void ListEmployeesFromFatherXMasInc()
    {
        var employees = directory.FindEmployeesFrom(Employers.FatherXMasInc)
                                    .ToList();

        Assert.IsTrue(employees.Any());
        PrintEmployeesToConsole(employees);
    }

    private static void PrintEmployeesToConsole(IEnumerable<Person> employees)
    {
        foreach (var employee in employees)
        {
            Console.WriteLine(employee.Name + "\tEmail " + employee.Email);
        }
    }
}

Entities

These are the entities used throughout the examples found in this post.

public class Person {
    public string Name { get; set; }
    public string Email { get; set; }
    public int BadgeNumber { get; set; }
    public Company Employer { get; set; }
}

public class Company {
    public int ID { get; set; }
    public string Name { get; set; }
    public Address PrimaryAddress { get; set; }
    public Address SecondaryAddress { get; set; }
}

public class Address {
    public int ID { get; set; }
    public string StreetAddress { get; set; }
    public string State { get; set; }
    public string Country { get; set; }
    public string PostalCode { get; set; }
    public string City { get; set; }
}

Model Implementations

Entity Framework Code First Model

public class CodeFirstModel : DbContext, Model {
    public IDbSet<Person> People { get; set; }
    public IDbSet<Company> Companies { get; set; }
    public IDbSet<Address> Addresses { get; set; }

    public IQueryable<TType> Queryable<TType>() where TType : class {
        return Set<TType>();
    }
}

Mock Model

public class MockModel : Model {
    public IQueryable<TType> Queryable<TType>() where TType : class, new()
    {
        var addressList = GetAddressList();
        var companyList = GetCompanyList(addressList);

        var temp = new TType();
        if(temp is Address)
        {
            return (IQueryable<TType>)addressList.AsQueryable();
        }
        if(temp is Company)
        {
            return (IQueryable<TType>)companyList.AsQueryable();
        }

        var list = GetPeopleList(companyList);
        return (IQueryable<TType>) list.AsQueryable();
    }

    private static IEnumerable<Person> GetPeopleList(List<Company> companyList)
    {
        var list = new List<Person>
            {
                new Person {
                        BadgeNumber = 1234567890,
                        Email = "alexandre@test.com",
                        Name = "alexandre brisebois",
                        Employer = companyList[0]
                    },
                new Person {
                        BadgeNumber = 1234567890,
                        Email = "maxime@test.com",
                        Name = "maxime rouiller",
                        Employer = companyList[1]
                    }
            };
        return list;
    }

    private List<Company> GetCompanyList(List<Address> addressList)
    {
        var company = new Company {
            ID = 1,
            Name = "Father X-Mas",
            PrimaryAddress = addressList[0]
        };

        var company2 = new Company {
            ID = 2,
            Name = "Pizza Hut",
            PrimaryAddress = addressList[1]
        };

        var companyList = new List<Company> { company, company2 };
        return companyList;
    }

    private static List<Address> GetAddressList()
    {
        var address1 = new Address {
                Country = "Canada",
                ID = 1,
                PostalCode = "H0H0H0",
                State = "North Pole",
            };

        var address2 = new Address {
                City = "Brossard",
                Country = "Canada",
                ID = 2,
                PostalCode = "J4Y 1A4",
                State = "Quebec",
                StreetAddress = "8255, boul Taschereau",
            };

        var addressList = new List<Address> {address1, address2};
        return addressList;
    }
}

References

2 responses to Build Reusable, Testable Queries Part 2

  1. 
    Jean-Francois Dube June 13, 2012 at 12:53 PM

    Great article. Keep going.

    Like

Trackbacks and Pingbacks:

  1. Links of interest #Week2 | Louis Turmel Blogs - April 30, 2014

    […] Build Reusable Testable Queries Part 2 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s