使用Extensions动态生成查询表达式树
根据自我项目的情况以及园子里高手的博文整理出的帮助，希望能帮助到大家。
1、场景介绍
前端按照列表中的字段动态配置筛选条件，要求后台也要根据不同的查询条件动态分配。
2、前后端约定
这部分使用字段 searchVal和searchVal_FilterMode和searchFormInputAttrs定义。如下
/// <summary>
/// 关键字模糊查询
/// </summary>
public string searchVal { get; set; }
/// <summary>
/// 通用查询配置 (1:模糊查询 , 2:精准查询)
/// </summary>
public int searchVal_FilterMode { get; set; }
/// <summary>
/// 子搜索组件传递的通用查询配置的属性名
/// </summary>
public List<string> searchFormInputAttrs { get; set; }
高级查询部分，要求筛选模式使用后缀 _FilterMode，如下示例
/// <summary> /// 工单号 /// </summary> public string WorkOrderNo { get; set; } /// <summary> /// 工单号-查询过滤模式 /// </summary> public string WorkOrderNo_FilterMode { get; set; }
如果是日期范围的话，使用List<String> 形式，如下示例
#region 单独处理日期 //表示是 高级查询范围查询特性 [HighSearchRangeAttribute] public List<string> CreateTime { get; set; } #endregion
/// <summary> /// 高级查询范围查询特性 /// </summary> public class HighSearchRangeAttribute : Attribute { }
配置的枚举类
/// <summary> /// 通用查询配置模式 /// </summary> public enum SearchFilterModeEnum { 模糊查询 = 1, 精准查询 = 2, 空值 = 3, 大于等于 = 4, 小于等于 = 5, 大于 = 6, 小于 = 7, 不等于 = 8 }
3、后端处理
生成Where条件扩展类
/// <summary> /// 生成Where条件扩展 /// </summary> public class WhereConditionsExtensions { public static FunReturnResultModel<Expression<Func<T, bool>>> GetWhereConditions<T, TParam>(TParam param) { FunReturnResultModel<Expression<Func<T, bool>>> result = new FunReturnResultModel<Expression<Func<T, bool>>>(); try { Expression<Func<T, bool>> conditions = PredicateExtensions.True<T>(); var errMsg = ""; #region 1、通用查询searchVal //判断通用查询searchVal var pro_value_searchVal = ClassHelper.GetPropertyValue<TParam>(param, SystemCommonVar.searchVal, out errMsg); if (!string.IsNullOrEmpty(errMsg)) { result.IsSuccess = false; result.ErrMsg = errMsg; return result; } if (!string.IsNullOrEmpty(pro_value_searchVal)) {//表示 通用查询searchVal不为空，需要查询 var pro_value_searchValMode = ClassHelper.GetPropertyValue<TParam>(param, SystemCommonVar.searchVal_FilterMode, out errMsg); if (!string.IsNullOrEmpty(errMsg)) { result.IsSuccess = false; result.ErrMsg = errMsg; return result; } var pro_value_searchFormInputAttrs = ClassHelper.GetPropertyValueForList<TParam>(param, SystemCommonVar.searchFormInputAttrs, out errMsg); if (!string.IsNullOrEmpty(errMsg)) { result.IsSuccess = false; result.ErrMsg = errMsg; return result; } if (pro_value_searchFormInputAttrs != null && pro_value_searchFormInputAttrs.Count > 0) { var i_pro_value_searchValMode = Convert.ToInt32(pro_value_searchValMode); if (i_pro_value_searchValMode == Convert.ToInt32(SearchFilterModeEnum.模糊查询)) {//模糊查询 var new_conditions = PredicateExtensions.GetConditionExpressionForMoreField<T>(pro_value_searchFormInputAttrs.ToArray(), pro_value_searchVal); conditions = conditions.And(new_conditions); } else if (i_pro_value_searchValMode == Convert.ToInt32(SearchFilterModeEnum.精准查询)) {//精准查询 var new_conditions = PredicateExtensions.GetConditionExpressionForMoreFieldByAnd<T>(pro_value_searchFormInputAttrs.ToArray(), pro_value_searchVal); conditions = conditions.And(new_conditions); /* pro_value_searchFormInputAttrs.ForEach(x => { if (!string.IsNullOrEmpty(x)) { var myParam = Expression.Parameter(typeof(T)); var condition = Expression.Lambda<Func<T, bool>>( Expression.Equal( Expression.Property(myParam, x), Expression.Constant(pro_value_searchVal, typeof(string)) ), myParam ); // for LINQ to SQl/Entities skip Compile() call conditions = conditions.And(condition); } }); //*/ } else { result.IsSuccess = false; result.ErrMsg = "通用查询配置不正确"; return result; } } } #endregion #region 2、高级查询 var high_pros = FilterHigh_pros<TParam>(param); List<HighSearchForDateTimeRangeModel> hsmForDatetimeList = new List<HighSearchForDateTimeRangeModel>(); List<HighSearchModel> hsmList = GetHighSearchModelList<TParam>(param, high_pros, ref hsmForDatetimeList); if (hsmList.Count > 0) { var high_conditions = PredicateExtensions.GetConditionExpressionForHighFieldByAnd<T>(hsmList); conditions = conditions.And(high_conditions); } if (hsmForDatetimeList.Count > 0) { var high_conditions = PredicateExtensions.GetConditionExpressionForHighFieldByAnd<T>(hsmForDatetimeList); conditions = conditions.And(high_conditions); } #endregion result.data = conditions; result.IsSuccess = true; return result; } catch { throw; } } /// <summary> /// 过滤掉Mode后缀的属性 和一些特殊属性的，如DateTimeRange /// </summary> /// <typeparam name="TParam"></typeparam> /// <param name="param"></param> /// <param name="high_pros"></param> /// <returns></returns> private static List<HighSearchModel> GetHighSearchModelList<TParam>(TParam param, List<PropertyInfo> high_pros, ref List<HighSearchForDateTimeRangeModel> hsmForDatetimeList) { List<HighSearchModel> hsmList = new List<HighSearchModel>(); string errMsg = ""; SearchFilterModeEnum _svmEnum = default(SearchFilterModeEnum); var hsmValue = ""; var pro_value = ""; foreach (var pro in high_pros) { if (!pro.Name.Contains(SystemCommonVar.highSearchModeSuffix)) {//不是Mode后缀的字段 //判断是不是有特性特性的值 var Attributes = ClassHelper.GetDIYAttributesByPro(pro); if (Attributes.Length > 0) { object v = ClassHelper.GetPropertyValue<TParam>(param, pro); if (v == null) { continue; } List<string> arr_value = v as List<string>; if (arr_value.Count < 2) { continue; } //string[] arr_value = (string[])v; //string[] arr_value = Convert.ToString(v).Split(','); hsmForDatetimeList.Add(new HighSearchForDateTimeRangeModel() { fieldName = pro.Name, start_fieldValue = arr_value[0], end_fieldValue = arr_value[1] }); continue; } pro_value = ClassHelper.GetPropertyValueByObject<TParam>(param, pro); if (string.IsNullOrEmpty(pro_value)) { continue; } //查找是相应Mode后缀的字段的值 hsmValue = ClassHelper.GetPropertyValue<TParam>(param, pro.Name + SystemCommonVar.highSearchModeSuffix, out errMsg); if (!string.IsNullOrEmpty(errMsg)) { throw new Exception(errMsg); } if (hsmValue == "0" || string.IsNullOrEmpty(hsmValue)) { continue; } _svmEnum = (SearchFilterModeEnum)Enum.Parse(typeof(SearchFilterModeEnum), hsmValue); if (_svmEnum == SearchFilterModeEnum.空值) { continue; } hsmList.Add(new HighSearchModel() { fieldName = pro.Name, fieldValue = pro_value, filterMode = _svmEnum }); } } return hsmList; } /// <summary> /// 过滤一些不需要的字段,如Page、PageSize等 /// </summary> /// <typeparam name="TParam"></typeparam> /// <param name="param"></param> /// <returns></returns> private static List<PropertyInfo> FilterHigh_pros<TParam>(TParam param) { var all_high_pros = ClassHelper.GetPropertyInfoList<TParam>(param); var high_pros = new List<PropertyInfo>(); //排除不用的 for (int i = 0; i < all_high_pros.Length; i++) { if (!SystemCommonVar.commnParamFatherPros.Contains(all_high_pros[i].Name)) { high_pros.Add(all_high_pros[i]); } } return high_pros; } }
public class ParameterRebinder : ExpressionVisitor { private readonly Dictionary<ParameterExpression, ParameterExpression> map; public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map) { this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>(); } public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp) { return new ParameterRebinder(map).Visit(exp); } protected override Expression VisitParameter(ParameterExpression p) { ParameterExpression replacement; if (map.TryGetValue(p, out replacement)) { p = replacement; } return base.VisitParameter(p); } } public static class PredicateExtensions { public static Expression<Func<T, bool>> True<T>() { return f => true; } public static Expression<Func<T, bool>> False<T>() { return f => false; } public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge) { // build parameter map (from parameters of second to parameters of first) var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f); // replace parameters in the second lambda expression with parameters from the first var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body); // apply composition of lambda expression bodies to parameters from the first expression return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters); } public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.And); } public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.Or); } /// <summary> /// 拼接成 c.Name.contains("1111")||c.Name.Contains("2222")||c.Name.Contains("3333")) 形式 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="options"></param> /// <param name="fieldName"></param> /// <returns></returns> public static Expression<Func<T, bool>> GetConditionExpression<T>(string[] options, string fieldName) { ParameterExpression left = Expression.Parameter(typeof(T), "c");//c=> Expression expression = Expression.Constant(false); foreach (var optionName in options) { Expression right = Expression.Call ( Expression.Property(left, typeof(T).GetProperty(fieldName)), //c.DataSourceName typeof(string).GetMethod("Contains", new Type[] { typeof(string) }),// 反射使用.Contains()方法 Expression.Constant(optionName) // .Contains(optionName) ); expression = Expression.Or(right, expression);//c.DataSourceName.contain("") || c.DataSourceName.contain("") } Expression<Func<T, bool>> finalExpression = Expression.Lambda<Func<T, bool>>(expression, new ParameterExpression[] { left }); return finalExpression; } /// <summary> /// 拼接成 c.Name.contains("1111")||c.Code.Contains("1111")||c.Address.Contains("1111")) 形式 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="options"></param> /// <param name="fieldName"></param> /// <returns></returns> public static Expression<Func<T, bool>> GetConditionExpressionForMoreField<T>(string[] fieldNames, string fieldValue) { ParameterExpression left = Expression.Parameter(typeof(T), "c");//c=> Expression expression = Expression.Constant(false); foreach (var fieldName in fieldNames) { Expression right = Expression.Call ( Expression.Property(left, typeof(T).GetProperty(fieldName)), //c.DataSourceName typeof(string).GetMethod("Contains", new Type[] { typeof(string) }),// 反射使用.Contains()方法 Expression.Constant(fieldValue) // .Contains(fieldValue) ); expression = Expression.Or(right, expression);//c.AAA.contain("") || c.BBB.contain("") } Expression<Func<T, bool>> finalExpression = Expression.Lambda<Func<T, bool>>(expression, new ParameterExpression[] { left }); return finalExpression; } /// <summary> /// 拼接成 c.Name.equals("1111")||c.Code.equals("1111")||c.Address.equals("1111")) 形式 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="options"></param> /// <param name="fieldName"></param> /// <returns></returns> public static Expression<Func<T, bool>> GetConditionExpressionForMoreFieldByAnd<T>(string[] fieldNames, string fieldValue) { ParameterExpression left = Expression.Parameter(typeof(T), "c");//c=> Expression expression = Expression.Constant(false); foreach (var fieldName in fieldNames) { Expression right = Expression.Call ( Expression.Property(left, typeof(T).GetProperty(fieldName)), //c.DataSourceName typeof(string).GetMethod("Equals", new Type[] { typeof(string) }),// 反射使用.Contains()方法 Expression.Constant(fieldValue) // .Contains(fieldValue) ); expression = Expression.Or(right, expression);//c.AAA.contain("") || c.BBB.contain("") } Expression<Func<T, bool>> finalExpression = Expression.Lambda<Func<T, bool>>(expression, new ParameterExpression[] { left }); return finalExpression; } public static Expression<Func<T, bool>> GetConditionExpressionForHighFieldByAnd<T>(List<HighSearchModel> hsmList) { var whereHelper = new WhereHelper<T>(); foreach (var field in hsmList) { switch (field.filterMode) { case Model.Enums.SearchFilterModeEnum.不等于: whereHelper.NotEqual(field.fieldName, field.fieldValue); break; case Model.Enums.SearchFilterModeEnum.大于: whereHelper.GreaterThan(field.fieldName, field.fieldValue); break; case Model.Enums.SearchFilterModeEnum.大于等于: whereHelper.GreaterThanOrEqual(field.fieldName, field.fieldValue); break; case Model.Enums.SearchFilterModeEnum.小于: whereHelper.LessThan(field.fieldName, field.fieldValue); break; case Model.Enums.SearchFilterModeEnum.小于等于: whereHelper.LessThanOrEqual(field.fieldName, field.fieldValue); break; case Model.Enums.SearchFilterModeEnum.模糊查询: whereHelper.Contains(field.fieldName, field.fieldValue); break; case Model.Enums.SearchFilterModeEnum.精准查询: whereHelper.Equal(field.fieldName, field.fieldValue); break; } } Expression<Func<T, bool>> finalExpression = Expression.Lambda<Func<T, bool>>(whereHelper.filter, new ParameterExpression[] { whereHelper.param }); return finalExpression; } public static Expression<Func<T, bool>> GetConditionExpressionForHighFieldByAnd<T>(List<HighSearchForDateTimeRangeModel> hsmForDatetimeList) { var whereHelper = new WhereHelper<T>(); foreach (var field in hsmForDatetimeList) { whereHelper.GreaterThanOrEqual(field.fieldName, field.start_fieldValue); whereHelper.LessThanOrEqual(field.fieldName, field.end_fieldValue); } Expression<Func<T, bool>> finalExpression = Expression.Lambda<Func<T, bool>>(whereHelper.filter, new ParameterExpression[] { whereHelper.param }); return finalExpression; } }
public class WhereHelper<T> //where T : class { public ParameterExpression param; public BinaryExpression filter; private MemberExpression common_left; private UnaryExpression common_right; public WhereHelper() { param = Expression.Parameter(typeof(T), "c");//构建 c=> 结构 //1==1 Expression left = Expression.Constant(1); filter = Expression.Equal(left, left);//构建出 c=> 1=1 } public Expression<Func<T, bool>> GetExpression() { return Expression.Lambda<Func<T, bool>>(filter, param); } private void CommonLeftRight(string propertyName, object value) { common_left = Expression.Property(param, typeof(T).GetProperty(propertyName));//构建构建c.{propertyName}的结构 var member = Expression.Property(param, propertyName); var propertyType = ((PropertyInfo)member.Member).PropertyType; var converter = TypeDescriptor.GetConverter(propertyType); // 1 if (!converter.CanConvertFrom(typeof(string))) // 2 throw new NotSupportedException(); var propertyValue = converter.ConvertFromInvariantString(value.ToString()); // 3 var constant = Expression.Constant(propertyValue); common_right = Expression.Convert(constant, propertyType); // 4 // common_right = Expression.Constant(value, value.GetType());//构建一个常量,值是 value } /// <summary> /// 等于 = /// </summary> /// <param name="propertyName"></param> /// <param name="value"></param> public void Equal(string propertyName, object value) { this.CommonLeftRight(propertyName, value); Expression result = Expression.Equal(common_left, common_right); filter = Expression.And(filter, result); } /// <summary> /// 不等于 <> /// </summary> /// <param name="propertyName"></param> /// <param name="value"></param> public void NotEqual(string propertyName, object value) { this.CommonLeftRight(propertyName, value); Expression result = Expression.NotEqual(common_left, common_right); filter = Expression.And(filter, result); } /// <summary> /// 大于 > /// </summary> /// <param name="propertyName"></param> /// <param name="value"></param> public void GreaterThan(string propertyName, object value) { this.CommonLeftRight(propertyName, value); Expression result = Expression.GreaterThan(common_left, common_right); filter = Expression.And(filter, result); } /// <summary> /// 大于等于 >= /// </summary> /// <param name="propertyName"></param> /// <param name="value"></param> public void GreaterThanOrEqual(string propertyName, object value) { this.CommonLeftRight(propertyName, value); Expression result = Expression.GreaterThanOrEqual(common_left, common_right); filter = Expression.And(filter, result); } /// <summary> /// 小于 < /// </summary> /// <param name="propertyName"></param> /// <param name="value"></param> public void LessThan(string propertyName, object value) { this.CommonLeftRight(propertyName, value); Expression result = Expression.LessThan(common_left, common_right); filter = Expression.And(filter, result); } /// <summary> /// 小于等于 <= /// </summary> /// <param name="propertyName"></param> /// <param name="value"></param> public void LessThanOrEqual(string propertyName, object value) { this.CommonLeftRight(propertyName, value); Expression result = Expression.LessThanOrEqual(common_left, common_right); filter = Expression.And(filter, result); } /// <summary> /// 包含 LIKE /// </summary> /// <param name="propertyName"></param> /// <param name="value"></param> public void Contains(string propertyName, string value) { Expression left = Expression.Property(param, typeof(T).GetProperty(propertyName)); Expression right = Expression.Constant(value, value.GetType()); Expression result = Expression.Call(left, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), right); filter = Expression.And(filter, result); } }
静态常量：
/// <summary> /// 系统通用的常量 /// </summary> public static class SystemCommonVar { /// <summary> /// 通用的查询字符串 /// </summary> public const string searchVal = "searchVal"; /// <summary> /// 高级查询的后缀名 /// </summary> public const string highSearchModeSuffix = "_FilterMode"; /// <summary> /// 通用的查询字符串的配置模式 /// </summary> public const string searchVal_FilterMode = searchVal + highSearchModeSuffix; /// <summary> /// 子搜索组件传递的属性名 /// </summary> public const string searchFormInputAttrs = "searchFormInputAttrs"; /// <summary> /// 通用的查询参数类的父类中的字段 /// </summary> public const string commnParamFatherPros = "page|pageSize|" + searchVal + "|" + searchVal_FilterMode + "|" + searchFormInputAttrs; }
类帮助：
/// <summary> /// 类帮助 /// </summary> public class ClassHelper { /// <summary> /// 获取属性对象列表 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="proName"></param> /// <param name="errMsg"></param> /// <returns></returns> public static PropertyInfo[] GetPropertyInfoList<T>(T t) { var pros = typeof(T).GetProperties(); return pros; } /// <summary> /// 获取属性对象 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="proName"></param> /// <param name="errMsg"></param> /// <returns></returns> public static PropertyInfo GetPropertyInfo<T>(T t, string proName, out string errMsg) { errMsg = ""; var pro = typeof(T).GetProperty(proName); if (pro == null) { errMsg = "属性名'" + proName + "'不存在类'" + typeof(T).Name + "'中"; return null; } return pro; } /// <summary> /// 获取属性的值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="proName"></param> /// <param name="errMsg"></param> /// <returns></returns> public static string GetPropertyValue<T>(T t, string proName, out string errMsg) { var pro = GetPropertyInfo<T>(t, proName, out errMsg); if (!string.IsNullOrEmpty(errMsg)) { return string.Empty; } var pro_value = pro.GetValue(t, null); var str = pro_value == null ? string.Empty : Convert.ToString(pro_value); return str; } /// <summary> /// 通过属性对象获取属性的值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="pro"></param> /// <returns></returns> public static string GetPropertyValueByObject<T>(T t, PropertyInfo pro) { var pro_value = pro.GetValue(t, null); var str = pro_value == null ? string.Empty : Convert.ToString(pro_value); return str; } /// <summary> /// 通过属性对象获取属性的值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="pro"></param> /// <returns></returns> public static object GetPropertyValue<T>(T t, PropertyInfo pro) { var pro_value = pro.GetValue(t, null); return pro_value; } /// <summary> /// 通过特性名获取特性 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="pro"></param> /// <returns></returns> public static object[] GetDIYAttributesByPro(PropertyInfo pro) { object[] Attributes = pro.GetCustomAttributes(typeof(HighSearchRangeAttribute), false); return Attributes; } /// <summary> /// 获取属性的值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="proName"></param> /// <param name="errMsg"></param> /// <returns></returns> public static List<string> GetPropertyValueForList<T>(T t, string proName, out string errMsg) { var pro = GetPropertyInfo<T>(t, proName, out errMsg); if (!string.IsNullOrEmpty(errMsg)) { return null; } var pro_value = pro.GetValue(t, null); var list = pro_value == null ? null : (List<string>)pro_value; return list; } }
4、使用方法
public class RoleParam : PageParam { /// <summary> /// 角色名 /// </summary> public string Name { get; set; } /// <summary> ///描述 /// </summary> public string Description { get; set; } #region 单独处理日期 //表示是 高级查询范围查询特性 [HighSearchRangeAttribute] public List<string> CreateTime { get; set; } #endregion #region 查询过滤模式 /// <summary> /// 角色名-查询过滤模式 /// </summary> public string Name_FilterMode { get; set; } /// <summary> ///描述-查询过滤模式 /// </summary> public string Description_FilterMode { get; set; } #endregion }
public class PageParam { /// <summary> /// 第几页 /// </summary> public int page { get; set; } /// <summary> /// 每页显示多少条 /// </summary> public int pageSize { get; set; } /// <summary> /// 关键字模糊查询 /// </summary> public string searchVal { get; set; } /// <summary> /// 通用查询配置 (1:模糊查询 , 2:精准查询) /// </summary> public int searchVal_FilterMode { get; set; } /// <summary> /// 子搜索组件传递的通用查询配置的属性名 /// </summary> public List<string> searchFormInputAttrs { get; set; } }
/// <summary> /// 方法返回实体类 /// </summary> public class FunReturnResultModel<T> { /// <summary> /// 执行是否成功 /// </summary> public bool IsSuccess { get; set; } /// <summary> /// 错误信息 /// </summary> public string ErrMsg { get; set; } /// <summary> /// 返回数据 /// </summary> public T data { get; set; } }
使用：
var whereConditions=WhereConditionsExtensions.GetWhereConditions<Role, RoleParam>(param); if (!whereConditions.IsSuccess) { return new MessageModel<PageModel<Role>>() { msg = whereConditions.ErrMsg, success = false, response = null }; }
whereConditions.data 就是生成的动态查询树。
前端请求参数示例：
人一定要靠自己
