自己动手写ORM(01):解析表达式树生成Sql碎片
什么是ORM框架:
ORM即对象关系映射(Object Relational Mapping,简称ORM),是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术。简单的说,ORM是通过使用描述对象和数据库之间映射的元数据,将程序中的对象自动持久化到关系数据库中。
自己也用过很多ORM框架,比如微软的EF,Hibernate。轻量级一点的有Depper等等。这些框架说白了就是让我们能像操作对象那样去操作数据库。
鄙人是个比较懒的程序猿,总想这写更少的代码做更多的事情,最近不是很忙,于是我就试着写写自己的ORM框架。在此之前我用反射写过ORM框架,总觉得性能比较低,在将查询结果转化成实体对象的时候,我没用反射,这样让实体类变得有点重,这次的目标是把实体类变得简单,并且不用反射。那么废话不多说,直接进入主题。
解析Expression Tree 转生成Sql碎片:
至于设么是表达式树,在此我就不做说明,园子里有很多文章,大家都可以去看看。
在EF中,我们查询数据时可能会用拉姆达表达式 Where(Func<T,ture> func)这个方法来筛选数据,例如,我们定义一个User实体类
1 public class User
2 {
3 public Guid Id { get; set; }
4 public string LoginId { get; set; }
5 public string Name { get; set; }
6 public bool Enabled { get; set; }
7 public DateTime CreateTime { get; set; }
8 }
现在我想查询用户Enabled为true 并且Name 中包含'lilei'的用户,在EF中我们可能会这样去写:
XXX.Where(u => u.Name.Contains("lilei") && u.Enabled==true)
那么现在我想将Where方法中的那个泛型委托翻译成Sql语句中的一部分应该怎么做呢?下面是我已经写好的一个解析表达式的类,可能写得不是很规范,大家尽量吐槽:
1 using LC.Factory.Code; 2 using LC.Factory.Entity; 3 using LC.Factory.Resource; 4 using System; 5 using System.Collections.Generic; 6 using System.Linq; 7 using System.Linq.Expressions; 8 using System.Reflection; 9 using System.Text; 10 using System.Text.RegularExpressions; 11 12 13 namespace LC.Factory.Common 14 { 15 public class ExpressionAnalyzer 16 { 17 /// <summary> 18 /// 表达式所有参数集合 19 /// </summary> 20 private Dictionary<string, object> _params; 21 /// <summary> 22 /// 命名参数别名 23 /// </summary> 24 private const string _argName = "TAB"; 25 /// <summary> 26 /// 解析结果 27 /// </summary> 28 public AnalysisData ResultData { get; set; } 29 public ExpressionAnalyzer() 30 { 31 ResultData = new AnalysisData(); 32 _params = new Dictionary<string, object>(); 33 34 } 35 public ExpressionAnalyzer(LambdaExpression exp) 36 : this() 37 { 38 if (exp != null) 39 { 40 AppendParams(GetChildValue(exp.Body), _params); 41 foreach (var item in exp.Parameters) 42 { 43 AnalysisTables(item); 44 } 45 AnalysisExpression(exp.Body, true); 46 } 47 } 48 /// <summary> 49 /// 解析表达式 50 /// </summary> 51 /// <param name="exp"></param> 52 /// <param name="isLeftChild"></param> 53 private void AnalysisExpression(Expression exp, bool isLeftChild = true) 54 { 55 switch (exp.NodeType) 56 { 57 case ExpressionType.AndAlso: 58 ResultData.StackList.Add("("); 59 AnalysisExpression(GetChildExpression(exp)); 60 ResultData.StackList.Add(")"); 61 ResultData.StackList.Add("AND"); 62 ResultData.StackList.Add("("); 63 AnalysisExpression(GetChildExpression(exp, false), false); 64 ResultData.StackList.Add(")"); 65 break; 66 case ExpressionType.OrElse: 67 ResultData.StackList.Add("("); 68 AnalysisExpression(GetChildExpression(exp)); 69 ResultData.StackList.Add(")"); 70 ResultData.StackList.Add("OR"); 71 ResultData.StackList.Add("("); 72 AnalysisExpression(GetChildExpression(exp, false), false); 73 ResultData.StackList.Add(")"); 74 break; 75 case ExpressionType.Equal: 76 AnalysisExpression(GetChildExpression(exp)); 77 ResultData.StackList.Add("="); 78 AnalysisExpression(GetChildExpression(exp, false), false); 79 break; 80 case ExpressionType.NotEqual: 81 AnalysisExpression(GetChildExpression(exp)); 82 ResultData.StackList.Add("!="); 83 AnalysisExpression(GetChildExpression(exp, false), false); 84 break; 85 case ExpressionType.GreaterThanOrEqual: 86 AnalysisExpression(GetChildExpression(exp)); 87 ResultData.StackList.Add(">="); 88 AnalysisExpression(GetChildExpression(exp, false), false); 89 break; 90 case ExpressionType.GreaterThan: 91 AnalysisExpression(GetChildExpression(exp)); 92 ResultData.StackList.Add(">"); 93 AnalysisExpression(GetChildExpression(exp, false), false); 94 break; 95 case ExpressionType.LessThan: 96 AnalysisExpression(GetChildExpression(exp)); 97 ResultData.StackList.Add("<"); 98 AnalysisExpression(GetChildExpression(exp, false), false); 99 break; 100 case ExpressionType.LessThanOrEqual: 101 AnalysisExpression(GetChildExpression(exp)); 102 ResultData.StackList.Add("<="); 103 AnalysisExpression(GetChildExpression(exp, false), false); 104 break; 105 case ExpressionType.Call: 106 var imExp = exp as MethodCallExpression; 107 AnalysisExpression(imExp.Object, true); 108 ResultData.StackList.Add("LIKE"); 109 if (imExp.Arguments.Count > 0) 110 { 111 var arg0 = imExp.Arguments[0] as MemberExpression; 112 ResultData.StackList.Add("'%'+"); 113 AnalysisExpression(imExp.Arguments[0], false); 114 ResultData.StackList.Add("+'%'"); 115 } 116 break; 117 case ExpressionType.MemberAccess: 118 if (isLeftChild) 119 { 120 AnalysisTables(exp); 121 var mberExp = exp as MemberExpression; 122 var parentName = GetExpressionName(mberExp.Expression); 123 if (!string.IsNullOrEmpty(parentName)) 124 { 125 ResultData.StackList.Add(string.Format("[{0}].{1}", parentName, GetExpressionName(exp))); 126 break; 127 } 128 ResultData.StackList.Add(GetExpressionName(exp)); 129 } 130 else 131 { 132 var paramName = GetParamName(exp); 133 ResultData.ParamList.Add(paramName, _params[paramName]); 134 ResultData.StackList.Add(paramName); 135 } 136 break; 137 case ExpressionType.Constant: 138 var constent = exp as ConstantExpression; 139 if (constent.Value == null) 140 { 141 var op = ResultData.StackList.ElementAt(ResultData.StackList.Count - 1); 142 ResultData.StackList.RemoveAt(ResultData.StackList.Count - 1); 143 if (string.Equals(op, "=")) 144 { 145 ResultData.StackList.Add("IS NULL"); 146 } 147 else 148 { 149 ResultData.StackList.Add("IS NOT NULL"); 150 } 151 break; 152 } 153 if (constent.Value.GetType() == typeof(String)) 154 { 155 ResultData.StackList.Add(string.Format("'{0}'", constent.Value)); 156 break; 157 } 158 if (constent.Value.GetType() == typeof(bool)) 159 { 160 if (ResultData.StackList.Count > 0) 161 { 162 var value = Convert.ToBoolean(constent.Value); 163 ResultData.StackList.Add(string.Format("{0}", value ? "1" : "0")); 164 } 165 166 break; 167 } 168 ResultData.StackList.Add(string.Format("{0}", constent.Value)); 169 break; 170 case ExpressionType.Convert: 171 var uExp = exp as UnaryExpression; 172 AnalysisExpression(uExp.Operand, isLeftChild); 173 break; 174 case ExpressionType.New: 175 var newExp = exp as NewExpression; 176 //解析查询字段 177 for (int i = 0; i < newExp.Arguments.Count; i++) 178 { 179 AnalysisExpression(newExp.Arguments[i]); 180 ResultData.StackList.Add("AS"); 181 ResultData.StackList.Add(string.Format("'{0}'", newExp.Members[i].Name)); 182 } 183 break; 184 case ExpressionType.Parameter: 185 throw new BusinessException(BusinessRes.SelectObjectMastBeAnNewObject); 186 //AnalysisExpression(Expression.New(exp.Type)); 187 //break; 188 default: 189 break; 190 } 191 192 } 193 /// <summary> 194 /// 获取孩子节点 195 /// </summary> 196 /// <param name="exp"></param> 197 /// <param name="getLeft"></param> 198 /// <returns></returns> 199 private Expression GetChildExpression(Expression exp, bool getLeft = true) 200 { 201 var className = exp.GetType().Name; 202 switch (className) 203 { 204 case "BinaryExpression": 205 case "LogicalBinaryExpression": 206 var bExp = exp as BinaryExpression; 207 return getLeft ? bExp.Left : bExp.Right; 208 case "PropertyExpression": 209 case "FieldExpression": 210 var mberExp = exp as MemberExpression; 211 return mberExp; 212 case "MethodBinaryExpression": 213 var mbExp = exp as BinaryExpression; 214 return getLeft ? mbExp.Left : mbExp.Right; 215 case "UnaryExpression": 216 var unaryExp = exp as UnaryExpression; 217 return unaryExp; 218 case "ConstantExpression": 219 var cExp = exp as ConstantExpression; 220 return cExp; 221 case "InstanceMethodCallExpressionN": 222 var imExp = exp as MethodCallExpression; 223 return imExp; 224 default: 225 return null; 226 } 227 } 228 /// <summary> 229 /// 获取变量名 230 /// </summary> 231 /// <param name="exp"></param> 232 /// <param name="isLeftChild"></param> 233 /// <returns></returns> 234 private string GetExpressionName(Expression exp) 235 { 236 var className = exp.GetType().Name; 237 switch (className) 238 { 239 case "PropertyExpression": 240 case "FieldExpression": 241 var mberExp = exp as MemberExpression; 242 return string.Format("{0}", mberExp.Member.Name); 243 case "TypedParameterExpression": 244 return _argName; 245 default: 246 return string.Empty; 247 } 248 } 249 /// <summary> 250 /// 获取参数名 251 /// </summary> 252 /// <param name="exp"></param> 253 /// <param name="isLeftChild"></param> 254 /// <returns></returns> 255 private string GetParamName(Expression exp) 256 { 257 var className = exp.GetType().Name; 258 switch (className) 259 { 260 case "PropertyExpression": 261 case "FieldExpression": 262 var mberExp = exp as MemberExpression; 263 return string.Format("@{0}", mberExp.Member.Name); 264 case "TypedParameterExpression": 265 var texp = exp as ParameterExpression; 266 return string.Format("@{0}", texp.Name); 267 default: 268 return string.Empty; 269 } 270 } 271 /// <summary> 272 /// 解析表信息 273 /// </summary> 274 /// <param name="exp"></param> 275 private void AnalysisTables(Expression exp) 276 { 277 var className = exp.GetType().Name; 278 switch (className) 279 { 280 case "PropertyExpression": 281 case "FieldExpression": 282 var mberExp = exp as MemberExpression; 283 if (!IsDefaultType(mberExp.Type)) 284 { 285 if (!ResultData.TableList.ContainsKey(mberExp.Member.Name)) 286 { 287 ResultData.TableList.Add(mberExp.Member.Name, new AnalysisTable() 288 { 289 Name = mberExp.Type.Name, 290 TableType = mberExp.Type, 291 IsMainTable = false 292 }); 293 } 294 } 295 AnalysisTables(mberExp.Expression); 296 break; 297 case "TypedParameterExpression": 298 //命名参数表达式 299 var texp = exp as ParameterExpression; 300 if (!IsDefaultType(texp.Type)) 301 { 302 if (!ResultData.TableList.ContainsKey(_argName)) 303 { 304 ResultData.TableList.Add(_argName, new AnalysisTable() 305 { 306 Name = texp.Type.Name, 307 TableType = texp.Type, 308 IsMainTable = true 309 }); 310 } 311 } 312 break; 313 default: 314 break; 315 } 316 } 317 /// <summary> 318 /// 解析获取表达式的值 319 /// </summary> 320 /// <param name="exp"></param> 321 /// <param name="leftChild"></param> 322 /// <returns></returns> 323 private object GetChildValue(Expression exp) 324 { 325 var className = exp.GetType().Name; 326 switch (className) 327 { 328 case "BinaryExpression": 329 case "LogicalBinaryExpression": 330 var lExp = exp as BinaryExpression; 331 var ret = GetChildValue(lExp.Left); 332 if (IsNullDefaultType(ret)) 333 { 334 ret = GetChildValue(lExp.Right); 335 } 336 return ret; 337 case "MethodBinaryExpression": 338 var mbExp = exp as BinaryExpression; 339 var ret1 = GetChildValue(mbExp.Left); 340 if (IsNullDefaultType(ret1)) 341 { 342 ret1 = GetChildValue(mbExp.Right); 343 } 344 return ret1; 345 346 case "PropertyExpression": 347 case "FieldExpression": 348 var mberExp = exp as MemberExpression; 349 return GetChildValue(mberExp.Expression); 350 case "ConstantExpression": 351 var cExp = exp as ConstantExpression; 352 return cExp.Value; 353 case "UnaryExpression": 354 var unaryExp = exp as UnaryExpression; 355 return GetChildValue(unaryExp.Operand); 356 case "InstanceMethodCallExpressionN": 357 var imExp = exp as MethodCallExpression; 358 if (imExp.Arguments.Count > 0) 359 { 360 return GetChildValue(imExp.Arguments[0]); 361 } 362 return null; 363 default: 364 return null; 365 } 366 367 } 368 /// <summary> 369 /// 初始化所有参数 370 /// </summary> 371 /// <param name="paramObj"></param> 372 private void AppendParams(object paramObj, Dictionary<string, object> _params) 373 { 374 if (IsNullDefaultType(paramObj)) 375 { 376 return; 377 } 378 if (_params == null) 379 { 380 _params = new Dictionary<string, object>(); 381 } 382 foreach (var item in paramObj.GetType().GetProperties()) 383 { 384 if (IsDefaultType(item.PropertyType)) 385 { 386 var value = item.GetValue(paramObj, null); 387 if (value != null) 388 { 389 _params.Add(string.Format("@{0}", item.Name), value); 390 } 391 continue; 392 } 393 394 AppendParams(item.GetValue(paramObj), _params); 395 } 396 397 foreach (var item in paramObj.GetType().GetFields()) 398 { 399 if (IsDefaultType(item.FieldType)) 400 { 401 var value = item.GetValue(paramObj); 402 if (value != null) 403 { 404 _params.Add(string.Format("@{0}", item.Name), value); 405 } 406 continue; 407 } 408 AppendParams(item.GetValue(paramObj), _params); 409 } 410 } 411 public Dictionary<string, object> GetParams(object paramObj) 412 { 413 Dictionary<string, object> dicParams = new Dictionary<string, object>(); 414 AppendParams(paramObj, dicParams); 415 return dicParams; 416 } 417 /// <summary> 418 /// 判断是否是系统默认基本类型 419 /// </summary> 420 /// <param name="type"></param> 421 /// <returns></returns> 422 private bool IsNullDefaultType(object obj) 423 { 424 if (obj == null) 425 { 426 return true; 427 } 428 return IsDefaultType(obj.GetType()); 429 } 430 private bool IsDefaultType(Type type) 431 { 432 string defaultType = @"String|Boolean|Double|Int32|Int64|Int16|Single|DateTime|Decimal|Char|Object|Guid"; 433 434 Regex e = new Regex(defaultType, RegexOptions.IgnoreCase); 435 if (type.Name.ToLower().Contains("nullable")) 436 { 437 if (type.GenericTypeArguments.Count() > 0) 438 { 439 return e.IsMatch(type.GenericTypeArguments[0].Name); 440 } 441 } 442 return e.IsMatch(type.Name); 443 } 444 } 445 }
在这个类中主要的方法是AnalysisExpression(Expression exp, bool isLeftChild = true),次啊面我们来测试一下上面的那个表达式:
1 [TestMethod]
2 public void TestExpression()
3 {
4
5 Expression<Func<User, bool>> exp = u => u.Name.Contains("lilei") && u.Enabled == true;
6 var result = new ExpressionAnalyzer(exp).ResultData;
7
8 if (result.TableList.Count > 0)
9 {
10 foreach (var item in result.TableList)
11 {
12 Console.WriteLine("{0} AS {1}", item.Value.Name, item.Key);
13 }
14 }
15 Console.WriteLine("***************************************************");
16 if (result.StackList.Count > 0)
17 {
18 Console.WriteLine(string.Join(" ", result.StackList));
19 }
20 Console.WriteLine("***************************************************");
21 if (result.ParamList.Count > 0)
22 {
23 foreach (var item in result.ParamList)
24 {
25 Console.WriteLine("{0} {1}", item.Key, item.Value);
26 }
27 }
28 }
输出结果:
再测试带参数的查询:
1 [TestMethod]
2 public void TestExpression()
3 {
4 var keyWord = "lilei";
5 var enable = true;
6
7 Expression<Func<User, bool>> exp = u => u.Name.Contains(keyWord) && u.Enabled == enable;
8 var result = new ExpressionAnalyzer(exp).ResultData;
9
10 if (result.TableList.Count > 0)
11 {
12 foreach (var item in result.TableList)
13 {
14 Console.WriteLine("{0} AS {1}", item.Value.Name, item.Key);
15 }
16 }
17 Console.WriteLine("***************************************************");
18 if (result.StackList.Count > 0)
19 {
20 Console.WriteLine(string.Join(" ", result.StackList));
21 }
22 Console.WriteLine("***************************************************");
23 if (result.ParamList.Count > 0)
24 {
25 foreach (var item in result.ParamList)
26 {
27 Console.WriteLine("{0} {1}", item.Key, item.Value);
28 }
29 }
30 }
输出结果:
以上是根据表达式树解析出来的Sql碎片,这为后续生成Sql语句奠定了基础。
注:看博客园看了几年了,第一次写文章,写的不是很好,请大家多多包涵,后面我还会继续把自己的编写的ORM框架分享出来,共同学习共同进步。