首页 > 代码库 > EF 实现多列排序

EF 实现多列排序

1、ExpressionMaker

技术分享
1     public class ExpressionMaker2     {3         public static Expression<Func<T, TKey>> MakeExperssion<T, TKey>(string propertyName)4         {5             ParameterExpression parameter = Expression.Parameter(typeof(T), "e");6             return Expression.Lambda<Func<T, TKey>>(Expression.Property(parameter, propertyName), parameter);7         }8     }
View Code

2、SortExpression

技术分享
  1     public enum Sort  2     {  3         None = 0,  4         Asc = 1,  5         Desc = 2  6     }  7   8     public class SortExpression<TEntity>  9     { 10         public Sort Sort { get; set; } 11         public Type KeyType { get; set; } 12         public Expression KeySelector { get; set; } 13  14         private SortExpression() 15         { 16             Sort = Sort.Asc; 17             KeyType = typeof(String); 18         } 19  20         public SortExpression(Expression keySelector) : this() 21         { 22             KeySelector = keySelector; 23         } 24  25         public SortExpression(Expression keySelector, Sort sort) : this(keySelector) 26         { 27             Sort = sort; 28         } 29  30         public SortExpression(Expression keySelector, Type keyType) : this(keySelector) 31         { 32             KeyType = keyType; 33         } 34  35         public SortExpression(Expression keySelector, Sort sort, Type keyType) : this(keySelector, sort) 36         { 37             KeyType = keyType; 38         } 39  40         public SortExpression(string propertyName) : this() 41         { 42             var propertyType = typeof(TEntity).GetProperty(propertyName).PropertyType; 43  44             if (propertyType == typeof(string)) 45             { 46                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, string>(propertyName); 47             } 48             else if (propertyType == typeof(DateTime)) 49             { 50                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, DateTime>(propertyName); 51             } 52             else if (propertyType == typeof(DateTime?)) 53             { 54                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, DateTime?>(propertyName); 55             } 56             else if (propertyType == typeof(int)) 57             { 58                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, int>(propertyName); 59             } 60             else if (propertyType == typeof(int?)) 61             { 62                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, int?>(propertyName); 63             } 64             else if (propertyType == typeof(decimal)) 65             { 66                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, decimal>(propertyName); 67             } 68             else if (propertyType == typeof(decimal?)) 69             { 70                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, decimal?>(propertyName); 71             } 72             else if (propertyType == typeof(bool)) 73             { 74                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, bool>(propertyName); 75             } 76             else if (propertyType == typeof(bool?)) 77             { 78                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, bool?>(propertyName); 79             } 80             else if (propertyType == typeof(double)) 81             { 82                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, double>(propertyName); 83             } 84             else if (propertyType == typeof(double?)) 85             { 86                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, double?>(propertyName); 87             } 88             else if (propertyType == typeof(float)) 89             { 90                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, float>(propertyName); 91             } 92             else if (propertyType == typeof(float?)) 93             { 94                 KeySelector = ExpressionMaker.MakeExperssion<TEntity, float?>(propertyName); 95             } 96             else 97             { 98                 throw new NotSupportedException("Unsupported data type:" + propertyType); 99             }100         }101 102         public SortExpression(string propertyName, Sort sort) : this(propertyName)103         {104             Sort = sort;105         }106 107         public SortExpression(string propertyName, Type keyType) : this(propertyName)108         {109             KeyType = keyType;110         }111 112         public SortExpression(string propertyName, Sort sort, Type keyType) : this(propertyName, sort)113         {114             KeyType = keyType;115         }116     }
View Code

3、OrderByExpression

