Generate EF orderby String Expression

Question

I want to generate an expression by a string parameter, for example, the code:

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 name it:

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

But it cannot compile! I change T to an object.

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

Then it can compile, but it does not work.

System.NotSupportedException: Cannot enter type "System.Int32" to enter "System.Object". LINQ to Entities only supports EDM listing of primitive or enumerated types.

Answer 1

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

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

This can be done using the extension method (explanations added as comments):

 public static IOrderedQueryable<TSource> OrderBy<TSource>( this IEnumerable<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 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 OrderBy overload, like any other overload.
For example:

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

Which means:

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

This approach can be used to determine all overloads of the OrderByDescending and OrderByDescending to use the string property selector.

Answer 2

You can try converting the Generate method to 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 property you want to order with:

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

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