Saleh Saleh - 23 days ago 12
C# Question

Setting a dynamic sort name field in a linq query

I want to be able to get an

OrderBy
query working with a lambda expression so that I get a SQL query with the TOP(n) key word (big performance boost).

I am able to do this if I specifiy ...

PaginatedList = Query.OrderBy(x => x.QuoteID).Skip(() => skipValue).Take(() => pageSize)


But because I want the orderBy field to be dynamic through a UI selection of a name I want to do something like this:

var propertyInfo = typeof(Data.Quote).GetProperty(sortName);
Expression<Func<Data.Quote, object>> orderField = x => propertyInfo.GetValue(x, null);
PaginatedList = Query.OrderBy(orderField).Skip(() => skipValue).Take(() => pageSize)


This gives me the 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."


I tried this that's not of type
Expression<Func<T, object>>


var propertyInfo = typeof(Data.Quote).GetProperty(sortName);
Func<Data.Quote, object> orderField = x => propertyInfo.GetValue(x, null);
PaginatedList = Query.OrderBy(x => orderField).Skip(() => skipValue).Take(() => pageSize)


And I get this error:


"Unable to create a constant value of type [...]. Only primitive types
or enumeration types are supported in this context"


I'm sure there is a way to achieve this but at the moment not sure how.

Answer

Here is how to achieve what you want:

var propertyInfo = typeof(Data.Quote).GetProperty(sortName);

ParameterExpression parameter = Expression.Parameter(typeof(T), "s");
MemberExpression property = Expression.Property(parameter, propertyInfo);
LambdaExpression sort = Expression.Lambda(property, parameter);

MethodCallExpression call = Expression.Call(
                                         typeof(Queryable),
                                         "OrderBy",
                                         new[] {typeof(T), property.Type},
                                         Query.Expression,
                                         Expression.Quote(sort));

var orderedQuery = (IOrderedQueryable<T>)Query.Provider.CreateQuery<T>(call);

PaginatedList = orderedQuery.Skip(skipValue).Take(pageSize);