自己动手写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 }
View Code

在这个类中主要的方法是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框架分享出来,共同学习共同进步。

 

posted @ 2016-03-26 22:37  Metto  阅读(3217)  评论(7编辑  收藏  举报