roroinpho21 roroinpho21 - 1 year ago 154
C# Question

How to make a dynamic order in Entity Framework

I have a dictionary declared like this:

private Dictionary<string, Expression<Func<Part, object>>> _orders = new Dictionary<string, Expression<Func<Part, object>>>()
{"Name", x => x.Name}, //string
{"Code", x => x.Code}, //string
{"EnterPrice", x => x.EnterPrice}, //decimal
{"ExitPrice", x => x.ExitPrice}, //decimal
{"IsActive", x => (bool)x.Active }, //bool
{"Quantity", x => x.Quantity}, //decimal
{"Reserved", x => x.Reserved}, //decimal

I try to bring data using the following code:

NameValueCollection filter = HttpUtility.ParseQueryString(Request.RequestUri.Query);
string sortField = filter["sortField"];
string sortOrder = filter["sortOrder"];
Func<IQueryable<Part>, IOrderedQueryable<Part>> orderBy = x => x.OrderBy(p => p.Id);
if (!string.IsNullOrEmpty(sortField) && _orders.ContainsKey(sortField))
bool sortMode = !string.IsNullOrEmpty(sortOrder) && sortOrder != "desc";
if (sortMode)
orderBy = x => x.OrderBy(_orders[sortField]);
orderBy = x => x.OrderByDescending(_orders[sortField]);
return Ok(this.DbService.Query(null, filterQuery));

method is:

public IQueryable<TEntity> Query(Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null, bool noTracking = true)

IQueryable<TEntity> query = DbContext.Set<TEntity>();
if (filter != null)
query = query.Where(filter);
if (orderBy != null) query = orderBy(query);
return noTracking ? query.AsNoTracking() : query;

But when the sort column is not
I obtain the following exception

"Unable to cast the type 'System.Boolean' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.","ExceptionType":"System.NotSupportedException","StackTrace":" at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()"}

I think that the dictionary declaration and/or initialization is wrong because if I do not have any sort set by browser then the default order will be
(which is declared inline) and it doesn't crash even if
. Can I declare the dictionary in a different way to solve my problem?


I removed the dictionary and I added the following extension which receive the field name and sort mode as parametters

public static class LinqExtension
public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, bool ascending = true)
var type = typeof(T);
var parameter = Expression.Parameter(type, "p");
PropertyInfo property;
Expression propertyAccess;
if (ordering.Contains('.'))
// support to be sorted on child fields.
String[] childProperties = ordering.Split('.');
property = type.GetProperty(childProperties[0]);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
for (int i = 1; i < childProperties.Length; i++)
property = property.PropertyType.GetProperty(childProperties[i]);
propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
property = typeof(T).GetProperty(ordering);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
MethodCallExpression resultExp = Expression.Call(typeof(Queryable),
ascending ? "OrderBy" : "OrderByDescending",
new[] { type, property.PropertyType }, source.Expression,
//return source.OrderBy(x => orderByExp);
return source.Provider.CreateQuery<T>(resultExp);

Also solution provided by Ivan Stoev works

Answer Source

The dictionary definition is ok - there is no good way to declare it to have values with different type.

The problem is that Expression<Func<T, object>> definition generates additional Expression.Convert for value type properties. To make it work with EF, the convert expression must be removed and corresponding Queryable method must be called dynamically. It can be encapsulated in a custom extension method like this:

public static class QueryableExtensions
    public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, Expression<Func<T, object>> keySelector, bool ascending)
        var selectorBody = keySelector.Body;
        // Strip the Convert expression
        if (selectorBody.NodeType == ExpressionType.Convert)
            selectorBody = ((UnaryExpression)selectorBody).Operand;
        // Create dynamic lambda expression
        var selector = Expression.Lambda(selectorBody, keySelector.Parameters);
        // Generate the corresponding Queryable method call
        var queryBody = Expression.Call(typeof(Queryable),
            ascending ? "OrderBy" : "OrderByDescending",
            new Type[] { typeof(T), selectorBody.Type },
            source.Expression, Expression.Quote(selector));
        return source.Provider.CreateQuery<T>(queryBody); 

and the usage in your scenario could be like this:

if (!string.IsNullOrEmpty(sortField) && _orders.ContainsKey(sortField))
    orderBy = x => x.OrderBy(_orders[sortField], sortOrder != "desc");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download