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.
QueryTests
to 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()); } } }
BusinessLogic
project add a new C# class named Blog
with 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; } } }
Queries
to the DataAccess
project and add a new C# class to it named BlogQueries
with 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)); } } }
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.