Generate EF orderby expression by string [duplicate]

by

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.

More Related Contents:

  1. How to create LINQ Expression Tree to select an anonymous type
  2. How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?
  3. How to wrap Entity Framework to intercept the LINQ expression just before execution?
  4. Linq: help me to make this work
  5. How to use a string to create a EF order by expression?
  6. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  7. The type of one of the expressions in the join clause is incorrect in Entity Framework
  8. Entity framework left join
  9. Mutating the expression tree of a predicate to target another type
  10. LEFT JOIN in LINQ to entities?
  11. Linq to EntityFramework DateTime
  12. LINQ to SQL multiple tables left outer join
  13. Entity Framework (.NET Full Framework) Ordering Includes [duplicate]
  14. How to compare only date components from DateTime in EF?
  15. How to Bulk Update records in Entity Framework?
  16. Entity Framework 6 Code First Custom Functions
  17. Entity Framework. Delete all rows in table
  18. LINQ to Entities does not recognize the method ‘System.Web.Mvc.FileResult’
  19. Order by dynamic parameter
  20. LINQ, Unable to create a constant value of type XXX. Only primitive types or enumeration types are supported in this context
  21. Linq Syntax – Selecting multiple columns
  22. Linq to Entity Join table with multiple OR conditions
  23. ‘Date’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported
  24. Log Queries executed by Entity Framework DbContext
  25. Access the value of a member expression
  26. Simulating Cross Context Joins–LINQ/C#
  27. How to do an “in” query in entity framework?
  28. How does PredicateBuilder work
  29. How do I dynamically create an Expression predicate?
  30. Should the order of LINQ query clauses affect Entity Framework performance?

Leave a Comment