Tuesday, August 17, 2010

Dynamic SQL-like Linq OrderBy Extension

So, it's been a while, but I thought I take moment and do my annual blog post ;).

I've been playing around with ASP.NET MVC and the Linq stuff for NHibernate recently. I was in need of an OrderBy extension method that could take a SQL-Like OrderBy string and sort a IQueryable<> or IEnumerable<> collection. I wrote up an implementation that worked, but I just wasn't satisfied with its internals (quite a bit of reflection to get the correct type to construct a LambdaExpression, etc)

At any rate, I couldn't leave well enough alone, and, after a bit of Googling, I ran across this StackOverflow answer about Dynamic LINQ OrderBy. The extension method wasn't exactly what I was looking for, but that ApplyOrder method is slick, and solved the portion of my implementation that was bothering me.

So, I though I would post up my version in case anybody finds it useful. It handles the following inputs:

list.OrderBy("SomeProperty");
list.OrderBy("SomeProperty DESC");
list.OrderBy("SomeProperty DESC, SomeOtherProperty");
list.OrderBy("SomeSubObject.SomeProperty ASC, SomeOtherProperty DESC");
Dynamic SQL-like Linq OrderBy Extension
    public static class OrderByHelper
    {
        public static IEnumerable<T> OrderBy<T>(this IEnumerable<T> enumerable, string orderBy)
        {
            return enumerable.AsQueryable().OrderBy(orderBy).AsEnumerable();
        }

        public static IQueryable<T> OrderBy<T>(this IQueryable<T> collection, string orderBy)
        {
            foreach(OrderByInfo orderByInfo in ParseOrderBy(orderBy))
                collection = ApplyOrderBy<T>(collection, orderByInfo);

            return collection;
        }

        private static IQueryable<T> ApplyOrderBy<T>(IQueryable<T> collection, OrderByInfo orderByInfo)
        {
            string[] props = orderByInfo.PropertyName.Split('.');
            Type type = typeof(T);

            ParameterExpression arg = Expression.Parameter(type, "x");
            Expression expr = arg;
            foreach (string prop in props)
            {
                // use reflection (not ComponentModel) to mirror LINQ
                PropertyInfo pi = 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);
            string methodName = String.Empty;

            if (!orderByInfo.Initial && collection is IOrderedQueryable<T>)
            {
                if (orderByInfo.Direction == SortDirection.Ascending)
                    methodName = "ThenBy";
                else
                    methodName = "ThenByDescending";
            }
            else
            {
                if (orderByInfo.Direction == SortDirection.Ascending)
                     methodName = "OrderBy";
                else
                     methodName = "OrderByDescending";
            }

            //TODO: apply caching to the generic methodsinfos?
            return (IOrderedQueryable<T>)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[] { collection, lambda });

        }

        private static IEnumerable<OrderByInfo> ParseOrderBy(string orderBy)
        {
            if (String.IsNullOrEmpty(orderBy))
                yield break;

            string[] items = orderBy.Split(',');
            bool initial = true;
            foreach(string item in items)
            {
                string[] pair = item.Trim().Split(' ');

                if (pair.Length > 2)
                    throw new ArgumentException(String.Format("Invalid OrderBy string '{0}'. Order By Format: Property, Property2 ASC, Property2 DESC",item));

                string prop = pair[0].Trim();

                if(String.IsNullOrEmpty(prop))
                    throw new ArgumentException("Invalid Property. Order By Format: Property, Property2 ASC, Property2 DESC");
                
                SortDirection dir = SortDirection.Ascending;
                
                if (pair.Length == 2)
                    dir = ("desc".Equals(pair[1].Trim(), StringComparison.OrdinalIgnoreCase) ? SortDirection.Descending : SortDirection.Ascending);

                yield return new OrderByInfo() { PropertyName = prop, Direction = dir, Initial = initial };

                initial = false;
            }

        }

        private class OrderByInfo
        {
            public string PropertyName { get; set; }
            public SortDirection Direction { get; set; }
            public bool Initial { get; set; }
        }

        private enum SortDirection
        {
            Ascending = 0,
            Descending = 1
        }
    }
Anyway, hope someone finds it useful. And if you see any areas that could use some TLC please let me know A

30 comments:

Stuart said...

Thanks.

I mean it. Thanks a lot this has saved me HOURS of work at a time when deadlines or tight. If you're ever in North Lincs, give me a shout and I'll buy you a beer!

Anonymous said...

Great coding, thanks a lot!!!

Unknown said...

Great stuff. Caching the PropertyInfos in a a hashtable would make this thing blaze.

