查询条件动态翻译成Expression表达式
相信大家在使用ORM框架的使用，一定会遇到过一个问题：前端的查询条件如何翻译成Linq呢？或如何翻译成Expression呢？前端指定的排序字段又如何来翻译成Expression呢？
今天就来帮大家解决这个问题，先来个使用案例，让大家看看效果：
1 public List<LinqOption> WhereOptions { get; set; } 2 public string OrderBy { get; set; } 3 /// <summary> 4 /// 查找条件 5 /// </summary> 6 public virtual Expression<Func<T, bool>> GetWhereExpression() 7 { 8 if (WhereOptions?.Count > 0) 9 { 10 if (And_Or == "And" || And_Or == "and") 11 return LinqHelper.CreateExpression<T>(WhereOptions); 12 13 else 14 return LinqHelper.CreateExpression<T>(WhereOptions, ExpressionType.OrElse); 15 } 16 else 17 return t => 1 == 1; 18 } 19 /// <summary> 20 /// 排序字段 21 /// </summary> 22 /// <returns></returns> 23 public virtual Expression<Func<T, object>> GetOrderByExpression() 24 { 25 if (string.IsNullOrEmpty(OrderBy)) 26 return null; 27 return LinqHelper.CreateExpression<T>(OrderBy); 28 }
其中的LinqOption.cs如下：
1 public class LinqOption 2 { 3 /// <summary> 4 /// 不等于：!= 大于：> 大于等于：>= 小于：< 小于等于：<= 等于：= 模糊匹配：like 包含：in 5 /// </summary> 6 public string Type { get; set; } 7 public string Key { get; set; } 8 public string Value { get; set; } 9 }
如何调用呢，比如前端给的参数是：
{
"WhereOptions":
{
["Type":">","Key":"p","Value":"2"],
["Type":"<","Key":"p","Value":"10"]
}
"OrderBy":"y"
}
后端如何根据这个进行查询呢：
1 public class test{ 2 public int p{get;set;} 3 public int y{get;set;} 4 } 5 6 var _tt = await _freeSql.Select<test>() 7 .Where(pageReq.GetWhereExpression()) //相当于：t => t.p > 2 && t.p < 10 8 .OrderBy(pageReq.GetOrderByExpression()) //相当于： t => t.y 9 .ToListAsync();
下面直接上实现源码：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Linq.Expressions; 5 using System.Reflection; 6 7 namespace Syspetro.Core.Extensions 8 { 9 public class LinqOption 10 { 11 /// <summary> 12 /// 不等于：!= 大于：> 大于等于：>= 小于：< 小于等于：<= 等于：= 模糊匹配：like 包含：in 13 /// </summary> 14 public string Type { get; set; } 15 public string Key { get; set; } 16 public string Value { get; set; } 17 } 18 public static class LinqHelper 19 { 20 /// <summary> 21 /// 获取指定字段类型 22 /// </summary> 23 /// <param name="t"></param> 24 /// <param name="propertyName"></param> 25 /// <returns></returns> 26 public static Type GetParameter(Type t, string propertyName) 27 { 28 #region 表达式目录树方式 29 if (string.IsNullOrEmpty(propertyName)) 30 return t; 31 Expression propertySelector = Expression.Parameter(t, "p");//创建参数p 32 var listsp = propertyName.Split('.'); 33 foreach (var sp in listsp) 34 { 35 propertySelector = Expression.Property(propertySelector, sp); 36 t = propertySelector.Type; 37 } 38 return t; 39 #endregion 40 41 #region 反射方式 42 //if (propertyName.Contains('.')) 43 //{ 44 // int index = propertyName.IndexOf('.'); 45 // string sp1 = propertyName.Substring(0, index); 46 // string sp2 = propertyName.Substring(index + 1, propertyName.Length - index - 1); 47 // var ps = t.GetProperties(); 48 // foreach (var p in ps) 49 // { 50 // if (string.Compare(p.Name, sp1, true) == 0) 51 // { 52 // if (string.IsNullOrEmpty(sp2)) 53 // return p.PropertyType; 54 // else 55 // return GetParameter(p.PropertyType, sp2); 56 // } 57 // } 58 //} 59 //else 60 //{ 61 // var ps = t.GetProperties(); 62 // foreach (var p in ps) 63 // { 64 // if (string.Compare(p.Name, propertyName, true) == 0) 65 // { 66 // return p.PropertyType; 67 // } 68 // } 69 //} 70 //return t; 71 #endregion 72 } 73 /// <summary> 74 /// 字符串转换成Linq 75 /// </summary> 76 /// <typeparam name="T"></typeparam> 77 /// <param name="key"></param> 78 /// <param name="type"></param> 79 /// <param name="value"></param> 80 /// <returns></returns> 81 public static Expression<Func<T, bool>> CreateExpression<T>(List<LinqOption> _options, ExpressionType expression = ExpressionType.AndAlso) where T : new() 82 { 83 if (_options == null) 84 { 85 return null; 86 } 87 var options = _options.Where(t => !string.IsNullOrEmpty(t.Value.Trim())).ToList(); 88 if (options == null || options.Count == 0) 89 { 90 return null; 91 } 92 ParameterExpression newParameter = Expression.Parameter(typeof(T), "c"); 93 var ex = CreateExpression<T>(options[0].Key, options[0].Type, options[0].Value, newParameter); 94 for (int i = 1; i < options.Count; i++) 95 { 96 var ex2 = CreateExpression<T>(options[i].Key, options[i].Type, options[i].Value, newParameter); 97 if (expression == ExpressionType.OrElse) 98 ex = OrElse<T>(ex, ex2, newParameter); 99 else if (expression == ExpressionType.AndAlso) 100 ex = AndAlso<T>(ex, ex2, newParameter); 101 else 102 throw new Exception("不支持该方法"); 103 } 104 return Expression.Lambda<Func<T, bool>>(ex, newParameter); 105 } 106 /// <summary> 107 /// 创建lambda表达式：p=>p.propertyName 108 /// </summary> 109 /// <typeparam name="T"></typeparam> 110 /// <param name="propertyName"></param> 111 /// <returns></returns> 112 public static Expression<Func<T, object>> CreateExpression<T>(string propertyName) 113 { 114 var parameter = Expression.Parameter(typeof(T), "p");//创建参数p 115 Expression propertySelector = parameter; 116 var listsp = propertyName.Split('.'); 117 foreach (var sp in listsp) 118 { 119 propertySelector = Expression.Property(propertySelector, sp); 120 } 121 propertySelector = Expression.Convert(propertySelector, typeof(object)); 122 return Expression.Lambda<Func<T, object>>(propertySelector, parameter); 123 } 124 /// <summary> 125 /// 创建lambda表达式：p=>p.propertyName 126 /// </summary> 127 /// <typeparam name="T"></typeparam> 128 /// <param name="propertyName"></param> 129 /// <returns></returns> 130 public static Expression<Func<T, TType>> CreateExpression<T, TType>(string propertyName) 131 { 132 var parameter = Expression.Parameter(typeof(T), "p");//创建参数p 133 Expression propertySelector = parameter; 134 var listsp = propertyName.Split('.'); 135 136 Type pt = typeof(TType); 137 foreach (var sp in listsp) 138 { 139 propertySelector = Expression.Property(propertySelector, sp); 140 pt = propertySelector.Type; 141 } 142 if (pt == typeof(TType)) 143 return Expression.Lambda<Func<T, TType>>(propertySelector, parameter); 144 else 145 throw new Exception("类型错误!"); 146 } 147 /// <summary> 148 /// 创建lambda表达式：p=>p.propertyName == propertyValue 149 /// </summary> 150 /// <typeparam name="T"></typeparam> 151 /// <param name="column"></param> 152 /// <param name="value"></param> 153 /// <returns></returns> 154 public static Expression<Func<T, bool>> CreateExpression<T>(string propertyName, string type, string propertyValue) 155 { 156 ParameterExpression parameter = Expression.Parameter(typeof(T), "p");//创建参数p 157 return Expression.Lambda<Func<T, bool>>(ToLinq<T>(propertyName, type, propertyValue, parameter), parameter); 158 } 159 160 private static Expression CreateExpression<T>(string propertyName, string type, string propertyValue, ParameterExpression parameter = null) 161 { 162 if (type == "in") 163 { 164 return ParaseIn<T>(parameter, propertyName.Trim(), propertyValue.Trim()); 165 } 166 else 167 { 168 return ToLinq<T>(propertyName.Trim(), type, propertyValue.Trim(), parameter); 169 } 170 } 171 private static Expression ToLinq(string type, MemberExpression pe, ConstantExpression value) 172 { 173 return type switch 174 { 175 "!=" => Expression.NotEqual(pe, value), 176 ">" => Expression.GreaterThan(pe, value), 177 ">=" => Expression.GreaterThanOrEqual(pe, value), 178 "<" => Expression.LessThan(pe, value), 179 "<=" => Expression.LessThanOrEqual(pe, value), 180 "=" => Expression.Equal(pe, value), 181 "like" => ToLinqLike(pe, value), 182 _ => throw new Exception("不支持该方法"), 183 }; 184 } 185 private static Expression ParaseIn<T>(ParameterExpression parameter, string propertyName, string propertyValue, bool isEqual = true) 186 { 187 var valueArr = propertyValue.Split(','); 188 Expression expression1; 189 190 if (parameter == null) 191 parameter = Expression.Parameter(typeof(T), "p");//创建参数p 192 var keyMember = (MemberExpression)GetPropertySelector(parameter, propertyName); 193 Type keyType = keyMember.Type; 194 ConstantExpression constant = Expression.Constant(TypeHelper.ChangeTo(keyType, valueArr[0]), keyType);//创建常数 195 if (isEqual) 196 expression1 = ToLinq("=", keyMember, constant); 197 else 198 expression1 = ToLinq("like", keyMember, constant); 199 200 for (int i = 1; i < valueArr.Length; i++) 201 { 202 keyType = keyMember.Type; 203 Expression expression2; 204 constant = Expression.Constant(TypeHelper.ChangeTo(keyType, valueArr[i]), keyType);//创建常数 205 if (isEqual) 206 expression2 = ToLinq("=", keyMember, constant); 207 else 208 expression2 = ToLinq("like", keyMember, constant); 209 expression1 = OrElse<T>(expression1, expression2, parameter); 210 } 211 return expression1; 212 } 213 private static Expression ToLinqLike(MemberExpression pe, ConstantExpression constant) 214 { 215 MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) }); 216 return Expression.Call(pe, method, constant); 217 } 218 private static Expression ToLinq<T>(string propertyName, string type, string propertyValue, ParameterExpression parameter = null) 219 { 220 if (parameter == null) 221 parameter = Expression.Parameter(typeof(T), "p");//创建参数p 222 var member = GetPropertySelector(parameter, propertyName); 223 var tt = member.Type; 224 ConstantExpression constant = Expression.Constant(TypeHelper.ChangeTo(tt, propertyValue), tt);//创建常数 225 return ToLinq(type, (MemberExpression)member, constant); 226 } 227 private static Expression GetPropertySelector(ParameterExpression parameter, string propertyName) 228 { 229 Expression propertySelector = parameter; 230 var listsp = propertyName.Split('.'); 231 foreach (var sp in listsp) 232 { 233 propertySelector = Expression.Property(propertySelector, sp); 234 } 235 return propertySelector; 236 } 237 /// <summary> 238 /// 合并表达式 expr1 AND expr2 239 /// </summary> 240 /// <typeparam name="T"></typeparam> 241 /// <param name="expr1"></param> 242 /// <param name="expr2"></param> 243 /// <returns></returns> 244 private static Expression AndAlso<T>(Expression expr1, Expression expr2, ParameterExpression newParameter) 245 { 246 if (expr1 == null) 247 return expr2; 248 else if (expr2 == null) 249 return expr1; 250 MyExpressionVisitor visitor = new MyExpressionVisitor(newParameter); 251 var left = visitor.Visit(expr1); 252 var right = visitor.Visit(expr2); 253 return Expression.AndAlso(left, right); 254 } 255 /// <summary> 256 /// 合并表达式 expr1 or expr2 257 /// </summary> 258 /// <typeparam name="T"></typeparam> 259 /// <param name="expr1"></param> 260 /// <param name="expr2"></param> 261 /// <returns></returns> 262 private static Expression OrElse<T>(Expression expr1, Expression expr2, ParameterExpression newParameter) 263 { 264 if (expr1 == null) 265 return expr2; 266 else if (expr2 == null) 267 return expr1; 268 MyExpressionVisitor visitor = new MyExpressionVisitor(newParameter); 269 270 var left = visitor.Visit(expr1); 271 var right = visitor.Visit(expr2); 272 return Expression.OrElse(left, right); 273 } 274 } 275 public class MyExpressionVisitor : ExpressionVisitor 276 { 277 public ParameterExpression Parameter { get; set; } 278 public MyExpressionVisitor() { } 279 public MyExpressionVisitor(ParameterExpression parameter) 280 { 281 this.Parameter = parameter; 282 } 283 public override Expression Visit(Expression node) 284 { 285 return base.Visit(node); 286 } 287 288 protected override Expression VisitParameter(ParameterExpression parameter) 289 { 290 return this.Parameter; 291 } 292 } 293 }