技术分享
  1  public static class OrderByExpression  2     {  3         public static IQueryable<T> OrderByCustom<T>(this IQueryable<T> query, params SortExpression<T>[] sortExperssions)  4         {  5             if (sortExperssions == null) return query;  6             if (sortExperssions.Length == 1) return query.OrderByExperssion(sortExperssions[0]);  7             int index = 0;  8             foreach (var sortExperssion in sortExperssions)  9             { 10                 if (index++ == 0) 11                 { 12                     query = query.OrderByExperssion(sortExperssion); 13                 } 14                 else 15                 { 16                     query = query.ThenByExperssion(sortExperssion); 17                 } 18             } 19             return query; 20         } 21  22         public static IQueryable<T> OrderByExperssion<T>(this IQueryable<T> query, SortExpression<T> sortExperssion) 23         { 24             if (sortExperssion.Sort == Sort.Asc) 25                 return query.OrderByExperssionAsc(sortExperssion.KeySelector, sortExperssion.KeyType); 26             else 27                 return query.OrderByExperssionDesc(sortExperssion.KeySelector, sortExperssion.KeyType); 28         } 29  30         private static IQueryable<T> OrderByExperssionAsc<T>(this IQueryable<T> query, Expression keySelector, Type propertyType) 31         { 32             if (propertyType == typeof(string)) 33             { 34                 return query.OrderBy((Expression<Func<T, string>>)keySelector); 35             } 36             if (propertyType == typeof(DateTime)) 37             { 38                 return query.OrderBy((Expression<Func<T, DateTime>>)keySelector); 39             } 40             if (propertyType == typeof(DateTime?)) 41             { 42                 return query.OrderBy((Expression<Func<T, DateTime?>>)keySelector); 43             } 44  45             if (propertyType == typeof(int)) 46             { 47                 return query.OrderBy((Expression<Func<T, int>>)keySelector); 48             } 49             if (propertyType == typeof(int?)) 50             { 51                 return query.OrderBy((Expression<Func<T, int?>>)keySelector); 52             } 53  54             if (propertyType == typeof(decimal)) 55             { 56                 return query.OrderBy((Expression<Func<T, decimal>>)keySelector); 57             } 58             if (propertyType == typeof(decimal?)) 59             { 60                 return query.OrderBy((Expression<Func<T, decimal?>>)keySelector); 61             } 62  63             if (propertyType == typeof(bool)) 64             { 65                 return query.OrderBy((Expression<Func<T, bool>>)keySelector); 66             } 67             if (propertyType == typeof(bool?)) 68             { 69                 return query.OrderBy((Expression<Func<T, bool?>>)keySelector); 70             } 71  72             if (propertyType == typeof(double)) 73             { 74                 return query.OrderBy((Expression<Func<T, double>>)keySelector); 75             } 76             if (propertyType == typeof(double?)) 77             { 78                 return query.OrderBy((Expression<Func<T, double?>>)keySelector); 79             } 80  81             if (propertyType == typeof(float)) 82             { 83                 return query.OrderBy((Expression<Func<T, float>>)keySelector); 84             } 85             if (propertyType == typeof(float?)) 86             { 87                 return query.OrderBy((Expression<Func<T, float?>>)keySelector); 88             } 89             throw new NotSupportedException("Unsupported data type:" + propertyType); 90         } 91  92         private static IQueryable<T> OrderByExperssionDesc<T>(this IQueryable<T> query, Expression keySelector, Type propertyType) 93         { 94             if (propertyType == typeof(string)) 95             { 96                 return query.OrderByDescending((Expression<Func<T, string>>)keySelector); 97             } 98             if (propertyType == typeof(DateTime)) 99             {100                 return query.OrderByDescending((Expression<Func<T, DateTime>>)keySelector);101             }102             if (propertyType == typeof(DateTime?))103             {104                 return query.OrderByDescending((Expression<Func<T, DateTime?>>)keySelector);105             }106 107             if (propertyType == typeof(int))108             {109                 return query.OrderByDescending((Expression<Func<T, int>>)keySelector);110             }111             if (propertyType == typeof(int?))112             {113                 return query.OrderByDescending((Expression<Func<T, int?>>)keySelector);114             }115 116             if (propertyType == typeof(decimal))117             {118                 return query.OrderByDescending((Expression<Func<T, decimal>>)keySelector);119             }120             if (propertyType == typeof(decimal?))121             {122                 return query.OrderByDescending((Expression<Func<T, decimal?>>)keySelector);123             }124 125             if (propertyType == typeof(bool))126             {127                 return query.OrderByDescending((Expression<Func<T, bool>>)keySelector);128             }129             if (propertyType == typeof(bool?))130             {131                 return query.OrderByDescending((Expression<Func<T, bool?>>)keySelector);132             }133 134             if (propertyType == typeof(double))135             {136                 return query.OrderByDescending((Expression<Func<T, double>>)keySelector);137             }138             if (propertyType == typeof(double?))139             {140                 return query.OrderByDescending((Expression<Func<T, double?>>)keySelector);141             }142 143             if (propertyType == typeof(float))144             {145                 return query.OrderByDescending((Expression<Func<T, float>>)keySelector);146             }147             if (propertyType == typeof(float?))148             {149                 return query.OrderByDescending((Expression<Func<T, float?>>)keySelector);150             }151 152             throw new NotSupportedException("Unsupported data type:" + propertyType);153         }154 155         public static IQueryable<T> ThenByExperssion<T>(this IQueryable<T> query, SortExpression<T> sortExperssion)156         {157             if (sortExperssion.Sort == Sort.Asc)158                 return query.ThenByExperssionAsc(sortExperssion.KeySelector, sortExperssion.KeyType);159             else160                 return query.ThenByExperssionDesc(sortExperssion.KeySelector, sortExperssion.KeyType);161         }162 163         private static IQueryable<T> ThenByExperssionAsc<T>(this IQueryable<T> query, Expression keySelector, Type propertyType)164         {165             if (propertyType == typeof(string))166             {167                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, string>>)keySelector);168             }169             if (propertyType == typeof(DateTime))170             {171                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, DateTime>>)keySelector);172             }173             if (propertyType == typeof(DateTime?))174             {175                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, DateTime?>>)keySelector);176             }177 178             if (propertyType == typeof(int))179             {180                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, int>>)keySelector);181             }182             if (propertyType == typeof(int?))183             {184                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, int?>>)keySelector);185             }186 187             if (propertyType == typeof(decimal))188             {189                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, decimal>>)keySelector);190             }191             if (propertyType == typeof(decimal?))192             {193                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, decimal?>>)keySelector);194             }195 196             if (propertyType == typeof(bool))197             {198                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, bool>>)keySelector);199             }200             if (propertyType == typeof(bool?))201             {202                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, bool?>>)keySelector);203             }204 205             if (propertyType == typeof(double))206             {207                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, double>>)keySelector);208             }209             if (propertyType == typeof(double?))210             {211                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, double?>>)keySelector);212             }213 214             if (propertyType == typeof(float))215             {216                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, float>>)keySelector);217             }218             if (propertyType == typeof(float?))219             {220                 return ((IOrderedQueryable<T>)query).ThenBy((Expression<Func<T, float?>>)keySelector);221             }222 223             throw new NotSupportedException("Unsupported data type:" + propertyType);224         }225 226         private static IQueryable<T> ThenByExperssionDesc<T>(this IQueryable<T> query, Expression keySelector, Type propertyType)227         {228             if (propertyType == typeof(string))229             {230                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, string>>)keySelector);231             }232             if (propertyType == typeof(DateTime))233             {234                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, DateTime>>)keySelector);235             }236             if (propertyType == typeof(DateTime?))237             {238                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, DateTime?>>)keySelector);239             }240 241             if (propertyType == typeof(int))242             {243                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, int>>)keySelector);244             }245             if (propertyType == typeof(int?))246             {247                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, int?>>)keySelector);248             }249 250             if (propertyType == typeof(decimal))251             {252                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, decimal>>)keySelector);253             }254             if (propertyType == typeof(decimal?))255             {256                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, decimal?>>)keySelector);257             }258 259             if (propertyType == typeof(bool))260             {261                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, bool>>)keySelector);262             }263             if (propertyType == typeof(bool?))264             {265                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, bool?>>)keySelector);266             }267 268             if (propertyType == typeof(double))269             {270                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, double>>)keySelector);271             }272             if (propertyType == typeof(double?))273             {274                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, double?>>)keySelector);275             }276 277             if (propertyType == typeof(float))278             {279                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, float>>)keySelector);280             }281             if (propertyType == typeof(float?))282             {283                 return ((IOrderedQueryable<T>)query).ThenByDescending((Expression<Func<T, float?>>)keySelector);284             }285 286             throw new NotSupportedException("Unsupported data type:" + propertyType);287         }288 289     }
