The problem I kept having was that at some point you’ll need to convert the GridView.SortExpression from your ASP.NET GridView to a Lamba Expression for your Queryable.OrderBy.

The challenge

You’ll get the string.

“OrderDate DESC”

And you have to translate that to:

ObjectContext.Orders.OrderByDescending(order => order.OrderDate)

In march/may I had spent a half day researching possibilities to solve this automatically. At that point I couldn’t find a clean solution. So I went with a generated solution (using T4 templates) which generated a big switch statement per entity.

A nice solution

We jump to november and I was browsing a little bit on StackOverflow to see if their were some interesting questions. My eye caught the question “How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?”, and it got me started to solve the sorting challenge. I had some hours available so I thought I’d give it another try.

There are a few other’s who have created a solution using the Expression building method, and I’ve combined it and refactored it to make it match more with the current Entity Framework. I’ve also added support for an infinite amount of child entities.

/***** BEGIN LICENSE BLOCK *****
 * Version: MPL 1.1/GPL 2.0/LGPL 2.1
 *
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 *
 * The Original Code is LINQExtensions.StringFieldNameSortingSupport.
 *
 * The Initial Developer of the Original Code is
 * Davy Landman.
 * Portions created by the Initial Developer are Copyright (C) 2008
 * the Initial Developer. All Rights Reserved.
 *
 * Contributor(s):
 *
 *
 * Alternatively, the contents of this file may be used under the terms of
 * either the GNU General Public License Version 2 or later (the "GPL"), or
 * the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
 * in which case the provisions of the GPL or the LGPL are applicable instead
 * of those above. If you wish to allow use of your version of this file only
 * under the terms of either the GPL or the LGPL, and not to allow others to
 * use your version of this file under the terms of the MPL, indicate your
 * decision by deleting the provisions above and replace them with the notice
 * and other provisions required by the GPL or the LGPL. If you do not delete
 * the provisions above, a recipient may use your version of this file under
 * the terms of any one of the MPL, the GPL or the LGPL.
 *
 * ***** END LICENSE BLOCK ***** */
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace LINQExtensions
{
    public static class StringFieldNameSortingSupport
    {
        #region Private expression tree helpers

        private static LambdaExpression GenerateSelector<TEntity>(String propertyName, out Type resultType) where TEntity : class
        {
            // Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
            var parameter = Expression.Parameter(typeof(TEntity), "Entity");
            //  create the selector part, but support child properties
            PropertyInfo property;
            Expression propertyAccess;
            if (propertyName.Contains('.'))
            {
                // support to be sorted on child fields.
                String[] childProperties = propertyName.Split('.');
                property = typeof(TEntity).GetProperty(childProperties[0], BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
                propertyAccess = Expression.MakeMemberAccess(parameter, property);
                for (int i = 1; i < childProperties.Length; i++)
                {
                    property = property.PropertyType.GetProperty(childProperties[i], BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
                    propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
                }
            }
            else
            {
                property = typeof(TEntity).GetProperty(propertyName, BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
                propertyAccess = Expression.MakeMemberAccess(parameter, property);
            }
            resultType = property.PropertyType;
            // Create the order by expression.
            return Expression.Lambda(propertyAccess, parameter);
        }
        private static MethodCallExpression GenerateMethodCall<TEntity>(IQueryable<TEntity> source, string methodName, String fieldName) where TEntity : class
        {
            Type type = typeof(TEntity);
            Type selectorResultType;
            LambdaExpression selector = GenerateSelector<TEntity>(fieldName, out selectorResultType);
            MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
                            new Type[] { type, selectorResultType },
                            source.Expression, Expression.Quote(selector));
            return resultExp;
        }
        #endregion
        public static IOrderedQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source, string fieldName) where TEntity : class
        {
            MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "OrderBy", fieldName);
            return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
        }

        public static IOrderedQueryable<TEntity> OrderByDescending<TEntity>(this IQueryable<TEntity> source, string fieldName) where TEntity : class
        {
            MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "OrderByDescending", fieldName);
            return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
        }
        public static IOrderedQueryable<TEntity> ThenBy<TEntity>(this IOrderedQueryable<TEntity> source, string fieldName) where TEntity : class
        {
            MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "ThenBy", fieldName);
            return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
        }
        public static IOrderedQueryable<TEntity> ThenByDescending<TEntity>(this IOrderedQueryable<TEntity> source, string fieldName) where TEntity : class
        {
            MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "ThenByDescending", fieldName);
            return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
        }
        public static IOrderedQueryable<TEntity> OrderUsingSortExpression<TEntity>(this IQueryable<TEntity> source, string sortExpression) where TEntity : class
        {
            String[] orderFields = sortExpression.Split(',');
            IOrderedQueryable<TEntity> result = null;
            for (int currentFieldIndex = 0; currentFieldIndex < orderFields.Length; currentFieldIndex++)
            {
                String[] expressionPart = orderFields[currentFieldIndex].Trim().Split(' ');
                String sortField = expressionPart[0];
                Boolean sortDescending = (expressionPart.Length == 2) && (expressionPart[1].Equals("DESC", StringComparison.OrdinalIgnoreCase));
                if (sortDescending)
                {
                    result = currentFieldIndex == 0 ? source.OrderByDescending(sortField) : result.ThenByDescending(sortField);
                }
                else
                {
                    result = currentFieldIndex == 0 ? source.OrderBy(sortField) : result.ThenBy(sortField);
                }
            }
            return result;
        }
    }
}

