One of the greatest benefits of well-defined abstractions in code is that we do not need to have an implementation before we begin to consume a component. The interface alone, in our case IQueryable<>, is sufficient to begin developing and testing the queries that we will use.
We will be using the NuGet package manager to install the Entity Framework 4.1 assemblies.
The package installer can be found at http://nuget.org.
We will also be using a database for connecting to and updating data.
Open the Improving Query Testing solution in the included source code examples.
Carry out the following steps in order to accomplish this recipe.
- We will start by adding a new unit test named
QueryTeststo the test project. We will make a test that connects to the database and retrieves a couple of records with a reusable query using the following code:using System; using System.Collections.Generic; using System.Linq; using BusinessLogic; using BusinessLogic.Queries; using Microsoft.VisualStudio.TestTools.UnitTesting; namespace Test { [TestClass] public class QueryTests { [TestMethod] public void ShouldFilterTestData() { //Arrange IQueryable<Blog> items = new List<Blog> { new Blog() { Creationdate = DateTime.Now, ShortDescription = "Test", Title = "Test" }, new Blog() { Creationdate = DateTime.Now, ShortDescription = "not this one", Title = "Blog" }, new Blog() { Creationdate = DateTime.Now, ShortDescription = "not this", Title = "TeBlog" }, new Blog() { Creationdate = DateTime.Now, ShortDescription = "not this one", Title = "TestBlog" } }.AsQueryable(); //Act var returnedValues = items.FilterByBlogName("Test"); //Assert Assert.AreEqual(2, returnedValues.Count()); } } } - In the
BusinessLogicproject add a new C# class namedBlogwith the following code:using System; using System.ComponentModel.DataAnnotations; using System.Text.RegularExpressions; namespace BusinessLogic { public class Blog { public int Id { get; set; } public DateTime Creationdate { get; set; } public string ShortDescription { get; set; } public string Title { get; set; } public double Rating { get; set; } } } - Add a new folder named
Queriesto theDataAccessproject and add a new C# class to it namedBlogQuerieswith the following code:using System.Linq; namespace DataAccess.Queries { public static class BlogQueries { public static IQueryable<Blog> FilterByBlogName(this IQueryable<Blog> items, string name) { return items.Where(x => x.Title.Contains(name)); } } } - Run our test and it will work.
We start our solution, like we usually do, with a test that communicates the intended feature and serves to mark the completion point for us. In this case, the test is to check if we can exercise our queries without communicating with a database. The trick here is to create a list in the memory of Blogs and then use the AsQueryable() method to get that list into a queryable form to exercise the filters.
We will set up our blog object, so we have something that we can use our filters to test against. This allows us to use the same objects that would be stored to test the filters, instead of defining inherited mock objects.
We then define our query against an IQueryable<T> of Blog, the same as we would use against the database.
By defining our queries and objects in this way, we have created a testable framework for database queries. However, there are a few things to keep in mind.
IQueryable allows us to continue to add more statements before translating the expression into SQL. This is our explicit way of telling other developers that it is ok to continue to add filters and joins to this statement before it goes to the database. This will allow the building of large complex statements that do heavy lifting in the database server. However, this ability comes at the risk of a loss of control by introducing the possibility that a combination of filters and joins that are not at all performant might be created. Our previous recipe, Improving performance with code access, specifically addresses this risk.
IQueryable supports deferred execution, so we will not get records until they are required, and all of the statements are then translated to SQL and executed on the database server.
IEnumerable does not allow further query composition to be translated into SQL. Basically, the SQL is frozen at that point but you can tack on further in memory processing. When we will be writing our query libraries, we will want to be sure that we use each of these libraries when they are called for. If we have an expensive projection, or a joined statement that we do not want to allow any further joins to be added to, then we need to return IEnumerable. This is our explicit way of communicating that we intended this code to come into memory in a certain way, and any further processing is to be done in the memory. This is especially handy if you need a SQL statement frozen at a certain point, so you can do something that does not translate to SQL such as type checking.
IEnumerable supports deferred execution, but it executes the SQL statement in the state that it was in when it became an IEnumerable and then proceeds to process all of the further composed tasks in memory on the objects loaded.