View Code

4、User 测试类

技术分享
1     public class User 2     {3         [Key]4         public int Id { get; set; }5         public string Name { get; set; }6         public string Gender { get; set; }7         public int Age { get; set; }8     }
View Code

5、SortingDbContext

技术分享
1     public class SortingDbContext : DbContext2     {3         public DbSet<User> Users { get; set; }4     }
View Code

6、调用

技术分享
 1 SortingDbContext db = new SortingDbContext(); 2  3 List<SortExpression<User>> sortExperssions = new List<SortExpression<User>>(); 4  5 Expression<Func<User, int>> exp1 = e => e.Age; 6 sortExperssions.Add(new SortExpression<User>(exp1, typeof(Int32))); 7  8 Expression<Func<User, string>> exp2 = e => e.Name; 9 sortExperssions.Add(new SortExpression<User>(exp2));10 11 Expression<Func<User, int>> exp3 = e => e.Name == "" ? 1 : 2;12 sortExperssions.Add(new SortExpression<User>(exp3, typeof(Int32)));13 14 var result = await db.Users.AsQueryable().OrderByCustom(sortExperssions.ToArray()).ToListAsync();
View Code

 

生成SQL 和效果图

技术分享

 

代码在此

 

如果谁有更好的方法请赐教!Thank you!

EF 实现多列排序