相信在使用EF的时候对查询条件或者排序上的处理令人心烦,下面我们就来动态拼接表达式解决这一问题
当我们在查询中使用Where的时候可以看到如下参数
下面我们就来扩展 Expression<Func<T,bool>> 这个参数
第一步: 建立处理功能类
首先我们要创建一个查询条件转化为表达式的泛型功能类 如 UosoExpressionParser<T> 至于为什么要用泛型类目的很明确就是为了适配不同的模型参数
转化条件为表达式 那么处理一个方法来接受条件 返回表达式,条件可以按照自己的模式去设置
public Expression<Func<T, bool>> ParserConditions(IEnumerable<UosoConditions> conditions) { //将条件转化成表达是的Body var query = ParseExpressionBody(conditions); return Expression.Lambda<Func<T, bool>>(query, parameter); }
public class UosoConditions { /// <summary> /// 字段名称 /// </summary> public string Key { get; set; } /// <summary> /// 值 /// </summary> public string Value { get; set; } /// <summary> /// 值类型 /// </summary> public string ValueType { get; set; } /// <summary> /// /// </summary> public UosoOperatorEnum Operator { get; set; } }
第二步: 条件转表达式具体处理
具体去实现 ParseExpressionBody 条件 枚举提供操作方式 如:(like 、 = 、!= 、> 、< 、>= 、<= 、in 、 between)
private Expression ParseExpressionBody(IEnumerable<UosoConditions> conditions) { if (conditions == null || conditions.Count() == 0) { return Expression.Constant(true, typeof(bool)); } else if (conditions.Count() == 1) { return ParseCondition(conditions.First()); } else { Expression left = ParseCondition(conditions.First()); Expression right = ParseExpressionBody(conditions.Skip(1)); return Expression.AndAlso(left, right); } }
private Expression ParseCondition(UosoConditions condition) { ParameterExpression p = parameter; Expression key = Expression.Property(p, condition.Key); Expression value = Expression.Constant(condition.Value); switch (condition.Operator) { case UosoOperatorEnum.Contains: return Expression.Call(key, typeof(string).GetMethod("Contains",new Type[] { typeof(string) }), value); case UosoOperatorEnum.Equal: return Expression.Equal(key, Expression.Convert(value, key.Type)); case UosoOperatorEnum.Greater: return Expression.GreaterThan(key, Expression.Convert(value, key.Type)); case UosoOperatorEnum.GreaterEqual: return Expression.GreaterThanOrEqual(key, Expression.Convert(value, key.Type)); case UosoOperatorEnum.Less: return Expression.LessThan(key, Expression.Convert(value, key.Type)); case UosoOperatorEnum.LessEqual: return Expression.LessThanOrEqual(key, Expression.Convert(value, key.Type)); case UosoOperatorEnum.NotEqual: return Expression.NotEqual(key, Expression.Convert(value, key.Type)); case UosoOperatorEnum.In: return ParaseIn(p, condition); case UosoOperatorEnum.Between: return ParaseBetween(p, condition); default: throw new NotImplementedException("不支持此操作"); } }
这里对 In 和between 做了特殊处理
private Expression ParaseBetween(ParameterExpression parameter, UosoConditions conditions) { ParameterExpression p = parameter; Expression key = Expression.Property(p, conditions.Key); var valueArr = conditions.Value.Split(','); if (valueArr.Length != 2) { throw new NotImplementedException("ParaseBetween参数错误"); } try { int.Parse(valueArr[0]); int.Parse(valueArr[1]); } catch { throw new NotImplementedException("ParaseBetween参数只能为数字"); } Expression expression = Expression.Constant(true, typeof(bool)); //开始位置 Expression startvalue = Expression.Constant(int.Parse(valueArr[0])); Expression start = Expression.GreaterThanOrEqual(key, Expression.Convert(startvalue, key.Type)); Expression endvalue = Expression.Constant(int.Parse(valueArr[1])); Expression end = Expression.GreaterThanOrEqual(key, Expression.Convert(endvalue, key.Type)); return Expression.AndAlso(start, end); }
private Expression ParaseIn(ParameterExpression parameter, UosoConditions conditions) { ParameterExpression p = parameter; Expression key = Expression.Property(p, conditions.Key); var valueArr = conditions.Value.Split(','); Expression expression = Expression.Constant(true, typeof(bool)); foreach (var itemVal in valueArr) { Expression value = Expression.Constant(itemVal); Expression right = Expression.Equal(key, Expression.Convert(value, key.Type));
expression = Expression.Or(expression, right); } return expression; }
第三步: 扩展分页、排序、查询条件
扩展 IQueryable<T> 就OK了,下面是我扩展的查询 排序 分页处理
扩展查询
public static IQueryable<T> QueryConditions<T>(this IQueryable<T> query, IEnumerable<UosoConditions> conditions) { var parser = new UosoExpressionParser<T>(); var filter = parser.ParserConditions(conditions); return query.Where(filter); }
扩展多条件排序
public static IQueryable<T> OrderConditions<T>(this IQueryable<T> query, IEnumerable<UosoOrderConditions> orderConditions) { foreach (var orderinfo in orderConditions) { var t = typeof(T); var propertyInfo = t.GetProperty(orderinfo.Key); var parameter = Expression.Parameter(t); Expression propertySelector = Expression.Property(parameter, propertyInfo); var orderby = Expression.Lambda<Func<T, object>>(propertySelector, parameter); if (orderinfo.Order == OrderSequence.DESC) query = query.OrderByDescending(orderby); else query = query.OrderBy(orderby); } return query; }
扩展分页
public static IQueryable<T> Pager<T>(this IQueryable<T> query, int pageindex, int pagesize,out int itemCount) { itemCount = query.Count(); return query.Skip((pageindex - 1) * pagesize).Take(pagesize); }
扩展基本完成了,接下来就是使用方式 下面是我写的查询分页方式
第四步: 具体使用方式
public IList<IdentityUser> GetPagedList2(IEnumerable<UosoConditions> conditions,IEnumerable<UosoOrderConditions> orderConditions,int pageIndex, int pageSize,out int itemcount) { return _userManager.Users.AsNoTracking().QueryConditions(conditions).OrderConditions(orderConditions).Pager(pageIndex, pageSize, out itemcount).ToList(); }
你需要构建相关的查询排序集合类就行了 如下:
List<UosoConditions> uosoConditions = new List<UosoConditions>() { new UosoConditions { Key = "UserName", Operator = UosoOperatorEnum.Contains, Value = "1,3", ValueType = "string" } }; List<UosoOrderConditions> orderConditions = new List<UosoOrderConditions> { new UosoOrderConditions{ Key="UserName", Order = OrderSequence.DESC }, new UosoOrderConditions{ Key="PhoneNumber", Order = OrderSequence.DESC } };
int itemcount = 0; var list = _userServices.GetPagedList2(uosoConditions, orderConditions, pageindex, pagesize, out itemcount);
第五步:结合前端分页样式实现整体(之前的有介绍)
ViewBag.Option = new UosoPagerOption() { ItemCount = itemcount, PageSize = pagesize, PageIndex = pageindex, CountNum = 5, Url = Request.Path.Value, Query = Request.Query };
以上是实现分页的全部过程,这里值得注意的是 在 like查询 Contains的时候,在.NetCore中需要如下这样写,不然可能会出现反射多次被实例化的问题
typeof(string).GetMethod("Contains",new Type[] { typeof(string) })
如果是.Net Framework 中 为如下方式
typeof(string).GetMethod("Contains")
原文转发自:https://www.cnblogs.com/liyouming/p/9402113.html