Linq To Entities中的动态排序

时间:2021-06-11 06:16:31

换了工作有一个月了,一样的工作、一样的代码、一样的体力活仍就……

Linq To Entityes 也是不新玩意了,近半年来也一直与之打交道,但一直也没对其深究过。今天新加的功能要对所有列支持排序,这也不是什么高难度的工作了,对与TSQL来说是写过几百遍了,但在Linq To Enitities中有点小恶心。

Linq To Entityes中的对象是个Queryable类型的,Queryable.OrderBy()方法的参数是一个Lamdba

source.OrderBy(t=>t.ID).Skip(0).Take(10)

表达式。第一个想到的便是用字段名反射出类型,如

source.OrderBy(t=>t.GetType().GetField(“ID”)).Skip(0).Take(10);

而运行时会报“无法识别GetField()方法”。

Linq实际上是一个表达式树,是基于语言级别的代码,OrderBy中需要一个表达式树。

在老外的网站上找到了如下代码,豁然开朗

        public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property)
        { 
            return ApplyOrder<T>(source, property, "OrderBy"); 
        }
 
        public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string property)
        { 
            return ApplyOrder<T>(source, property, "OrderByDescending"); 
        }
 
        public static IOrderedQueryable<T> ThenBy<T>(this IOrderedQueryable<T> source, string property)
        {
            return ApplyOrder<T>(source, property, "ThenBy"); 
        }
 
        public static IOrderedQueryable<T> ThenByDescending<T>(this IOrderedQueryable<T> source, string property)
        { 
            return ApplyOrder<T>(source, property, "ThenByDescending"); 
        }
 
        static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, string methodName)
        {
            string[] props = property.Split('.');
            Type type = typeof(T); ParameterExpression arg = Expression.Parameter(type, "x");
            Expression expr = arg; foreach (string prop in props)
            { 
                // use reflection (not ComponentModel) to mirror LINQ 
                PropertyInfo pi = type.GetProperty(prop); expr = Expression.Property(expr, pi); type = pi.PropertyType;
            } 
            Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type); 
            LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg); 
            object result = typeof(Queryable).GetMethods().Single(method => method.Name == methodName 
                            && method.IsGenericMethodDefinition 
                            && method.GetGenericArguments().Length == 2 
                            && method.GetParameters().Length == 2)
                            .MakeGenericMethod(typeof(T), type)
                            .Invoke(null, new object[] { source, lambda }); 
            return (IOrderedQueryable<T>)result;
        }

上面的代码满足所有的排序功能了,我的项目中只需要对单列排序,所以对代码做了简化

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source"></param>
        /// <param name="property">排序字段</param>
        /// <param name="isAscdening"></param>
        /// <returns></returns>
        static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, bool isAscdening)
        {
            Type type = typeof(T);
            ParameterExpression arg = Expression.Parameter(type, "x");
            Expression expr = arg;
 
            PropertyInfo pi = type.GetProperty(property);
            expr = Expression.Property(expr, pi);
            type = pi.PropertyType;
 
            Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
            LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);
 
            object result;
            if (true == isAscdening)
            {
                result = typeof(Queryable).GetMethods().
                    Single(method => method.Name == "OrderBy" && method.IsGenericMethodDefinition
                        && method.GetGenericArguments().Length == 2 && method.GetParameters().Length == 2).
                    MakeGenericMethod(typeof(T), type)
                    .Invoke(null, new object[] { source, lambda });
            }
            else
            {
                result = typeof(Queryable).GetMethods().
                    Single(method => method.Name == "OrderByDescending" && method.IsGenericMethodDefinition
                        && method.GetGenericArguments().Length == 2 && method.GetParameters().Length == 2).
                    MakeGenericMethod(typeof(T), type)
                    .Invoke(null, new object[] { source, lambda });
            }
            return (IOrderedQueryable<T>)result;
        } 

还可以将方法做Queryable的扩展方法,使用起来会更方便

根据列名动态生成查询


        static IQueryable<T> MakeQuery<T>(IQueryable<T> source, string colName, string colValue)
        { 
           Type theType = typeof(T);
            //创建一个参数c
            ParameterExpression param = Expression.Parameter(typeof(T), "c");
            //c.City=="London"
            Expression left = Expression.Property(param,typeof(T).GetProperty(colName));
            Expression right = Expression.Constant(colValue);
            Expression filter = Expression.Equal(left, right);             Expression pred = Expression.Lambda(filter, param);
            //Where(c=>c.City=="London")
            Expression expr = Expression.Call(typeof(Queryable), "Where",
                new Type[] { typeof(T) }, 
                Expression.Constant(source), pred);
            //生成动态查询
            IQueryable<T> query = source.AsQueryable()
                .Provider.CreateQuery<T>(expr);
            return query;
        }