动态LINQ构建(实现等于不等于大于小于,like以及IN)
首先感谢园子里的“红烧狮子头”,他的工作是本文的基础,引文如下http://www.cnblogs.com/daviddai/archive/2013/03/09/2952087.html,本版本实现了类似SQL中的like与in的功能,实现了多orderby的级联排序,下面贴出代码:
一些辅助类:
public enum ConditionFlags { And = 0, Or = 1, } public enum RelationFlags { Equal = 0, NoEqual = 1, GreaterThan = 2, LessThan = 4, GreaterThanOrEqual = 8, LessThanOrEqual = 16, LikeWildcardBoth = 32, LikeLeftWildcardOnly = 64, LikeRightWildcardOnly = 128, In = 256, } public class QueryCondition { public string ConditionField { get; set; } public object FieldValue { get; set; } public ConditionFlags Condition { get; set; } public RelationFlags Relation { get; set; } } public class OrderByCondition { public string OrderField { get; set; } public bool IsDesc { get; set; } }
实现IEnumerable的实现方法:
public static IEnumerable<T> GetDataByDynamicQuery<T>(this IEnumerable<T> sourceList, List<QueryCondition> queryConditionList, params OrderByCondition[] orderByConditionList) { if (null == sourceList) { throw new ArgumentException("source list must not be null"); } IQueryable<T> sourceLs = sourceList.AsQueryable(); Expression finalExpr; Expression filter, totalExpr; filter = totalExpr = Expression.Constant(true); ParameterExpression param = Expression.Parameter(typeof(T), "n"); foreach (var item in queryConditionList ?? Enumerable.Empty<QueryCondition>()) { //反射找出所有查询条件的属性值,如果该查询条件值为空或者null不添加动态lambda表达式 string propertyName = item.ConditionField; var propertyVal = item.FieldValue; if (!string.IsNullOrEmpty(propertyName) && propertyVal != null && propertyVal.ToString() != string.Empty) { //n.property PropertyInfo property = typeof(T).GetProperty(propertyName); Expression left = Expression.Property(param, property); //等式右边的值 Expression right = Expression.Constant(propertyVal); MethodInfo containsmethod; switch (item.Relation) { case RelationFlags.Equal: if (typeof(string) == property.PropertyType) { containsmethod = typeof(string).GetMethod("Equals", new[] { typeof(string), typeof(string), typeof(StringComparison) }); filter = Expression.Call(containsmethod,left, right, Expression.Constant(StringComparison.OrdinalIgnoreCase)); } else { filter = Expression.Equal(left, right); } break; case RelationFlags.NoEqual: filter = Expression.NotEqual(left, right); break; case RelationFlags.GreaterThan: filter = Expression.GreaterThan(left, right); break; case RelationFlags.LessThan: filter = Expression.LessThan(left, right); break; case RelationFlags.GreaterThanOrEqual: filter = Expression.GreaterThanOrEqual(left, right); break; case RelationFlags.LessThanOrEqual: filter = Expression.LessThanOrEqual(left, right); break; case RelationFlags.LikeWildcardBoth: if (typeof (string) == property.PropertyType) { containsmethod = property.PropertyType.GetMethod("Contains", new Type[] { property.PropertyType }); filter = Expression.Call(left, containsmethod, right); } else { throw new ArgumentException(" 'like %x%' operator work on string type only "); } break; case RelationFlags.LikeLeftWildcardOnly: if (typeof (string) == property.PropertyType) { containsmethod = property.PropertyType.GetMethod("EndsWith", new Type[] { property.PropertyType }); filter = Expression.Call(left, containsmethod, right); } else { throw new ArgumentException(" 'like %x' operator work on string type only "); } break; case RelationFlags.LikeRightWildcardOnly: if (typeof (string) == property.PropertyType) { containsmethod = property.PropertyType.GetMethod("StartsWith", new Type[] { property.PropertyType }); filter = Expression.Call(left, containsmethod, right); } else { throw new ArgumentException(" 'like x%' operator work on string type only "); } break; case RelationFlags.In: Expression rightTmp, filterTmp; Expression totalExprTmp = Expression.Constant(false); foreach (var itemValue in propertyVal as IEnumerable ?? Enumerable.Empty<object>()) { rightTmp = Expression.Constant(itemValue); if (typeof(string) == property.PropertyType) { containsmethod = typeof(string).GetMethod("Equals", new[] { typeof(string), typeof(string), typeof(StringComparison) }); filterTmp = Expression.Call(containsmethod,left, rightTmp, Expression.Constant(StringComparison.OrdinalIgnoreCase)); } else { filterTmp = Expression.Equal(left, rightTmp); } totalExprTmp = Expression.Or(filterTmp, totalExprTmp); } filter = totalExprTmp = Expression.And(totalExprTmp, Expression.Constant(true)); break; default: filter = Expression.Constant(true); break; } switch (item.Condition) { case ConditionFlags.And: totalExpr = Expression.And(totalExpr, filter); break; case ConditionFlags.Or: totalExpr = Expression.Or(totalExpr.NodeType.Equals(Expression.Constant(true).NodeType) ? Expression.Constant(false) : totalExpr, filter); break; default: break; } } } //Where部分条件 Expression pred = Expression.Lambda(totalExpr, param); finalExpr = Expression.Call(typeof(Queryable), "Where", new Type[] { typeof(T) }, Expression.Constant(sourceLs), pred); string orderByFunctionName = "OrderBy"; string orderByDescFunctionName = "OrderByDescending"; foreach (var orderbyCondition in orderByConditionList ?? Enumerable.Empty<OrderByCondition>()) { PropertyInfo pInfo = typeof(T).GetProperty(orderbyCondition.OrderField); //OrderBy部分排序 finalExpr = Expression.Call(typeof(Queryable), orderbyCondition.IsDesc ? orderByDescFunctionName : orderByFunctionName, new Type[] { typeof(T), pInfo.PropertyType }, finalExpr, Expression.Lambda(Expression.Property(param, pInfo), param)); orderByFunctionName = "ThenBy"; orderByDescFunctionName = "ThenByDescending"; } return sourceLs.Provider.CreateQuery<T>(finalExpr); }
调用示例:
if (queryEntity.ApplicationIds != null && queryEntity.ApplicationIds.Length > 0) { var queryCondition = new QueryCondition { ConditionField = "ApplicationId", FieldValue = queryEntity.ApplicationIds, Condition = ConditionFlags.And, Relation = RelationFlags.In }; queryConditionList.Add(queryCondition); } if (!string.IsNullOrWhiteSpace(queryEntity.LinkPath)) { var queryCondition = new QueryCondition(); queryCondition.ConditionField = "LinkPath"; queryCondition.FieldValue = queryEntity.LinkPath; queryCondition.Condition = ConditionFlags.And; queryCondition.Relation = RelationFlags.Equal; queryConditionList.Add(queryCondition); } var orderByConditionList = new List<OrderByCondition>(); var orderByCondition = new OrderByCondition {OrderField = "SortIndex", IsDesc = true}; orderByConditionList.Add(orderByCondition); var orderByConditionMenuId = new OrderByCondition {OrderField = "MenuId", IsDesc = false}; orderByConditionList.Add(orderByConditionMenuId); return alllist.GetDataByDynamicQuery<ControlPanelMenuEntity>(queryConditionList, orderByConditionList.ToArray()).ToList();