首页 > 代码库 > 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 }
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 }
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 }
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 }
5、SortingDbContext
1 public class SortingDbContext : DbContext2 {3 public DbSet<User> Users { get; set; }4 }
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();
生成SQL 和效果图
代码在此
如果谁有更好的方法请赐教!Thank you!
EF 实现多列排序
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。