c# - Order by dynamic parameter

Question

I'm trying to make and order by with dynamic parameter, this is the code:

var res = from c in db.CUSTOMERS select c;

if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(direction))
      {
       var property = typeof(CUSTOMERS).GetProperty(sortBy);

       if(direction == "asc")
         {
            res = res.OrderBy(x => property.GetValue(x));
         }
      else
         {
           res = res.OrderBy(x => property.GetValue(x));
         } 
}

return res.ToList();

but I get this error :

LINQ to Entities does not recognize the method 'System.Object GetValue(System.Object)' method, and this method cannot be translated into a store expression.

question from:https://stackoverflow.com/questions/65849281/order-by-dynamic-parameter

Answer 1

If you want to do sorting on the Server Side, you have to correct expression.

 var query = from c in db.CUSTOMERS select c;
    
 if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(direction))
 {
    query = query.ApplyOrderBy(new [] {Tuple.Create(sortBy, direction != "asc")});
 }

 return query.ToList();

And implementation:

public static class QueryableExtensions
{
    public static IQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, IEnumerable<Tuple<string, bool>> order)
    {
        var expr = ApplyOrderBy(typeof(T), query.Expression, order);
        return query.Provider.CreateQuery<T>(expr);
    }

    static Expression MakePropPath(Expression objExpression, string path)
    {
        return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
    }

    static Expression ApplyOrderBy(Type entityType, Expression queryExpr, IEnumerable<Tuple<string, bool>> order)
    {
        var param = Expression.Parameter(entityType, "e");
        var isFirst = true;
        foreach (var tuple in order)
        {
            var lambda = Expression.Lambda(MakePropPath(param, tuple.Item1), param);
            var methodName =
                isFirst ? tuple.Item2 ? nameof(Queryable.OrderByDescending) : nameof(Queryable.OrderBy)
                : tuple.Item2 ? nameof(Queryable.ThenByDescending) : nameof(Queryable.ThenBy);

            queryExpr = Expression.Call(typeof(Queryable), methodName, new[] { entityType, lambda.Body.Type }, queryExpr, lambda);
            isFirst = false;
        }

        return queryExpr;
    }
}