How to construct Order By Expression dynamically in Entity Framework? [duplicate]

Question

I used the following methods to construct Order By Expression. Original Source

It is really slick. The downside is it only works if Property is string type.

How can I make it to accept different Property type without creating a bunch of methods for different data types?

public static bool PropertyExists<T>(string propertyName)
{
    return typeof (T).GetProperty(propertyName, BindingFlags.IgnoreCase |
      BindingFlags.Public | BindingFlags.Instance) != null;
}

public static Expression<Func<T, string>> GetPropertyExpression<T>(string propertyName)
{
    if (typeof(T).GetProperty(propertyName, BindingFlags.IgnoreCase | 
        BindingFlags.Public | BindingFlags.Instance) == null)
    {
        return null;
    }

    var paramterExpression = Expression.Parameter(typeof(T));

    return (Expression<Func<T, string>>)Expression.Lambda(
        Expression.PropertyOrField(paramterExpression, propertyName), paramterExpression);
}

Usage

// orderBy can be either Name or City.
if (QueryHelper.PropertyExists<Club>(orderBy)) 
{ 
   var orderByExpression = QueryHelper.GetPropertyExpression<Club>(orderBy); 
   clubQuery = clubQuery.OrderBy(orderByExpression); 
} 
else 
{ 
   clubQuery = clubQuery.OrderBy(c => c.Id); 
}

Problem

public class Club 
{ 
  public int Id { get; set; } 
  public string Name { get; set; } 
  public string City { get; set; } 
  public DateTime CreateDate { get; set; } <= this won't work
}

My Current Approach (Too many if statements)

public static Expression<Func<TSource, TKey>> 
    GetPropertyExpression<TSource, TKey>(string propertyName)
{
    if (typeof (TSource).GetProperty(propertyName, BindingFlags.IgnoreCase | 
        BindingFlags.Public | BindingFlags.Instance) == null)
    {
        return null;
    }
    var paramterExpression = Expression.Parameter(typeof (TSource));
    return (Expression<Func<TSource, TKey>>) 
        Expression.Lambda(Expression.PropertyOrField(
           paramterExpression, propertyName), paramterExpression);
}

The downside is I end up with a lot of if statements for each datatype.

if (QueryHelper.PropertyExists<Club>(orderBy)) 
{
   if(orderBy == "CreateDate")
   {       
      var orderByExpression = GetPropertyExpression<Club, DateTime>(orderBy);
      ...
   }
   else if(orderBy == "Name" || orderBy == "City")
   {
      var orderByExpression = GetPropertyExpression<Club, string>(orderBy);
      ...
   }
   ...
}
else 
{ 
   clubQuery = clubQuery.OrderBy(c => c.Id); 
}

So it is at the var orderByExpression = QueryHelper.GetPropertyExpression<Club>(orderBy); that it causes the problem? — krillgar
– krillgar, Commented Jan 20, 2016 at 17:38
The problem is too many repeated code for each data type. Please kindly refer to the last code I posted. It seems that very common problem for whoever use dynamic OrderBy; but I could not find any solution in Google. I'm open to any suggestion or alternative approach. — Win
– Win, Commented Jan 20, 2016 at 17:47
Rather than passing in "CreateDate" or "Name" or "City" or what not, I'd pass in some sort of object that represents the property to sort by. This object would have the ability to generate a sort expression, and other useful things. More code, but no messy if jungle. — Mike Christensen
– Mike Christensen, Commented Jan 20, 2016 at 18:04
@MikeChristensen what would be the type of the returned Expression? — moarboilerplate
– moarboilerplate, Commented Jan 20, 2016 at 18:27
@moarboilerplate - Hmm, I suppose LambdaExpression? The only thing Expression<TDelegate> gives you is Compile and Update which he probably doesn't need. — Mike Christensen
– Mike Christensen, Commented Jan 20, 2016 at 18:37

kumaheiyama · Accepted Answer · 2019-11-13 18:13:09Z

36

I found a solution with the help of Jon Skeet's old answer.

public static class QueryHelper
{
    private static readonly MethodInfo OrderByMethod =
        typeof (Queryable).GetMethods().Single(method => 
        method.Name == "OrderBy" && method.GetParameters().Length == 2);

    private static readonly MethodInfo OrderByDescendingMethod =
        typeof (Queryable).GetMethods().Single(method => 
        method.Name == "OrderByDescending" && method.GetParameters().Length == 2);

    public static bool PropertyExists<T>(this IQueryable<T> source, string propertyName)
    {
        return typeof(T).GetProperty(propertyName, BindingFlags.IgnoreCase |
            BindingFlags.Public | BindingFlags.Instance) != null;
    }

    public static IQueryable<T> OrderByProperty<T>(
       this IQueryable<T> source, string propertyName)
    {
        if (typeof (T).GetProperty(propertyName, BindingFlags.IgnoreCase | 
            BindingFlags.Public | BindingFlags.Instance) == null)
        {
            return null;
        }
        ParameterExpression paramterExpression = Expression.Parameter(typeof (T));
        Expression orderByProperty = Expression.Property(paramterExpression, propertyName);
        LambdaExpression lambda = Expression.Lambda(orderByProperty, paramterExpression);
        MethodInfo genericMethod = 
          OrderByMethod.MakeGenericMethod(typeof (T), orderByProperty.Type);
        object ret = genericMethod.Invoke(null, new object[] {source, lambda});
        return (IQueryable<T>) ret;
    }

