How to make a dynamic order in Entity Framework

Question

I have a dictionary declared like this:

private Dictionary<string, Expression<Func<Part, object>>> _orders = new Dictionary<string, Expression<Func<Part, object>>>()
    {
        {"Name", x => x.Name}, //string
        {"Code", x => x.Code}, //string
        {"EnterPrice", x => x.EnterPrice}, //decimal
        {"ExitPrice", x => x.ExitPrice}, //decimal
        {"IsActive", x => (bool)x.Active }, //bool
        {"Quantity", x => x.Quantity}, //decimal
        {"Reserved", x => x.Reserved}, //decimal
    };

I try to bring data using the following code:

    NameValueCollection filter = HttpUtility.ParseQueryString(Request.RequestUri.Query);
    string sortField = filter["sortField"];
    string sortOrder = filter["sortOrder"];
    Func<IQueryable<Part>, IOrderedQueryable<Part>> orderBy = x => x.OrderBy(p => p.Id);
    if (!string.IsNullOrEmpty(sortField) && _orders.ContainsKey(sortField))
    {
        bool sortMode = !string.IsNullOrEmpty(sortOrder) && sortOrder != "desc";
        if (sortMode)
        {
            orderBy = x => x.OrderBy(_orders[sortField]);
        }
        else
        {
            orderBy = x => x.OrderByDescending(_orders[sortField]);
        }
    }
    return Ok(this.DbService.Query(null, filterQuery));

And Query method is:

public IQueryable<TEntity> Query(Expression<Func<TEntity, bool>> filter = null,
    Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null, bool noTracking = true)
{

    IQueryable<TEntity> query = DbContext.Set<TEntity>();
    if (filter != null)
    {
        query = query.Where(filter);
    }
    if (orderBy != null) query = orderBy(query);
    return noTracking ? query.AsNoTracking() : query;
}

But when the sort column is not string I obtain the following exception

"Unable to cast the type 'System.Boolean' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.","ExceptionType":"System.NotSupportedException","StackTrace":" at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()"}

I think that the dictionary declaration and/or initialization is wrong because if I do not have any sort set by browser then the default order will be x=>x.Id (which is declared inline) and it doesn't crash even if Id is long. Can I declare the dictionary in a different way to solve my problem?

PROBLEM SOLVED

I removed the dictionary and I added the following extension which receive the field name and sort mode as parametters

public static class LinqExtension
{
    public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, bool ascending = true)
    {
        var type = typeof(T);
        var parameter = Expression.Parameter(type, "p");
        PropertyInfo property;
        Expression propertyAccess;
        if (ordering.Contains('.'))
        {
            // support to be sorted on child fields.
            String[] childProperties = ordering.Split('.');
            property = type.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(T).GetProperty(ordering);
            propertyAccess = Expression.MakeMemberAccess(parameter, property);
        }
        var orderByExp = Expression.Lambda(propertyAccess, parameter);
        MethodCallExpression resultExp = Expression.Call(typeof(Queryable),
                                                         ascending ? "OrderBy" : "OrderByDescending",
                                                         new[] { type, property.PropertyType }, source.Expression,
                                                         Expression.Quote(orderByExp));
        //return  source.OrderBy(x => orderByExp);
        return source.Provider.CreateQuery<T>(resultExp);
    }
}

Also solution provided by Ivan Stoev works

Answer 1

The dictionary definition is ok - there is no good way to declare it to have values with different type.

The problem is that Expression<Func<T, object>> definition generates additional Expression.Convert for value type properties. To make it work with EF, the convert expression must be removed and corresponding Queryable method must be called dynamically. It can be encapsulated in a custom extension method like this:

public static class QueryableExtensions
{
    public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, Expression<Func<T, object>> keySelector, bool ascending)
    {
        var selectorBody = keySelector.Body;
        // Strip the Convert expression
        if (selectorBody.NodeType == ExpressionType.Convert)
            selectorBody = ((UnaryExpression)selectorBody).Operand;
        // Create dynamic lambda expression
        var selector = Expression.Lambda(selectorBody, keySelector.Parameters);
        // Generate the corresponding Queryable method call
        var queryBody = Expression.Call(typeof(Queryable),
            ascending ? "OrderBy" : "OrderByDescending",
            new Type[] { typeof(T), selectorBody.Type },
            source.Expression, Expression.Quote(selector));
        return source.Provider.CreateQuery<T>(queryBody); 
    }
}

and the usage in your scenario could be like this:

if (!string.IsNullOrEmpty(sortField) && _orders.ContainsKey(sortField))
    orderBy = x => x.OrderBy(_orders[sortField], sortOrder != "desc");