How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?

Question

    public static IQueryable<TResult> ApplySortFilter<T, TResult>(this IQueryable<T> query,
                                                        string columnName)
        where T : EntityObject

    {
        var param = Expression.Parameter(typeof(T), "o");
        var body = Expression.PropertyOrField(param,columnName);

        var sortExpression = Expression.Lambda(body, param);
        return query.OrderBy(sortExpression);
    }

Because the type for OrderBy is not inferred from sortExpression I need to specify it something like this at run time:

        var sortExpression = Expression.Lambda<T, TSortColumn>(body, param);

Or

        return query.OrderBy<T, TSortColumn>(sortExpression);

I don't think this is possible however as TSortColumn can only be determined during runtime.

Is there a way around this?

Answer 1

It seems that this is the way to do it, now to verify that:

// ***** OrderBy(company => company) *****
// Create an expression tree that represents the expression
// 'whereCallExpression.OrderBy(company => company)'
MethodCallExpression orderByCallExpression = Expression.Call(
    typeof(Queryable),
    "OrderBy",
    new Type[] { queryableData.ElementType, queryableData.ElementType },
    whereCallExpression,
    Expression.Lambda<Func<string, string>>(pe, new ParameterExpression[] { pe }));
// ***** End OrderBy *****

Answer 2

We did something similar (not 100% the same, but similar) in a LINQ to SQL project. Here's the code:

        var type = typeof(T);  
        var property = type.GetProperty(columnName);  
        var parameter = Expression.Parameter(type, "p");  
        var propertyAccess = Expression.MakeMemberAccess(parameter, property);  
        var orderByExp = Expression.Lambda(propertyAccess, parameter);  
        MethodCallExpression resultExp = Expression.Call(typeof(Queryable), "OrderBy", new Type[] { type, property.PropertyType }, currentItems.Expression, Expression.Quote(orderByExp));

We didn't actually use a generic, we had a known class, but it should work on a generic (I've put the generic placeholder where it should be)

Answer 3

I've extended your functions to add support for Child Properties.

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]);
            propertyAccess = Expression.MakeMemberAccess(parameter, property);
            for (int i = 1; i < childProperties.Length; i++)
            {
                    property = property.PropertyType.GetProperty(childProperties[i]);
                    propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
            }
    }
    else
    {
            property = typeof(TEntity).GetProperty(propertyName);
            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;
}

You can use these functions like:

GenerateMethodCall<TEntity>(source, "OrderByDescending", fieldName);

Answer 4

You can also use Dynamic Linq

Info here http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

C# download here http://msdn.microsoft.com/en-us/vcsharp/bb894665.aspx

Then just add the using Linq.Dynamic; and you automatically get 2 additional extension methods that can be used like this

return query.OrderBy("StringColumnName");

Answer 5

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 :-) ):

public static IQueryable OrderBy(this IQueryable source, string orderByValues) where TEntity : class { IQueryable returnValue = null;

        string orderPair = orderByValues.Trim().Split(',')[0];
        string command = orderPair.ToUpper().Contains("DESC") ? "OrderByDescending" : "OrderBy";

        var type = typeof(TEntity);
        var parameter = Expression.Parameter(type, "p");

        string propertyName = (orderPair.Split(' ')[0]).Trim();

        System.Reflection.PropertyInfo property;
        MemberExpression propertyAccess;

        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);
            }
        }
        else
        {
            property = type.GetProperty(propertyName);
            propertyAccess = Expression.MakeMemberAccess(parameter, property);
        }

        var orderByExpression = Expression.Lambda(propertyAccess, parameter);

        var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },

                               source.Expression, Expression.Quote(orderByExpression));


        returnValue = source.Provider.CreateQuery<TEntity>(resultExpression);


        if (orderByValues.Trim().Split(',').Count() > 1)
        {
            // remove first item
            string newSearchForWords = orderByValues.ToString().Remove(0, orderByValues.ToString().IndexOf(',') + 1);
            returnValue = source.OrderBy(newSearchForWords);
        }

        return returnValue;

    }

Regards

Slobodan