Landman Code Exploring my outer regions of coding.

A blog about C#, Delphi, assembler and general developer stuff.

Landman Code Exploring my outer regions of coding.

A blog about C#, Delphi, assembler and general developer stuff.

LINQ to Entities string based dynamic OrderBy

The String Maker picture by  hpk on http://flickr.com/photos/hpk/86047888/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: , ,

12 comments:

  1. Krishna said:

    Thanks for pointing to Scott's post.
    I could not use your code due to the license.

    at 11 July, 2009 14:35  
  2. Davy Landman said:

    Hi Krishna,

    why does my license limit you?

    If I'm not mistaken my license is very flexible.

    at 11 July, 2009 16:25  
  3. Krishna said:

    Hello Davy,

    I usually prefer MIT/BSD,Apache PL,Microsoft PL in that order.This is specifically for business use.For my personal use, any license would work.
    Mozilla PL seems very permissive to me except for the clause 3.2 which says that I should also make available the source along with my distribution. This is not a big deal usually but I still have to be mindful about it in every release (of my software) or I will be violating the terms of the license.

    at 12 July, 2009 02:06  
  4. Davy Landman said:

    Hi krishna,

    It seems to me that clause only applies if you change something in the source.


    Any Modification which You create or to which You contribute must be
    made available in Source Code form under the terms of this License either
    on the same media as an Executable version or via an accepted Electronic
    Distribution Mechanism to anyone to whom you made an Executable version
    available

    at 12 July, 2009 10:36  
  5. Krishna said:

    Davy,

    You have to admit,it's still a bother compared to the other licenses.
    The way I see it, the difference is in attribution vs distribution.
    Whereas in the case of apache and MS I have to keep the notice and not change the license on derivative works , in case of MPL I also have to make sure that I distribute the source.

    Ultimately, it's about choice.But I feel for business use, Apache and BSD are the best options (for the user that is).

    at 14 July, 2009 08:51  
  6. slobodan said:

    Hi Davy,
    I used your idea for extension method for OrderBy. But in case of "many to many" I am getting error. For example you have table Site, Customer and Customer_site.
    For given Site I want to sort by customer name and in OrderBy extension (when I pass "site.customer" where customer is navigation property) I get error in line: propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);

    This is what I use (with some enhancements :-) ):

    if (propertyName.Contains('.'))
    {
    // support to be sorted on child fields.
    String[] childProperties = propertyName.Split('.');
    property = typeof(TEntity).GetProperty(childProperties[0]);
    propertyAccess = Expression.MakeMemberAccess(parameter, property);

    for (int i = 1; i < childProperties.Length; i++)
    {
    Type t = property.PropertyType;
    if (!t.IsGenericType)
    {
    property = t.GetProperty(childProperties[i]);
    }
    else
    {
    property = t.GetGenericArguments().First().GetProperty(childProperties[i]);
    }

    propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
    }
    }

    Regards

    Slobodan

    at 03 November, 2009 21:51  
  7. Anonymous said:

    Hey dude, you rock!
    This works on EF CTP5 too! ;)

    at 12 December, 2010 11:43  
  8. Kashy said:

    This comment has been removed by the author. at 16 August, 2012 03:52  
  9. Kashy said:

    I tried your code but it does not work for any subsequent calls to Skip and Take. I am guessing it's just generating dynamic sql on the backend but does not acknowledge that orderby clause is set.

    at 16 August, 2012 03:54  
  10. pomber said:

    How do you handle nulls?

    For example, in
    var result = queryableOrders.OrderBy("Employee.LastName")

    What if Employee is null?

    at 27 February, 2013 20:21  
  11. Gorsh said:

    thank you VERY much, all my mornig trying to solve this with the current fwk, versions, etc. THANX!

    at 10 September, 2014 18:37  
  12. Santosh said:

    Hi Landman,

    Thanks for sharing knowledge, just a small suggestion on code, for "many to many" scenario instead of passing "table_property_name.actual_propertyname" cannot we directly pass the property name? And let your class identify from which table that property belong to?

    Like below:

    if (propertyName.Contains('.'))
    {
    //Your code
    }
    else
    {
    property = typeof(TEntity).GetProperty(propertyName, BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

    //NEW SUGGESTED CODE
    //if property not found in current entity then look it for child properties
    if (property == null)
    {
    var childProperties = typeof (TEntity).GetProperties();
    foreach (var p in childProperties)
    {
    if (p.PropertyType.BaseType == typeof(BaseEntity))//Note that Base entity is the base of all core entity classes
    {
    property = p.PropertyType.GetProperty(propertyName);
    if (property != null)
    {
    propertyAccess = Expression.MakeMemberAccess(parameter, p);
    propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
    break;
    }
    }
    }
    }
    else
    {
    propertyAccess = Expression.MakeMemberAccess(parameter, property);
    }
    }

    at 10 March, 2015 11:21  

Post a Comment