Improving complex where clauses

In this recipe, we will be composing queries out of reusable chunks into larger business specific queries.

Getting ready

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.

How to do it...

Carry out the following steps in order to accomplish this recipe.

  1. We will start by writing a test, which will query the database with a complex set of 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());
    }
  }
 
}
  2. In the 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; }
  }
}
  3. Add a 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");
    }

  }
}
  4. Modify the 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();
      }
  }
}
  5. We then want to add a 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));
    }
  }
}
  6. We want to add a new C# class to the 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);
    }
  }

}
  7. Run our test and everything will pass.

How it works...

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.

There's more...

We have leveraged several areas that can be handled in more detail and would give greater benefit with a deeper look.

Compose-able LINQ

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.

See also

In this chapter:

  • Implementing the specification pattern recipe
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset