Generate EF orderby expression by string

Question

I want to generate expression by string parameter,some code like:

private Expression<Func<Task, T>> Generate(string orderby)
{
    switch (orderby)
    {
        case "Time":  
            return t => t.Time;
        case "Money":
            return t => t.RewardMoney;
        default:
            return t => t.Id;
    }
}

then call it:

_context.Items.OrderBy(Generate("Money"));

But It can't compile! I change T to object.

private Expression<Func<Task, object>> Generate(string orderby)

Then It can compile, but It doesn't work.

System.NotSupportedException: Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

Solution 1

Using reflection and expression-trees you can provide the parameters and then call OrderBy function, Instead of returning Expression<Func<Task, T>> and then calling OrderBy.

Note that OrderBy is an extension method and has implemented in both System.Linq.Enumarable and System.Linq.Queryable classes. The first one is for linq-to-objects and the latter is for linq-to-entities. entity-framework needs the expression tree of the query in order to translate it to SQL commands. So we use the Queryable implementation.

It can be done by an extension method(explanations added as comments):

public static IOrderedQueryable<TSource> OrderBy<TSource>(
       this IQueryable<TSource> query, string propertyName)
{
    var entityType = typeof(TSource);

    //Create x=>x.PropName
    var propertyInfo = entityType.GetProperty(propertyName);
    ParameterExpression arg = Expression.Parameter(entityType, "x");
    MemberExpression property = Expression.Property(arg, propertyName);
    var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

    //Get System.Linq.Queryable.OrderBy() method.
    var enumarableType = typeof(System.Linq.Queryable);
    var method = enumarableType.GetMethods()
         .Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
         .Where(m =>
         {
            var parameters = m.GetParameters().ToList();
            //Put more restriction here to ensure selecting the right overload                
            return parameters.Count == 2;//overload that has 2 parameters
         }).Single();
    //The linq's OrderBy<TSource, TKey> has two generic types, which provided here
    MethodInfo genericMethod = method
         .MakeGenericMethod(entityType, propertyInfo.PropertyType);

    /*Call query.OrderBy(selector), with query and selector: x=> x.PropName
      Note that we pass the selector as Expression to the method and we don't compile it.
      By doing so EF can extract "order by" columns and generate SQL for it.*/
    var newQuery = (IOrderedQueryable<TSource>)genericMethod
         .Invoke(genericMethod, new object[] { query, selector });
    return newQuery;
}

Now you can call this overload of OrderBy like any other overload of it.
For example:

var cheapestItems = _context.Items.OrderBy("Money").Take(10).ToList();

Which translates to:

SELECT TOP (10)  {coulmn names} FROM  [dbo].[Items] AS [Extent1] 
       ORDER BY [Extent1].[Money] ASC

This approach can be used to define all overloads of OrderBy and OrderByDescending methods to have string property selector.

Solution 2

You could try converting the Generate method in a generic method:

private Expression<Func<Task, TResult>> Generate<TResult>(string orderby)
{
     switch (orderby)
     {
        case "Time":  
          return t => t.Time;
        case "Money":
          return t => t.RewardMoney;
        default:
         return t => t.Id;
     }
}

So, if you call this method, you need to specify the type of the property that you want to order by:

_context.Items.OrderBy(Generate<decimal>("Money"));

Now remember that TResult can only be a primitive type or enumeration type.

Solution 3

I referred to the old System.Linq.Dynamic codebase in CodePlex and created a quite simple version from the perspective of implementation and invocation. Of course, it's an extension method on IQueryable<T>

/*
using System;
using System.Linq;
using System.Linq.Expressions;
*/

public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string orderByExpression)
{
    if (string.IsNullOrEmpty(orderByExpression))
        return query;

    string propertyName, orderByMethod;
    string[] strs = orderByExpression.Split(' ');
    propertyName = strs[0];

    if (strs.Length == 1)
        orderByMethod = "OrderBy";
    else
        orderByMethod = strs[1].Equals("DESC", StringComparison.OrdinalIgnoreCase) ? "OrderByDescending" : "OrderBy";

    ParameterExpression pe = Expression.Parameter(query.ElementType);
    MemberExpression me = Expression.Property(pe, propertyName);

    MethodCallExpression orderByCall = Expression.Call(typeof(Queryable), orderByMethod, new Type[] { query.ElementType, me.Type }, query.Expression
        , Expression.Quote(Expression.Lambda(me, pe)));

    return query.Provider.CreateQuery(orderByCall) as IQueryable<T>;
}

Here is samples how to use it, tested for Entity Framework Core 3:

IQueryable<Person> query = dbContext.People;
query = query.OrderBy("FirstName"); // ORDER BY FirstName

IQueryable<Person> query = dbContext.People;
query = query.OrderBy("FirstName ASC"); // ORDER BY FirstName

IQueryable<Person> query = dbContext.People;
query = query.OrderBy("FirstName DESC"); // ORDER BY FirstName DESC

Solution 4

Use a generic method. Since lambda expressions can only be assigned to strongly typed delegates or expressions, we must use an according temp. Then we can assign this temp to a variable typed as object. Finally we can return the result by casting to the result type.

public Expression<Func<Task, TResult>> Generate<TResult>(string orderby)
{
    object result;
    switch (orderby) {
        case "Time":
            Expression<Func<Task, DateTime>> temp1 = t => t.Time;
            result = temp1;
            break;
        case "Money":
            Expression<Func<Task, decimal>> temp2 = t => t.RewardMoney;
            result = temp2;
            break;
        default:
            Expression<Func<Task, int>> temp3 = t => t.Id;
            result = temp3;
            break;
    }
    return (Expression<Func<Task, TResult>>)result;
}

View more solutions

Comments

[Michael Freidgeim]

Possible duplicate of How do I specify the Linq OrderBy argument dynamically?

[yubaolee]

thank you for your answer, but when you pass "Time" as parameter,the call will change: _context.Items.OrderBy(Generate<DataTime>("Time")). This function losts its function?

[KeithS]

Excellent extension. I would note that the first parameter should be an IQueryable<T>, not an IEnumerable<T>. If the OP or any other reader need more, there is a whole library of string-based Linq methods available on NuGet (search for System.Linq.Dynamic) or here: msdn.microsoft.com/en-us/vstudio/bb894665.aspx

[sairfan]

Is there a chance, can we extend order asc or desc? thanks

[Taher Rahgooy]

@sairfan : "OrderBy" is ASC by default, by replacing it with "OrderByDescending" in the where clause you get DESC.

[sairfan]

thanks, have one more question, does it support navigation properties? I'm trying like this items.OrderBy("Category.Name"); and getting error

[Taher Rahgooy]

No, it doesn't. If you want more flexibility it's better to go for Dynamic library mentioned in the first comment by KeithS

[Arun Prasad E S]

@Oliver how to use this

[Olivier Jacot-Descombes]

@ArunPrasadES, _context.Items.OrderBy(Generate("Money"));, as the OP wanted to have.

[PurTahan]

how to suppurt OrderByDescending? with this code it is not supported.

[Taher Rahgooy]

@PurTahan You need to make a copy of this extension and change the m.Name == "OrderBy" to m.Name == "OrderByDescending". Also, don't forget to change the new method name to OrderByDescending.

[IdahoB]

FYI - Ditto for .ThenBy() and .ThenByDescending(). Thanks a mil for this answer ... fixed everything for me!!