如何在实体框架中进行动态排序

本文关键字：动态 排序 框架 实体 | 更新日期: 2023-09-27 18:08:37

我有一个这样声明的字典:

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
    };

我尝试使用以下代码带来数据:

    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]);
        }
        else
        {
            orderBy = x => x.OrderByDescending(_orders[sortField]);
        }
    }
    return Ok(this.DbService.Query(null, filterQuery));

， Query方法为:

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;
}

但是当排序列不是string时，我获得以下异常

"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()"}

我认为字典声明和/或初始化是错误的，因为如果我没有浏览器设置的任何排序，那么默认顺序将是x=>x.Id(内联声明)，即使Id是long，它也不会崩溃。我可以用另一种方式声明字典来解决问题吗?

问题解决了

我删除了字典，并添加了以下扩展，接收字段名称和排序模式作为参数

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);
            }
        }
        else
        {
            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,
                                                         Expression.Quote(orderByExp));
        //return  source.OrderBy(x => orderByExp);
        return source.Provider.CreateQuery<T>(resultExp);
    }
}

Ivan Stoev works

如何在实体框架中进行动态排序

字典定义是可以的-没有好的方法来声明它具有不同类型的值。

问题是Expression<Func<T, object>>定义为值类型属性生成额外的Expression.Convert。要使它与EF一起工作，必须删除转换表达式，并且必须动态调用相应的Queryable方法。它可以封装在自定义扩展方法中，如下所示:

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); 
    }
}

，在您的场景中可以这样使用:

if (!string.IsNullOrEmpty(sortField) && _orders.ContainsKey(sortField))
    orderBy = x => x.OrderBy(_orders[sortField], sortOrder != "desc");
相关文章：