You can also download this file. All you have to do to use these extensions methods is add a using LINQExtensions;".

It's now very simple to write queries like this

var result = queryableOrders.OrderBy("Employee.LastName").ThenByDescending("Freight").ToList();

I’ve deliberately kept the parsing of the SortExpression out of these extension methods. In my opinion it should be kept outside of the string field names support. I’ve added a new extension method just for this purpose.

var result = queryableOrders.OrderUsingSortExpression("Employee.LastName, Freight DESC").ToList();

Bummer

When researching for writing this article I found the article Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library) by the Scott Guthrie, which does exactly as I developed. The only reason I still post this code is that it’s less heavy and not under de Microsoft Permissive License (but under a very flexible triple license).

So decide for yourself, use this code directly or download the samples pack and locate the DynamicLinq folder and use that library (which has some very cool stuff in it).

Tags: c#, Entity Framework, LINQ

My team has switched (with the coaching of class-a) to LINQ to Entities from the ADO.NET Entity Framework for our new applications. Because we came from ADO.NET Typed Datasets we hade to revise our architecture. Together with a team member we designed our new architecture (and asked Alex Thissen to review it). We used a new project to test our architecture and use pair programming to solve problems which arose during the development of the business layer.

We encapsulated the ObjectContext from the Entity Framework using the repository pattern. How we did this I might post about later, but now I’m going to discuss something else.

The problem was that each entity got it’s own repository, but you have to share the ObjectContext, or nothing works except the stuff that happens on that entity alone. Anyone who uses the LINQ to Entities (or LINQ to SQL) can guess what happened, a lot of Exceptions and non updating data. So there are two ways we could solve it, create an repository factory which would inject the correct ObjectContext or make some kind of singleton access to a ObjectContext. We chose for the second solution because we already had an repository factory which we’re planning to use as a unit of work pattern. So that repository factory couldn’t be a singleton and we still had to solve the same problem of the ObjectContext instances.

The singleton pattern I would have normally used was the fully lazy instantiation singleton by Jon Skeet which in my opinion should be mentioned in the MSDN P&P Article: Implementing Singleton in C#. But we wanted a ObjectContext per request, so it’s not really a singleton but more a more managed life-cycle. Although most of our applications are web based, I’d like it if it could survive outside of ASP.NET. Alex Thissen gave us a sample of how he did it, and we used it for a while. The code he provided is below.

