In this chapter, we will cover:
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.
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.
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.
Carry out the following steps in order to accomplish this recipe.
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; } } }
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 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(); } } }
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"); } } }
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) }; } } }
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 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, 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.
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.