In this recipe, we will be composing queries out of reusable chunks into larger business specific queries.
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 Complex Where Clauses solution in the included source code examples.
Carry out the following steps in order to accomplish this recipe.
where
clauses, which needs to be abstracted. Use the following code:using System; using System.Collections.Generic; using System.Linq; using BusinessLogic; using DataAccess.Queries; using Microsoft.VisualStudio.TestTools.UnitTesting; namespace Test { [TestClass] public class QueryTests { private static IQueryable<Blog> items; [ClassInitialize] public static void Setup(TestContext context) { //Arrange 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(); } [TestMethod] public void ShouldApplyAllFilters() { //Act var returnedValues = items.FilterByAll (x=>x.Title.Contains("e"), x=>x.Title.Contains("B")); //Assert Assert.AreEqual(2, returnedValues.Count()); } [TestMethod] public void ShouldApplyAnyFilter() { //Act var returnedValues = items.FilterByAny (x => x.Title.Contains("e"), x => x.Title.Contains("B")); //Assert Assert.AreEqual(4, 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; } } }
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
folder to the DataAccess
project and a new C# class named BlogQueries
with the following code:using System; using System.Linq; using System.Linq.Expressions; using BusinessLogic; namespace DataAccess.Queries { public static class BlogQueries { public static IQueryable<Blog> FilterByAll(this IQueryable<Blog> items, params Expression<Func<Blog,bool>>[] wheres) { return items.Where(ComplexWhereUtility.BuildAnd(wheres)); } public static IQueryable<Blog> FilterByAny(this IQueryable<Blog> items, params Expression<Func<Blog,bool>>[] wheres) return items.Where(ComplexWhereUtility.BuildOr(wheres)); } } }
Queries
folder in the DataAccess
project named ComplexWhereUtility
with the following code:using System; using System.Linq; using System.Linq.Expressions; // Usage added to from // http://blogs.msdn.com/b/meek/archive/2008/ 05/02/linq-to-entities-combining-predicates.aspx namespace DataAccess.Queries { public static class ComplexWhereUtility { public static Expression<T> Compose<T> (this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge) { // build parameter map (from parameters of second to parameters of first) var map = first.Parameters.Select((f, i) => new {f, s = second.Parameters[i]}) .ToDictionary(p => p.s, p => p.f); // replace parameters in the second lambda expression with parameters from the first var secondBody = ParameterRebinder .ReplaceParameters(map, second.Body); // apply composition of lambda expression bodies to parameters from the first expression return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters); } public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.And); } public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.Or); } public static Expression<Func<T, bool>> OrElse<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.Or); } public static Expression<Func<T, bool>> BuildAnd<T>(params Expression<Func<T, bool>>[] conditions) { return conditions.Aggregate<Expression<Func<T, bool>>, Expression<Func<T, bool>>>(null, (current, expression) => current == null ? expression : current.And(expression)); } public static Expression<Func<T, bool>> BuildOr<T>(params Expression<Func<T, bool>>[] conditions) { return conditions.Aggregate<Expression<Func<T, bool>>, Expression<Func<T, bool>>>(null, (current, expression) => current == null ? expression : current.Or(expression)); } public static Expression<Func<T, bool>> BuildOrElse<T>(params Expression<Func<T, bool>>[] conditions) { return conditions.Aggregate<Expression<Func<T, bool>>, Expression<Func<T, bool>>> (null, (current, expression) => current == null ? expression : current.OrElse(expression)); } } public class ParameterRebinder : ExpressionVisitor { private readonly Dictionary<ParameterExpression, ParameterExpression> map; public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map) { this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>(); } public static Expression ReplaceParameters (Dictionary<ParameterExpression, ParameterExpression> map, Expression exp) { return new ParameterRebinder(map).Visit(exp); } protected override Expression VisitParameter (ParameterExpression p) { ParameterExpression replacement; if (map.TryGetValue(p, out replacement)) { p = replacement; } return base.VisitParameter(p); } } }
As always, we start with a test that defines a set of data and a complex set of filters that we want to apply. We apply them and test the result against a known set of data. This allows us to define done and make sure we have accomplished the goal.
Once we have our test, we then need to set up the mapping and context to allow for a complete database and functional data access. Once this is done, we have a full database connection and a queryable repository.
We then create a query library, which leverages complex predicate builders that we can find in greater detail at http://blogs.msdn.com/b/meek/archive/2008/05/02/linq-to-entities-combining-predicates.aspx. This allows us to combine many predicates into an expression and then apply them in various configurations in order to easily adapt to the most complex business scenarios.
We have leveraged several areas that can be handled in more detail and would give greater benefit with a deeper look.
When LINQ is referred to as compose-able, it means we can build a query and then add additional operators to that query before it is executed at runtime. This allows us to use smaller discreet queries, which can build on one another and be combined to handle even the most complex business scenarios. When we compose a LINQ statement it is iterated only one time to get the results into memory.