In this recipe, we will be composing dynamically grouped sets of data into a queryable set that is functional and generic enough that it can be used against most domains.
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 groups some in memory data by property name and returns the proper grouped set with the following code:using System; using System.Collections.Generic; using System.Linq; using BusinessLogic; using BusinessLogic.Queries; using Microsoft.VisualStudio.TestTools.UnitTesting; namespace Test { [TestClass] public class QueryTests { [TestMethod] public void ShouldFilterTestData() { //Arrange IQueryable<Blog> items = new List<Blog> { new Blog() { Creationdate = DateTime.Now, ShortDescription = "Test", Title = "Test" }, new Blog() { Creationdate = DateTime.Now, ShortDescription = "not this one", Title = "Test" }, new Blog() { Creationdate = DateTime.Now, ShortDescription = "not this", Title = "TeBlog" }, new Blog() { Creationdate = DateTime.Now, ShortDescription = "not this one", Title = "TestBlog" } }.AsQueryable(); //Act var returnedValues = items.GroupByBlogTitle("Title"); //Assert Assert.AreEqual(2, returnedValues .First(x => x.Key.ToString() == "Test").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 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 Extensions
with the following code:using System; using System.Collections.Generic; using System.Linq; using System.Linq.Dynamic; using System.Linq.Expressions; using DynamicExpression = System.Linq.Dynamic.DynamicExpression; // These extensions are posted by Mitsu Furuta under the Micrsoft Public License // Found here -- http://blogs.msdn.com/b/mitsu/archive/2007/12/22/playing-with-linq-grouping-groupbymany.aspx // They are amazing work, and make any dynamic querying easier namespace DataAccess.DyanmicQueries { public static class Extensions { public static IEnumerable<GroupResult> GroupByMany<TElement>(this IEnumerable<TElement> elements, params string[] groupSelectors) { var selectors = new List<Func<TElement, object>>(groupSelectors.Length); foreach (var selector in groupSelectors) { LambdaExpression l = DynamicExpression .ParseLambda(typeof(TElement), typeof(object), selector); selectors.Add((Func<TElement, object>)l.Compile()); } return elements.GroupByMany(selectors.ToArray()); } public static IEnumerable<GroupResult> GroupByMany <TElement>(this IEnumerable<TElement> elements, params Func<TElement, object>[] groupSelectors) { if (groupSelectors.Length > 0) { var selector = groupSelectors.First(); //reduce the list recursively until zero var nextSelectors = groupSelectors.Skip(1).ToArray(); return elements.GroupBy(selector) .Select(g => new GroupResult { Key = g.Key, Count = g.Count(), Items = g, SubGroups = g.GroupByMany(nextSelectors) }); } return null; } } }
Queries
folder add a new C# class named GroupResult
with the following code:using System.Collections; using System.Collections.Generic; namespace System.Linq.Dynamic { public class GroupResult { public object Key { get; set; } public int Count { get; set; } public IEnumerable Items { get; set; } public IEnumerable<GroupResult> SubGroups { get; set; } public override string ToString() { return string.Format("{0} ({1})", Key, Count); } } }
As always, we will start our solution with the definition of the problem, our tests. These will make sure we both accomplish the goal and don't over implement for features that were not requested.
Once we have our test, the next step for us is to set up the Blog
, the mapping, and the context so we have a fully formed context to test with.
Our implementation of dynamic queries will be leveraging a couple of open source classes that we will find invaluable. These will allow us to focus on solving the problem instead of taking a deep dive into expression tree building. While it is a good technology to understand and use, it is not the focus of this recipe.
We will implement a couple of extension methods which will allow us to group queryable data into a set of grouped data. These groups can take in the name of the columns to group by. However, they can easily take in a business name and translate it to columns.
Dynamic grouping is difficult because we will not know the return type of the group key. This is shown by our implementation of the extensions. Notice that we have the extension methods returning GroupResults
, and not just an IGrouping<T>
. This is to allow for the scenario of not knowing the class at design time.
With this recipe, we can leverage more than just Language Integrated Query, and can take a deeper look at those parts.
This namespace is part of a solution which allows developers the same level of flexibility with LINQ that we have with SQL. Dynamic SQL is nasty, but there are some problems that require it. Those same problems can now be solved using System.Linq.Dynamic
. This is a powerful library that can be used to do many cool things, but should be used cautiously. We can quickly get into the nasty SQL land with it, and should be wary.
As developers, one of the key things we need to focus on is componentization of an application. This means going through and trying to reuse things without coupling them together. We run into many struggles with this in pure object-oriented solutions, but when we start talking to database, this gets even more difficult. The data access layer has always been excused from this because it has to be coupled to the database, right? Wrong. We can overcome this by taking a few simple steps outlined here. Imagine a reusable data access layer that we can port into any application without fear, and without as much as one new line of code. If we implement our Entity Framework correctly, the core pieces will be portable and reusable in the extreme.
AsQueryable
and AsEnumerable
are powerful methods that allow us to determine the amount of control we would like to give someone who is using our code. AsQueryable
allows the return of a query that can be modified and changed before it is rendered into SQL. However, AsEnumerable
takes that control and locks SQL in its current state. Further detail can be layered in and executions can be added, but the SQL will not change.