How to pass multiple Expressions to OrderBy for EF?

Question

I am using EF 4.2, but I expect this would apply to EF 4 and 4.1 as well.

I would like to pass an IQueryable<T> and multiple Expression<Func<TSource, TKey>> to a method and have the method apply OrderBy and ThenBy to the IQueryable<T> as appropriate.

I found this answer, and wrote the method below based on that:

public IQueryable<User> ApplyOrderBy(IQueryable<User> query, IEnumerable<Expression<Func<User, IComparable>>> orderBy)
{
    if (orderBy == null) 
    {
        return query;
    }

    IOrderedQueryable<User> output = null;

    foreach(var expression in orderBy)
    {
        if (output == null)
        {
            output = query.OrderBy(expression);
        }
        else
        {
            output = output.ThenBy(expression);
        }
    }

    return output ?? query;
}

This works fine as long as the properties I order by are strings, but when I try to order by an int property, I get an exception:

Unable to cast the type 'System.Int32' to type 'System.IComparable'. LINQ to Entities only supports casting Entity Data Model primitive types.

Any suggestions to work around this, or for a different approach altogether? I considered passing in an IEnumerable<Expression>, but then would need to figure out how to cast back to the specific type (e.g. Expression<Func<User, int>) to call OrderBy.

Answer 1

I cannot explain why using an Int32 does not work but using a string. Aren't both EDM "primitive" types and do not both implement IComparable? I don't understand the different behaviour.

Anyway, it seems to be necessary to pass in every expression in the collection with the concrete type it should be sorted by to avoid the failing type cast. In other words not an IComparable, but instead an int, a string, a DateTime, etc.

I had success to achieve this along the lines of the idea in this answer: How to check for the presence of an OrderBy in a ObjectQuery<T> expression tree

Define an interface which does not depend on the type to sort by but only the entity type. (The example below is generalized to arbitrary entities. If you only want that for User remove the generic parameter and replace TEntity in the queryables by User.)

public interface IOrderByExpression<TEntity> where TEntity : class
{
    IOrderedQueryable<TEntity> ApplyOrderBy(IQueryable<TEntity> query);
    IOrderedQueryable<TEntity> ApplyThenBy(IOrderedQueryable<TEntity> query);
}

Define an implementation of that interface which now takes the type to sort by as a second generic parameter:

public class OrderByExpression<TEntity, TOrderBy> : IOrderByExpression<TEntity>
    where TEntity : class
{
    private Expression<Func<TEntity, TOrderBy>> _expression;
    private bool _descending;

    public OrderByExpression(Expression<Func<TEntity, TOrderBy>> expression,
        bool descending = false)
    {
        _expression = expression;
        _descending = descending;
    }

    public IOrderedQueryable<TEntity> ApplyOrderBy(
        IQueryable<TEntity> query)
    {
        if (_descending)
            return query.OrderByDescending(_expression);
        else
            return query.OrderBy(_expression);
    }

    public IOrderedQueryable<TEntity> ApplyThenBy(
        IOrderedQueryable<TEntity> query)
    {
        if (_descending)
            return query.ThenByDescending(_expression);
        else
            return query.ThenBy(_expression);
    }
}

Then ApplyOrderBy would look like this:

public IQueryable<TEntity> ApplyOrderBy<TEntity>(IQueryable<TEntity> query,
    params IOrderByExpression<TEntity>[] orderByExpressions)
    where TEntity : class
{
    if (orderByExpressions == null)
        return query;

    IOrderedQueryable<TEntity> output = null;

    foreach (var orderByExpression in orderByExpressions)
    {
        if (output == null)
            output = orderByExpression.ApplyOrderBy(query);
        else
            output = orderByExpression.ApplyThenBy(output);
    }

    return output ?? query;
}

And it can be used the following way:

var query = context.Users ... ;

var queryWithOrderBy = ApplyOrderBy(query,
    new OrderByExpression<User, string>(u => u.UserName),    // a string, asc
    new OrderByExpression<User, int>(u => u.UserId, true));  // an int, desc

var result = queryWithOrderBy.ToList(); // didn't throw an exception for me

The need to specify the generic type parameters explicitely in the OrderByExpression instances is not nice but I couldn't find a way so that the compiler infers the types. (I was hoping it would, because the compiler infers the User as TEntity from query for the ApplyOrderBy method, then I expected that it knows the TEntity of OrderByExpression (equals User as well). So the lambda parameter u should be known as a User and then the compiler could derive the type from UserName as string and from UserId as int. But this theory is apparently wrong. The compiler complains and wants to have the generic types explicitly.)