Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 6. Improving Complex Query Scenarios

In this chapter, we will cover:

Improving dynamic sorting
Grouping at runtime without Lambas
Handling explicit loading
Improving complex where clauses
Implementing the specification pattern

Introduction

When we leverage Entity Framework, we gain a huge amount of flexibility and power. This power and flexibility make it easy to get data into and out of simple to moderately complex database structures in many ways. We can get a very nice API for simple queries. However, when our business problems get more complex, we need different patterns to handle this increasing complexity. These patterns range from minor adjustments to how we leverage code, to major refactoring of the data access layer. We will walk through these together, but each one will need to be weighed against the needs of our applications.

Improving dynamic sorting

In this recipe, we will be leveraging a string or set of strings to sort a list, much like you would sort a list based on a post back from an HTML page.

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 Dynamic Sorting solution in the included source code examples.

How to do it...

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

We will start by adding a new unit test named SortingTests to the test project. We will make a test that sorts some TestObject data in memory by property name and then returns the proper order with the following code:

using System.Collections.Generic;
using System.Linq;
using BusinessLogic;
using BusinessLogic.Queries;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Test
{
  [TestClass]
  public class SortExtensionTest
  {
    [TestMethod]
    public void CanSortWithOnlyStrings()
    {
      IQueryable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
        
      }.AsQueryable();

      Assert.AreEqual(2, items.OrderBy("id", "ASC")
        .ToArray()[1].id);
    }

    [TestMethod]
    public void CanSortDescendingWithOnlyString()
    {
      IQueryable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
                                
      }.AsQueryable();

      Assert.AreEqual(1, items.OrderBy("id", "DSC")
        .ToArray()[2].id);

    }

    [TestMethod]
    public void CanSortMultipleTimesAscending()
    {
      IQueryable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
        new TestObject(){id = 2, Test = "Test1"} 
                                
      }.AsQueryable();
      var item = items.OrderBy("id", "ASC")
        .ThenBy("Test", "ASC").ToArray()[3];
      Assert.AreEqual("Test3", item.Test);
    }

    [TestMethod]
    public void CanSortMultipleTimesWithMultipleDirrections()
    {
      IQueryable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
        new TestObject(){id = 2, Test = "Test1"} 
                                
      }.AsQueryable();
      var item = items.OrderBy("id", "ASC")
        .ThenBy("Test", "DSC").ToArray()[3];
      Assert.AreEqual("Test1", item.Test);

    }

    [TestMethod]
    public void CanSortFromAListOfStrings()
    {
      //Arrange
      IQueryable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1",
          Test2 = "Test1"}, 
        new TestObject(){id = 2, Test = "Test3",
          Test2 = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3",
          Test2 = "Test2"}, 
        new TestObject(){id = 4, Test = "Test3",
          Test2 = "Test3"}, 
        new TestObject(){id = 5, Test = "Test1",
          Test2 = "Test2"} 
                                
      }.AsQueryable();

      var strings = new[] { "Test", "Test2" };
      var dirrection = "ASC";
      //Act
      var orderedQuery = items.OrderBy("ASC", strings);

      //Assert
      var item = orderedQuery.FirstOrDefault();
      var thirdItem = orderedQuery.ToArray()[2];
      Assert.AreEqual(1, item.id);
      Assert.AreEqual(2, thirdItem.id);
    }

    [TestMethod]
    public void CanEnumerableSortWithOnlyStrings()
    {
      IEnumerable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
      };

    Assert.AreEqual(2, items.OrderBy("id", "ASC")
      .ToArray()[1].id);
    }

    [TestMethod]
    public void CanEnumerableSortDescendingWithOnlyString()
    {
      IEnumerable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
                                
      };

      Assert.AreEqual(1, items.OrderBy("id", "DSC")
        .ToArray()[2].id);

    }

    [TestMethod]
    public void CanEnumerableSortMultipleTimesAscending()
    {
      IEnumerable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
        new TestObject(){id = 2, Test = "Test1"} 
                                
      };
      var item = items.OrderBy("id", "ASC")
        .ThenBy("Test", "ASC").ToArray()[3];
      Assert.AreEqual("Test3", item.Test);
    }

    [TestMethod]
    public void 
      CanEnumerableSortMultipleTimesWithMultipleDirrections()
    {
      IEnumerable<TestObject> items = new List<TestObject>() 
      { 
        new TestObject(){id = 1, Test = "Test1"}, 
        new TestObject(){id = 3, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test3"}, 
        new TestObject(){id = 2, Test = "Test2"}, 
        new TestObject(){id = 2, Test = "Test1"} 
                                
      };
      var item = items.OrderBy("id", "ASC")
        .ThenBy("Test", "DSC").ToArray()[3];
            Assert.AreEqual("Test1", item.Test);

    }

    [TestMethod]
    public void CanEnumerableSortFromAListOfStrings()
    {
    //Arrange
    IEnumerable<TestObject> items = new List<TestObject>() 
    { 
      new TestObject(){id = 1, Test = "Test1",
        Test2 = "Test1"}, 
      new TestObject(){id = 2, Test = "Test3",
        Test2 = "Test1"}, 
      new TestObject(){id = 3, Test = "Test3",
        Test2 = "Test2"}, 
      new TestObject(){id = 4, Test = "Test3",
        Test2 = "Test3"}, 
      new TestObject(){id = 5, Test = "Test1",
        Test2 = "Test2"} 
                                
    };

    var strings = new[] { "Test", "Test2" };
    var dirrection = "ASC";
    //Act
    var orderedQuery = items.OrderBy("ASC", strings);

    //Assert
    var item = orderedQuery.FirstOrDefault();
    var thirdItem = orderedQuery.ToArray()[2];
    Assert.AreEqual(1, item.id);
    Assert.AreEqual(2, thirdItem.id);
  }
}

  public class TestObject
  {
    public int id { get; set; }
    public string Test { get; set; }
    public string Test2 { get; set; }
    public string Test3 { get; set; }
    public string Test4 { get; set; }
  }
}

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; }
  }
}

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");
    }

  }
}

