Get dynamic OrderBy in LINQ

Question

I am using datatables in quite a few pages in my ASP.NET MVC 3 site. They use server side paging and now I want to implement sorting based on the column headers. Datatables comes with iSortCol_0 which is an int value of what column is clicked.

I did not like this approach as the query would end up something like:

if(iSortCol_0 == 0)
{    
    // query
    // OderBy(x => x.CarName)
}

Then this would be repeated for each column (plus an else clause on each to order by descending). So I have changed my approach and now pass the column name to the server.

I have come up with the following:

Expression<Func<vw_Car, string>> sortExpression1 = null;
Expression<Func<vw_Car, int>> sortExpression2 = null;

switch(columnToSort) 
{
    case "InvoiceNo": sortExpression1 = x => x.CarNo; break;
    case "ClientNumber": sortExpression1 = x => x.ForeName; break;
    case "ClientName": sortExpression1 = x => x.SurName; break;
    default: sortExpression2 =  x => x.Age.Value; break;
}

// start of query
.OrderByDir(sortDirection, sortExpression1 , sortExpression2)

Now the OrderByDir looks like below:

public static IOrderedQueryable<T> OrderByDir<T>(this IQueryable<T> source, string dir, Expression<Func<T, string>> column1, Expression<Func<T, int>> column2)
{
    if (column1 != null)
    {
        return dir == "asc" ? source.OrderBy(column1) : source.OrderByDescending(column1);
    }         
    if (column2 != null)
    {
        return dir == "asc" ? source.OrderBy(column2) : source.OrderByDescending(column2);
    }    
    return null;
}

This works in that it sorts columns that are of type string or int. I have another column of DateTime that I want to sort, so I would need to write another sortExpression3 and then add that to my OrderByDir.

However I don't really like the implementation - I had attempted to write a generic sort expression that took an object as second parameter instead of string, int, Datetime, but when had this code in place, I was getting Unable to cast the type 'System.DateTime' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types.

Any one any ideas as to a possible better approach to this?

Why are there two sortExpressions? Based on your code, either expression one or expression two is set but never both at the same time. Is this correct? — Daniel Hilgarth, Apr 18 '13 at 08:08
@mattytommo: Not applicable, quote "I'm assuming that you only need to support LINQ to Objects". — Daniel Hilgarth, Apr 18 '13 at 08:13
@DanielHilgarth - because one of the sort expressions is for Age column(an int field) - the other sort expression is for string columns — Ctrl_Alt_Defeat, Apr 18 '13 at 09:38

jwaliszko · Answer 1 · 2013-04-18T08:55:05.030

Such extension should fits your needs:

public static class LinqExtensions
{
    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property)
    {
        return ApplyOrder(source, property, "OrderBy");
    }

    public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string property)
    {
        return ApplyOrder(source, property, "OrderByDescending");
    }

    private static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, string methodName)
    {
        var props = property.Split('.');
        var type = typeof(T);
        var arg = Expression.Parameter(type, "x");
        Expression expr = arg;
        foreach (var prop in props)
        {
            // use reflection (not ComponentModel) to mirror LINQ
            var pi = type.GetProperty(prop);
            expr = Expression.Property(expr, pi);
            type = pi.PropertyType;
        }
        var delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
        var lambda = Expression.Lambda(delegateType, expr, arg);

        var 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;
    }
}

Invoke example:

query.OrderBy("Age.Value");

i would recomend to add order direction as parameter instead separate method. — maxlego, Apr 18 '13 at 19:06
It can be done, but the clue here is lambda expression tree building and execution. Other secondary changes are a matter of subjective preferences. — jwaliszko, Apr 18 '13 at 20:19

Get dynamic OrderBy in LINQ

1 Answers1