相信在使用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 classUosoConditions { /// <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) { returnParseCondition(conditions.First()); } else { Expression left =ParseCondition(conditions.First()); Expression right = ParseExpressionBody(conditions.Skip(1)); returnExpression.AndAlso(left, right); } }
privateExpression ParseCondition(UosoConditions condition) { ParameterExpression p =parameter; Expression key =Expression.Property(p, condition.Key); Expression value =Expression.Constant(condition.Value); switch(condition.Operator) { caseUosoOperatorEnum.Contains: return Expression.Call(key, typeof(string).GetMethod("Contains",new Type[] { typeof(string) }), value); caseUosoOperatorEnum.Equal: returnExpression.Equal(key, Expression.Convert(value, key.Type)); caseUosoOperatorEnum.Greater: returnExpression.GreaterThan(key, Expression.Convert(value, key.Type)); caseUosoOperatorEnum.GreaterEqual: returnExpression.GreaterThanOrEqual(key, Expression.Convert(value, key.Type)); caseUosoOperatorEnum.Less: returnExpression.LessThan(key, Expression.Convert(value, key.Type)); caseUosoOperatorEnum.LessEqual: returnExpression.LessThanOrEqual(key, Expression.Convert(value, key.Type)); caseUosoOperatorEnum.NotEqual: returnExpression.NotEqual(key, Expression.Convert(value, key.Type)); caseUosoOperatorEnum.In: returnParaseIn(p, condition); caseUosoOperatorEnum.Between: returnParaseBetween(p, condition); default: throw new NotImplementedException("不支持此操作"); } }
这里对 In 和between 做了特殊处理
privateExpression 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)); returnExpression.AndAlso(start, end); }
privateExpression 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 invalueArr) { Expression value =Expression.Constant(itemVal); Expression right =Expression.Equal(key, Expression.Convert(value, key.Type));
expression = Expression.Or(expression, right); } returnexpression; }
第三步: 扩展分页、排序、查询条件
扩展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); returnquery.Where(filter); }
扩展多条件排序
public static IQueryable<T> OrderConditions<T>(this IQueryable<T> query, IEnumerable<UosoOrderConditions>orderConditions) { foreach (var orderinfo inorderConditions) { 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); } returnquery; }
扩展分页
public static IQueryable<T> Pager<T>(this IQueryable<T> query, int pageindex, int pagesize,out intitemCount) { 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 intitemcount) { return _userManager.Users.AsNoTracking().QueryConditions(conditions).OrderConditions(orderConditions).Pager(pageIndex, pageSize, outitemcount).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>{ newUosoOrderConditions{ Key="UserName", Order =OrderSequence.DESC }, newUosoOrderConditions{ Key="PhoneNumber", Order =OrderSequence.DESC } };
int itemcount = 0; var list = _userServices.GetPagedList2(uosoConditions, orderConditions, pageindex, pagesize, out itemcount);
第五步:结合前端分页样式实现整体(之前的有介绍)
ViewBag.Option = newUosoPagerOption() { 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")