Modify the BlogContext class to contain the new mappings and a DbSet property 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();
    }
  }
}

Add a new folder named Queries to the DataAccess project and add a new C# class to it named SortExtensions with the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace BusinessLogic.Queries
{
  public static class SortingExtension
  {
    private const string Ascending = "ASC";

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property, string dirrection)
    {
      return dirrection == Ascending ? source.OrderBy(property) : source.OrderByDescending(property);
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property)
    {
      return ApplyOrder(source, property, "OrderBy");
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, OrderByParameter parameter)
    {
      return ApplyOrder(source, parameter.Property,
        parameter.Dirrection == Ascending ? "OrderBy"
           : "OrderByDescending");
    }

    public static IOrderedQueryable<T> OrderBy<T>
      (this IQueryable<T> source, string dirrection,
        params string[] properties)
    {
      if (properties.Length == 0)
       throw new InvalidOperationException
        ("Cannot Sort based on an Empty List of parameters");

      IOrderedQueryable<T> orderedQuery = null;
      for (int i = 0; i < properties.Count(); i++)
      {
        if (i == 0) orderedQuery = 
          source.OrderBy(properties[i], dirrection);
        else orderedQuery.ThenBy(properties[i], dirrection);
      }
      return orderedQuery;
    }

    public static IOrderedQueryable<T> OrderBy<T>
      (this IQueryable<T> source,
        params OrderByParameter[] parameters)
    {
      if (parameters.Length == 0)
      throw new InvalidOperationException("Cannot Sort based
        on an Empty List of parameters");

      IOrderedQueryable<T> orderedQuery = null;
      for (int i = 0; i < parameters.Count(); i++)
      {
        if (i == 0) orderedQuery = 
          source.OrderBy(parameters[i]);
                else orderedQuery.ThenBy(parameters[i]);
      }
      return orderedQuery;
    }

    public static IOrderedQueryable<T> 
      OrderByDescending<T>(this IQueryable<T> source,
        string property)
    {
      return ApplyOrder(source, property, "OrderByDescending");
    }

    public static IOrderedQueryable<T> 
      OrderByDescending<T>(this IQueryable<T> source,
        params OrderByParameter[] parameters)
    {
      if (parameters.Length == 0)
      throw new InvalidOperationException("Cannot Sort based
        on an Empty List of parameters");

      IOrderedQueryable<T> orderedQuery = null;
      for (int i = 0; i < parameters.Count(); i++)
      {
        if (i == 0) orderedQuery = 
          source.OrderByDescending(parameters[i]);
        else orderedQuery.ThenByDescending(parameters[i]);
      }
      return orderedQuery;
    }

    public static IOrderedQueryable<T> ThenBy<T>
      (this IOrderedQueryable<T> source,
        OrderByParameter parameter)
    {
      return ApplyOrder(source, parameter.Property,
      parameter.Dirrection == Ascending ? "OrderBy" 
        : "OrderByDescending");
    }

    public static IOrderedQueryable<T> ThenBy<T>
      (this IOrderedQueryable<T> source, string property,
        string dirrection)
    {
      return dirrection == Ascending ? source.ThenBy(property)
        : source.ThenByDescending(property);
    }

    public static IOrderedQueryable<T> ThenBy<T>
      (this IOrderedQueryable<T> source, string property)
    {
      return ApplyOrder(source, property, "ThenBy");
    }
    public static IOrderedQueryable<T> ThenByDescending<T>
      (this IOrderedQueryable<T> source, string property)
    {
      return ApplyOrder(source, property, "ThenByDescending");
    }

    private static IOrderedQueryable<T> 
      ApplyOrder<T>(IQueryable<T> source, string property,
        string methodName)
    {
      string[] props = property.Split('.'),
      Type type = typeof(T);
      ParameterExpression arg = Expression
        .Parameter(type, "x");
      Expression expr = arg;
      foreach (PropertyInfo pi in props.Select(prop => 
        type.GetProperty(prop)))
      {
        expr = Expression.Property(expr, pi);
        type = pi.PropertyType;
      }
      Type delegateType = typeof(Func<,>)
        .MakeGenericType(typeof(T), type);
      LambdaExpression lambda = Expression.Lambda
        (delegateType, expr, arg);

      object result = typeof(Queryable).GetMethods().Single(
      method => method.Name == methodName
        && method.IsGenericMethodDefinition
          && method.GetGenericArguments().Length == 2
            && method.GetParameters().Length == 2)
              .MakeGenericMethod(typeof(T), type)
                .Invoke(null, new object[] { source, lambda });
      return (IOrderedQueryable<T>)result;
    }

    public static IOrderedQueryable<T> OrderBy<T>
      (this IEnumerable<T> source, string property,
         string dirrection)
    {
      return dirrection == Ascending ?
        source.AsQueryable().OrderBy(property)
          : source.AsQueryable().OrderByDescending(property);
    }
    public static IOrderedQueryable<T> OrderBy<T>
      (this IEnumerable<T> source, string property)
    {
      return ApplyOrder(source.AsQueryable(),
        property, "OrderBy");
    }

    public static IOrderedQueryable<T> OrderBy<T>
      (this IEnumerable<T> source,
        OrderByParameter parameter)
    {
      return ApplyOrder(source.AsQueryable(),
        parameter.Property,
      parameter.Dirrection == Ascending ? "OrderBy"
        : "OrderByDescending");
    }

    public static IOrderedQueryable<T> OrderBy<T>
      (this IEnumerable<T> source, string dirrection,     params string[] properties)
    {
      if (properties.Length == 0)
      throw new InvalidOperationException("Cannot Sort
        based on an Empty List of parameters");

      IOrderedQueryable<T> orderedQuery = null;
      for (int i = 0; i < properties.Count(); i++)
      {
        if (i == 0) orderedQuery = source.AsQueryable()
          .OrderBy(properties[i], dirrection);
        else orderedQuery.ThenBy(properties[i], dirrection);
      }
      return orderedQuery;
    }

    public static IOrderedQueryable<T>
      OrderBy<T>(this IEnumerable<T> source,
        params OrderByParameter[] parameters)
    {
      if (parameters.Length == 0)
      throw new InvalidOperationException("Cannot Sort
        based on an Empty List of parameters");

        IOrderedQueryable<T> orderedQuery = null;
        for (int i = 0; i < parameters.Count(); i++)
        {
          if (i == 0) orderedQuery =
            source.AsQueryable().OrderBy(parameters[i]);
          else orderedQuery.ThenBy(parameters[i]);
        }
      return orderedQuery;
    }

    public static IOrderedQueryable<T>
      OrderByDescending<T>(this IEnumerable<T> source,
        string property)
    {
      return ApplyOrder(source.AsQueryable(),
        property, "OrderByDescending");
    }

    public static IOrderedQueryable<T>
      OrderByDescending<T>(this IEnumerable<T> source,
        params OrderByParameter[] parameters)
    {
      if (parameters.Length == 0)
      throw new InvalidOperationException("Cannot Sort
        based on an Empty List of parameters");

      IOrderedQueryable<T> orderedQuery = null;
      for (int i = 0; i < parameters.Count(); i++)
      {
        if (i == 0) orderedQuery =
          source.AsQueryable()
            .OrderByDescending(parameters[i]);
        else orderedQuery.ThenByDescending(parameters[i]);
      }
      return orderedQuery;
    }

    public static IOrderedQueryable<T> ThenBy<T>
      (this IOrderedEnumerable<T> source,
         OrderByParameter parameter)
    {
      return ApplyOrder(source.AsQueryable(),
        parameter.Property,
          parameter.Dirrection == Ascending ?
            "OrderBy" : "OrderByDescending");
    }

    public static IOrderedQueryable<T> ThenBy<T>
      (this IOrderedEnumerable<T> source,
        string property,
    string dirrection)
    {
      return dirrection == Ascending ?
        source.ThenBy(property)
          : source.ThenByDescending(property);
    }

    public static IOrderedQueryable<T> ThenBy<T>
      (this IOrderedEnumerable<T> source,
        string property)
    {
      return ApplyOrder(source.AsQueryable(),
        property, "ThenBy");
    }

    public static IOrderedQueryable<T> ThenByDescending<T>
      (this IOrderedEnumerable<T> source, string property)
    {
      return ApplyOrder(source.AsQueryable(),
        property, "ThenByDescending");
    }


  }
}

In the Queries folder, add a new C# class named OrderByParameter with the following code:

using System;

namespace BusinessLogic.Queries
{
  public class OrderByParameter
  {
    private const string Format = "{0}{1}";
    public string Dirrection { get; set; }
    public string Property { get; set; }

    private OrderByParameter()
    {

    }

    public static implicit operator string
      (OrderByParameter parameter)
    {
      return string.Format(Format, parameter.Property,
        parameter.Dirrection);
    }

    public static implicit operator OrderByParameter
      (string value)
    {
      if (value.Length < 4)
      throw new InvalidOperationException
        ("Cannot convert to OrderByParameter due to
          invalid string");
      return new OrderByParameter
      {
        Property = value.Substring(0, value.Length - 3),
        Dirrection = value.Substring(value.Length - 3)
      };
    }
  }
}

Run our test and it will work.

How it works...

We begin by setting up a suite of tests that will verify our sorting logic is full and complete without unintended side effects. This test serves as our safety net and our definition of done.

Once we have a test in place, we can move on to adding an object and mapping it to a context so we have a fully formed data access level. This will give us the ability to write integration tests to validate against a database if it is needed.

Once we have these structures in place, we can move on to adding the sorting extension methods which will make up the bulk of the logic in this recipe. These will be accepted in a queryable set of data, and apply a sort to it in the form of an expression. There are two big advantages to this, one is that we can compose these extension methods when needed and add them later. The second big advantage is the ability to have a deferred execution on something like dynamic sorting. We can create a sorting process, which not only accomplishes the goal but also has very few unintended side effects to the normal mode of execution.

After we have this logic in place, we need to add a parameter object in order to make the processing of string inputs easier. This wrapper will translate the incoming string into an actionable set of data. This ensures that the string parse logic is encapsulated and not a matter of concern for the sorting engine.

There's more...

There are several tools we can leverage in this recipe that are deep subjects. It will be a benefit to us if we can understand them thoroughly.

Expression Trees

Expression trees, at their most basic level, are a way of looking at executable code as data. We can use this to evaluate code, making decisions based on its parameters, or perform binary operations. This is tremendously helpful when translating executable code into SQL statements. This evaluation can be tedious and hard to learn, but once we understand, it gives unlimited cosmic power without the itty bitty living space.

When we are done with the code as data, we can simply compile the expression tree and execute it as code once again.

Deferred execution

When we leverage expression trees, we are in essence able to pass behavior around, and when it is finally needed it can be executed. This is a powerful concept that has built on years of delegate functions and pointers. Expression trees allow us to do this. We build and compose our statements, and then execute them at the last possible moment, or when we force them to execute. This is our choice and it gives us far greater control when data is accessed.

Encapsulation

Being able to separate the queries and the data allows us to gain a much higher level of encapsulation than we could previously achieve in standard data access. This encapsulation helps us gain more reusability without having to take on additional overhead.

Table of Contents for
6. Improving Complex Query Scenarios

Chapter 6. Improving Complex Query Scenarios

Introduction

Improving dynamic sorting

Getting ready

How to do it...

How it works...

There's more...

Expression Trees

Deferred execution

Encapsulation

See also

Table of Contents for 6. Improving Complex Query Scenarios

Create new playlist

Sign In

Sign Up

Chapter 6. Improving Complex Query Scenarios

Introduction

Improving dynamic sorting

Getting ready

How to do it...

How it works...

There's more...

Expression Trees

Deferred execution

Encapsulation

See also

Table of Contents for
6. Improving Complex Query Scenarios