public class DataContextFactory
{
    private const string DATACONTEXTKEY = "NorthwindataContext";
    private static object myLock = new object();

    internal static NorthwindEntities CurrentContext
    {
        get
        {
            NorthwindEntities context = DataContext;
            if (context == null)
            {
                lock (myLock)
                {
                    if (context == null)
                    {
                        context = new NorthwindEntities 
                        DataContext = context;
                    }
                }
            }
            return context;
        }
    }

    private static NorthwindEntities DataContext
    {
        get
        {
            if (IsInWebContext)
            {
                return (NorthwindEntities)HttpContext.Current.Items[DATACONTEXTKEY];
            }
            else
            {
                return (NorthwindEntities)CallContext.GetData(DATACONTEXTKEY);
            }
        }

        set
        {
            if (IsInWebContext)
            {
                HttpContext.Current.Items[DATACONTEXTKEY] = value;
            }
            else
            {
                CallContext.SetData(DATACONTEXTKEY, value);
            }
        }
    }

    private static bool IsInWebContext
    {
        get { return HttpContext.Current != null; }
    }
}

We used it in the beginning and it worked. But when I started developing a T4 template for our repositories I looked at the code again and it didn’t feel right, especially the locking. Because both the HttpContext.Current as the CallContext are unique for each thread, so why do we need to lock if only one thread access the same HttpContext.Current or CallContext (please correct me if I'm wrong). A little bit searching on the internet lead me to SimoneB's post about the ASP.NET Singleton-per-Page pattern, I combined this with the CallContext knowledge in my own DataContextFactory. The code for that factory is located below.

internal class DataContextFactory
{
    public static NorthwindEntities CurrentContext
    {
        get
        {
            if (IsInWebContext)
            {
                // support the difference between pages in Server.Transfer
                Page currentPage = HttpContext.Current.Handler as Page;
                if(currentPage == null)
                    throw new NullReferenceException("The page is null");
                return (currentPage.Items["NorthwindEntitiesSingleton"] ??
                       (currentPage.Items["NorthwindEntitiesSingleton"] =
                        new NorthwindEntities())) as NorthwindEntities;
            }
            else
            {
                NorthwindEntities result = CallContext.GetData("NorthwindEntitiesSingleton") as  NorthwindEntities;
                if (result == null)
                {
                    result = new NorthwindEntities();
                    CallContext.SetData("NorthwindEntitiesSingleton", result);
                }
                return result;
            }
        }
    }
    private static bool IsInWebContext
    {
        get { return HttpContext.Current != null; }
    }
}

Because our repositories are generated this DataContextFactory is also generated and therefore it has the string copied allover the place.

Our repositories and the repository factory both store the DataContext in a private field, so if the repository instance is stored in a session, the context will remain the same, but if you create a new repository you’ll have the current DataContext.  To make this more transparent we used the following rule: when we need to use the unit of work pattern, we store the repository factory in the session and only request repositories from that factory. If it’s just a simple page, request the desired repository directly. Below is an example of how a repository looks like using the ContextFactory.

public partial class CategoryRepository: ICategoryRepository
{
    private NorthwindEntities context;

    /// <summary>
    /// Implement this partial method to execute code after the constructor.
    /// </summary>
    partial void AfterConstructed();

    public CategoryRepository() :
        this (DataContextFactory.CurrentContext)
    {
    }
    internal CategoryRepository(NorthwindEntities context)
    {
        this.context = context;
        AfterConstructed();
    }
    // The rest
}

I just have one problem I haven’t figured out yet, the DataContext implements IDisposable, so when I’m done with it I should call Dispose to clean the resources. The problem is, I don’t know when to correctly call Dispose. So if anybody has a good idea how to solve this?

Tags: ADO.NET, architecture, c#, Entity Framework, linq to entities, patterns