你的位置:首页 > ASP.net教程

[ASP.net教程]自己动手写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           else131           {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             else148             {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 = false292               });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 = true309               });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框架分享出来,共同学习共同进步。