    public static IQueryable<T> OrderByPropertyDescending<T>(
        this IQueryable<T> source, string propertyName)
    {
        if (typeof (T).GetProperty(propertyName, BindingFlags.IgnoreCase | 
            BindingFlags.Public | BindingFlags.Instance) == null)
        {
            return null;
        }
        ParameterExpression paramterExpression = Expression.Parameter(typeof (T));
        Expression orderByProperty = Expression.Property(paramterExpression, propertyName);
        LambdaExpression lambda = Expression.Lambda(orderByProperty, paramterExpression);
        MethodInfo genericMethod = 
          OrderByDescendingMethod.MakeGenericMethod(typeof (T), orderByProperty.Type);
        object ret = genericMethod.Invoke(null, new object[] {source, lambda});
        return (IQueryable<T>) ret;
    }
}

Usage

string orderBy = "Name";
if (query.PropertyExists(orderBy))
{
   query = query.OrderByProperty(orderBy);
   - OR - 
   query = query.OrderByPropertyDescending(orderBy);
}

edited Nov 13, 2019 at 18:13

kumaheiyama

7841 gold badge13 silver badges30 bronze badges

answered Jan 20, 2016 at 18:49

Win

62.4k13 gold badges106 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

krillgar Over a year ago

Did we really expect anyone else to have an answer? ;)

SJMan Over a year ago

Is it possible to modify this to also sort on Navigation properties of current entity ?

kumaheiyama Over a year ago

Excellent solution! My only question is how can I order by an subordinate object? For instance, if I have an object Apple { Kernel { Size = 13 } } how would I order the query on the Size property? Setting orderBy in the example to "Kernel.Size" doesn't work.

James Hancock Over a year ago

This doesn't work with EF Core 3.1. Any suggestions on how to make it work right?

ListenSoftware Louise Ai Agent Over a year ago

there should be a way to take an sql expression on the order by and evaluate the fields and operators then generate the c# code, a code generator. This seems like a verbose way to dynamically add an order by.

Uttam Ughareja · Accepted Answer · 2022-09-24 11:55:11Z

6

option 1 : this can done using expression : check this sample

public static IQueryable<T> OrderByPropertyOrField<T>(this IQueryable<T> queryable, string propertyOrFieldName, bool ascending = true)
{
        var elementType = typeof (T);
        var orderByMethodName = ascending ? "OrderBy" : "OrderByDescending";

        var parameterExpression = Expression.Parameter(elementType);
        var propertyOrFieldExpression = Expression.PropertyOrField(parameterExpression, propertyOrFieldName);
        var selector = Expression.Lambda(propertyOrFieldExpression, parameterExpression);

        var orderByExpression = Expression.Call(typeof (Queryable), orderByMethodName,
                                                new[] {elementType, propertyOrFieldExpression.Type}, queryable.Expression, selector);

        return queryable.Provider.CreateQuery<T>(orderByExpression);
}

option 2 (if you are using ef core):

 public static IQueryable<TEntity> ApplyOrderBy<TEntity>(
        this IQueryable<TEntity> query, string? orderBy, string orderDirection)
{
        if (orderBy is null) return query;
        
        query = orderDirection == "Asc"
            ? query.OrderBy(p => EF.Property<TEntity>(p!, orderBy))
            : query.OrderByDescending(p => EF.Property<TEntity>(p!, orderBy));

        return query;
 }

edited Sep 24, 2022 at 11:55

answered Jul 13, 2022 at 17:06

Uttam Ughareja

1,4952 gold badges21 silver badges27 bronze badges

1 Comment

nolisj Over a year ago

The EFCore solution is without equal. Thanks a lot!

Romias · Accepted Answer · 2019-11-13 19:10:10Z

-2

I have, IMHO, a simpler solution:

public static IOrderedQueryable<TSource> Sort<TSource>(this IQueryable<TSource> source, bool ascending , string sortingProperty)
{
    if (ascending)
        return source.OrderBy(item => item.GetReflectedPropertyValue(sortingProperty));
    else
        return source.OrderByDescending(item => item.GetReflectedPropertyValue(sortingProperty));
}

private static object GetReflectedPropertyValue(this object subject, string field)
{
    return subject.GetType().GetProperty(field).GetValue(subject, null);
}

The usage is:

myQueryableCollection.Sort(ascending: true, "Name")

Of course, the PropertyExist() helper is a great addition...

answered Nov 13, 2019 at 19:10

Romias

14.2k7 gold badges61 silver badges89 bronze badges

3 Comments

Tomas Bruckner Over a year ago

Hi, does this work in EF Core 3? It throws for me: .OrderByDescending(e0 => (object)e0 .GetReflectedPropertyValue(__filter_Sort_Predicate_3))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToLi st(), or ToListAsync(). See go.microsoft.com/fwlink/?linkid=2101038 for more information.

Ian Over a year ago

Doesn't work:

The LINQ expression 'DbSet<ImportLog>     .Where(i => .....)     .OrderBy(i => (object)i         .GetReflectedPropertyValue(__sortingProperty_0))' could not be translated.

Romias Over a year ago

@Ian if it doesn't translate is because you are sending the query directly to the database. You have to get the results with a ToList() or similar before.

Collectives™ on Stack Overflow

How to construct Order By Expression dynamically in Entity Framework? [duplicate]

Usage

Problem

My Current Approach (Too many if statements)

3 Answers 3

Usage

5 Comments

1 Comment

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Usage

Problem

My Current Approach (Too many if statements)

3 Answers 3

Usage

5 Comments

1 Comment

3 Comments

Linked

Related