Composed queries are an effective way to link separate but related pieces of filtering logic into reusable pieces. In this recipe, we will compose queries together to accomplish a certain filter without needing to write the code twice but still having it translated into a single SQL execution.
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 Transaction Scope 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.Data.Entity.Validation; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Transactions; using BusinessLogic; using BusinessLogic.Queries; using DataAccess; using DataAccess.Database; using Microsoft.VisualStudio.TestTools.UnitTesting; using Test.Properties; using System.Data.Entity; namespace Test { [TestClass] public class QueryTests { [TestMethod] public void ShouldReturnRecordsFromTheDatabaseByName() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); IBlogRepository repo = new BlogRepository(context); //Act var items = repo.Set<Blog>().FilterByBlogName("Test"); //Assert Assert.AreEqual(2, items.Count()); } [TestMethod] public void ShouldReturnRecordsFromTheDatabaseByDescription() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); IBlogRepository repo = new BlogRepository(context); //Act var items = repo.Set<Blog>().FilterByBlogName("es"); //Assert Assert.AreEqual(2, items.Count()); } [TestMethod] public void ShouldReturnRecordsFromTheDatabaseByBoth() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); IBlogRepository repo = new BlogRepository(context); //Act var items = repo.Set<Blog>() .FilterByNameAndDescription("Test","es"); //Assert Assert.AreEqual(1, items.Count()); } } }
DataAccess project Database
folder with the following code to set up data:using System; using System.Data.Entity; using BusinessLogic; namespace DataAccess.Database { public class Initializer : DropCreateDatabaseAlways<BlogContext> { public Initializer() { } protected override void Seed(BlogContext context) { context.Set<Blog>().Add(new Blog() { Creationdate = DateTime.Now, ShortDescription = "not this one", Title = "Test Blog" }); context.Set<Blog>().Add(new Blog() { Creationdate = DateTime.Now, ShortDescription = "Testing", Title = "Test Blog 2" }); context.Set<Blog>().Add(new Blog() { Creationdate = DateTime.Now, ShortDescription = "Testing", Title = "not Blog" }); context.SaveChanges(); } } }
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; } } }
Mapping
folder to the DataAccess
project and then add a BlogMapping
class to the folder with the following code:using System.ComponentModel.DataAnnotations; using System.Data.Entity.ModelConfiguration; using BusinessLogic; namespace DataAccess.Mappings { public class BlogMapping : EntityTypeConfiguration<Blog> { public BlogMapping() { this.ToTable("Blogs"); this.HasKey(x => x.Id); this.Property(x => x.Id).HasDatabaseGeneratedOption (DatabaseGeneratedOption.Identity) .HasColumnName("BlogId"); this.Property(x => x.Title) .IsRequired().HasMaxLength(250); this.Property(x => x.Creationdate) .HasColumnName("CreationDate").IsRequired(); this.Property(x => x.ShortDescription) .HasColumnType("Text").IsMaxLength().IsOptional() .HasColumnName("Description"); } } }
BlogContext
class to contain the new mappings for Blogs
with the following code:using System; using System.Data.Entity; using System.Linq; using BusinessLogic; using DataAccess.Mappings; namespace DataAccess { public class BlogContext : DbContext, IUnitOfWork { public BlogContext(string connectionString) : base(connectionString) { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new BlogMapping()); base.OnModelCreating(modelBuilder); } public IQueryable<T> Find<T>() where T : class { return this.Set<T>(); } public void Refresh() { this.ChangeTracker.Entries().ToList() .ForEach(x=>x.Reload()); } public void Commit() { this.SaveChanges(); } } }
Queries
to the BusinessLogic
project and add a new C# class to it named BlogQueries
with the following code:using System; using System.Linq; namespace BusinessLogic.Queries { public static class BlogQueries { public static IQueryable<Blog> FilterByBlogName(this IQueryable<Blog> items, string name) { return items.Where(x => x.Title.Contains(name)); } public static IQueryable<Blog> FilterByDescription(this IQueryable<Blog> items, string description) { return items.Where(x=> x.ShortDescription .Contains(description)); } public static IQueryable<Blog> FilterByNameAndDescription(this IQueryable<Blog> items, string name, string description) { return items.FilterByBlogName(name) .FilterByDescription(description); } } }
We begin our solution by defining a set of tests that apply filters one at a time and then in combination, and finally test the results. This ensures that we accomplish the functionality that we are aiming at without breaking any individual piece.
We initialize the database with a set of test data which we can assert against for the query filtering. This allows us to know the inputs and test if the outputs adhere to the expected results.
We then set up our blog entity, the mappings for it, and the DbContext
, so we could communicate with the database and have our filters translated properly into SQL statements. This is the piece that allows us to ignore the database schema and worry only about the object filtering.
The next step is to define our three queries, so we can test them. Notice that the first two queries are the queries that specify a where statement. However, the third query merely composes the already defined behavior into a single execution without the developer being aware that it combines with the existing functionality. This allows us to adhere to the DRY principle.
We leverage several principles here, which will help us in understanding how to implement this in a production environment.
Each piece of functionality should not need to be typed more than once in an application. This is an ideal that if held to, will force us to abstract our code base into patterns that lend themselves to reuse. The effort is well worth it though, as it helps to ensure that each piece is doing just one thing, which helps us to adhere to single responsibility principle.
This principle states that each method and class should have one and only one reason to change. This normally confuses some developers, but the confusion lies in how we define "one reason to change." At the method level, the reason to change should be very specific. For example, "The rules for this filter are now different." However, at a class level, this one reason should be more generalized. For example, "We have added another filter." This one reason to change is essential for our one reason for creating any class or method to begin with. This means that the consumers of the object will never be surprised by a change because it will be in line with what they would expect from your component.
Some people believe that Object Relational Mappers (ORMs), such as Entity Framework, intrinsically break this principle because an entity class might change because of the object or the database. However, as we have already learned, this is not the case. Entity Framework clearly separates mapping rules from the classes, thereby avoiding this conflict with the single responsibility principle.