OhSnapPlaya said...

This is just awesome. Thank you so much!

Martin D said...

Thank you for this brilliant piece of code!

Unknown said...

Excellent piece of code, thank you.

Anonymous said...

Very nice! You just saved my day!!! :) :)

Eric Khor said...

Thanks a lot!!!

ChrisD said...

Thank you so much. Saved me so much time. Brilliant!

ChrisD said...

Thanks so much for this. Saved me hours of work. Cheers!

L said...

Thanks a lot this has saved me HOURS
of work , Great coding .

Marcilio said...

Tks a Lot!!

Marcilio said...

Its a beautiful code!

Unknown said...

great code!!

knito said...

Adam, really this is amazing, thanks a lot for this great code!!!

İbrahim Uludağ said...

Very Thanks,

michel said...

great work!! what about random? like Guid.NewGuid()?

Brian McCord said...

That's great code! Thank you. I would request one small change if you have a few minutes. It would be nice if null or an empty string were passed in for it to have a "Default Sort" (perhaps the first property on the object) and sort on that. The reason would be for following it up with .Skip(). Currently, if you pass in a null or empty, it just returns which would cause a .Skip() to fail. Adding a default sort would avoid that.

Brian McCord said...

It actually turned out to be pretty easy. Just put the following code as the first code in the OrderBy(this IQueryable collection, string orderBy) method:

if (orderBy == null || orderBy == string.Empty)
{
Type type = typeof(T);
orderBy = type.GetProperties()[0].Name;
}

Unknown said...

Great job, thank You!

Bernardo Loureiro said...

This solution don't works with collections of extended class. Returns me a error of conflict of types. Anybody knows how to resolve?

Unknown said...

Simple and Slick. Thanks!

I used this OrderByHelper to gracefully allow jtSorting as input for my controller.


http://www.codeproject.com/Articles/277576/AJAX-based-CRUD-tables-using-ASP-NET-MVC-3-and-jTa

Unknown said...

You are awesome men, Good stuff...

sanksuri said...

Thanks a lot!!great work!

Unknown said...

This was a fantastic help, thanks! Like Troy, I needed it to work with jTable.


btw, using ReSharper, the OrderBy method on IQueryable reduced to a single line:


return ParseOrderBy(orderBy).Aggregate(collection, ApplyOrderBy);

a lost soul said...

Love this little piece of code that solves tons of coding needs for dynamic sorting. But I've run into an issue with ICollection sorting. For example if Object Vehicle contains Cars, so if i were to attempt to sort on Cars.Name ASC the property name does not exist on ICollection.

Any suggestions?

Adam Anderson said...

@a lost soul, if I understand your problem correctly, you have a class Car with property Name that inherits from class Vehicle that doesn't have a property Name. You have an ICollection<Vehicle> that you want to order by "Name" using this extension?

The way this method works, you would be bound by the same rules as if you called OrderBy<TSource,TKey>(Func<TSource,TKey>), which is to say, the only properties available would be ones available on TSource e.g. Vehicle or one of its base classes.

One simple option might be, assuming Name is common to Car and Vehicle, you could move it to the base class, which would allow you to order by it using this method.

Another simple option would be to filter the list to a specific type before Ordering with something like .OfType<Car>()

Or a combo of the two above, create a interface INamedVehicle { string Name { get; } } that is implemented by Car, and cast the collection to that with .OfType<INamedVehicle>() prior to ordering

One other option, that is a decidedly more complex, and less generic, but could be as "dynamic" as the posted method would be to create a custom IComparer<Vehicle> that has more knowledge of the type hierarchy, that takes a property list as a string and can do custom comparison logic based on type and property. Then use OrderBy(item => item, new MyVehicleComparer("Name"))

Hope that helps.

Unknown said...

This class is just great and I've used it for years! Thanks.

It fails in a way I don't manage to solve though; If I sort by a child object and any item in the collection has NULL in the path, a NullException is thrown.

Example: I want to sort on "Stock.Shipping.Tour.Name" and one item has Tour = null.

I'd really appreciate if you can hint how to sprt such objects at the top or bottom instead of crashing.

Unknown said...

Hi Adam,
I was wondering if you could get in touch with me in regards to licensing of this code. Could you email me at greg.boudreau@gmail.com?
Thanks!

Unknown said...

This code works great - exactly what I needed for multi-column sorting, except that it fails in a UWP Release build targeting the .NET Native tool chain. the solution is to add, in your projectname.rd.xml file, the following:

Type Name="System.Linq.Queryable" Browse="Required Public" Dynamic="Required Public"