Lambda转sql部分代码保存

  1     public class SqlExpressionTree
  2     {
  3         public string GetQuerySql<T>(Expression<Func<T, bool>> condi)
  4         {
  5             string condition = "select * from  "+typeof(T).Name+" "+ typeof(T).Name + " where ";
  6             BinaryExpression body = (BinaryExpression)condi.Body;
  7             condition+= GetSqlByExpression(body);
  8             return condition;
  9         }
 10         /// <summary>
 11         /// 通过Lambda解析为Sql
 12         /// </summary>
 13         /// <param name="func"></param>
 14         /// <returns></returns>
 15         public static string GetSqlByExpression(Expression func)
 16         {
 17             var funcType = CheckExpressionType(func);
 18             switch (funcType)
 19             {
 20                 case EnumNodeType.BinaryOperator:
 21                     return VisitBinaryExpression(func as BinaryExpression);
 22                 case EnumNodeType.Constant:
 23                     return VisitConstantExpression(func as ConstantExpression);
 24                 case EnumNodeType.Call:
 25                     return VisitMethodCallExpression(func as MethodCallExpression);
 26                 case EnumNodeType.UndryOperator:
 27                     return VisitUnaryExpression(func as UnaryExpression);
 28                 case EnumNodeType.MemberAccess:
 29                     return VisitMemberAccessExpression(func as MemberExpression);
 30                 default:
 31                     throw new NotSupportedException("不支持的操作在表达式处理中：");
 32             }
 33         }
 34         public string CheckExpression(Expression exp)
 35         {
 36             string leftClude = "", rightClude = "";
 37             if (exp.ToString().IndexOf("((") == 0)
 38             {
 39                 leftClude = "("; rightClude = ")";
 40             }
 41             if (exp.NodeType == ExpressionType.MemberAccess || exp.NodeType == ExpressionType.Constant)
 42             {
 43                 return exp.ToString();
 44             }
 45             if (exp as MethodCallExpression == null && exp as BinaryExpression == null)
 46                 throw new ArgumentException("不支持表达式的类型", "exp");
 47             if (exp as MethodCallExpression != null)
 48             {
 49                 MethodCallExpression cexp = exp as MethodCallExpression;
 50                 return "";
 51             }
 52             else
 53             {
 54                 switch (exp.NodeType)
 55                 {
 56                     case ExpressionType.Equal:
 57                         return leftClude + CheckExpression(((BinaryExpression)exp).Left) + " = " + CheckExpression(((BinaryExpression)exp).Right) + rightClude;
 58                     case ExpressionType.Or:
 59                         return leftClude + CheckExpression(((BinaryExpression)exp).Left) + " or " + CheckExpression(((BinaryExpression)exp).Right) + rightClude;
 60                     case ExpressionType.OrElse:
 61                         return leftClude + CheckExpression(((BinaryExpression)exp).Left) + " or " + CheckExpression(((BinaryExpression)exp).Right) + rightClude;
 62                     case ExpressionType.AndAlso:
 63                         return leftClude + CheckExpression(((BinaryExpression)exp).Left) + " and " + CheckExpression(((BinaryExpression)exp).Right) + rightClude;
 64                     default:
 65                         return "";
 66                 }
 67             }
 68         }
 69         /// <summary>
 70         /// 判断表达式类型
 71         /// </summary>
 72         /// <param name="func">lambda表达式</param>
 73         /// <returns></returns>
 74         private static EnumNodeType CheckExpressionType(Expression func)
 75         {
 76             switch (func.NodeType)
 77             {
 78                 case ExpressionType.AndAlso:
 79                 case ExpressionType.OrElse:
 80                 case ExpressionType.Equal:
 81                 case ExpressionType.GreaterThanOrEqual:
 82                 case ExpressionType.LessThanOrEqual:
 83                 case ExpressionType.GreaterThan:
 84                 case ExpressionType.LessThan:
 85                 case ExpressionType.NotEqual:
 86                     return EnumNodeType.BinaryOperator;
 87                 case ExpressionType.Constant:
 88                     return EnumNodeType.Constant;
 89                 case ExpressionType.MemberAccess:
 90                     return EnumNodeType.MemberAccess;
 91                 case ExpressionType.Call:
 92                     return EnumNodeType.Call;
 93                 case ExpressionType.Not:
 94                 case ExpressionType.Convert:
 95                     return EnumNodeType.UndryOperator;
 96                 default:
 97                     return EnumNodeType.Unknown;
 98             }
 99         }
100         private static string ExpressionTypeToString(ExpressionType type)
101         {
102             switch (type)
103             {
104                 case ExpressionType.Equal:
105                     return " = ";
106                 case ExpressionType.Or:
107                     return " or ";
108                 case ExpressionType.OrElse:
109                     return " or ";
110                 case ExpressionType.AndAlso:
111                     return " and ";
112                 default:
113                     return "";
114             }
115         }
116         /// <summary>
117         /// 判断一元表达式
118         /// </summary>
119         /// <param name="func"></param>
120         /// <returns></returns>
121         private static string VisitUnaryExpression(UnaryExpression func)
122         {
123             var result = ExpressionTypeToString(func.NodeType);
124             var funcType = CheckExpressionType(func.Operand);
125             switch (funcType)
126             {
127                 case EnumNodeType.BinaryOperator:
128                     return result + VisitBinaryExpression(func.Operand as BinaryExpression);
129                 case EnumNodeType.Constant:
130                     return result + VisitConstantExpression(func.Operand as ConstantExpression);
131                 case EnumNodeType.Call:
132                     return result + VisitMethodCallExpression(func.Operand as MethodCallExpression);
133                 case EnumNodeType.UndryOperator:
134                     return result + VisitUnaryExpression(func.Operand as UnaryExpression);
135                 case EnumNodeType.MemberAccess:
136                     return result + VisitMemberAccessExpression(func.Operand as MemberExpression);
137                 default:
138                     throw new NotSupportedException("不支持的操作在一元操作处理中：");
139             }
140         }
141 
142         /// <summary>
143         /// 判断常量表达式
144         /// </summary>
145         /// <param name="func"></param>
146         /// <returns></returns>
147         private static string VisitConstantExpression(ConstantExpression func)
148         {
149             if (func.Value.ToString() == "")
150             {
151                 return "\'\' ";
152             }
153             else if (func.Value.ToString() == "True")
154             {
155                 return "1 = 1 ";
156             }
157             else if (func.Value.ToString() == "False")
158             {
159                 return "0 = 1 ";
160             }
161             else
162             {
163                 return "'" + func.Value.ToString() + "' ";
164 
165             }
166         }
167 
168         /// <summary>
169         /// 判断包含变量的表达式
170         /// </summary>
171         /// <param name="func"></param>
172         /// <returns></returns>
173         private static string VisitMemberAccessExpression(MemberExpression func)
174         {
175             try
176             {
177                 var tablename = func.Expression.Type.Name;
178                 return tablename + "." + func.Member.Name + " ";
179             }
180             catch
181             {
182                 object value;
183                 switch (func.Type.Name)
184                 {
185                     case "Int32":
186                         {
187                             var getter = Expression.Lambda<Func<int>>(func).Compile();
188                             value = getter();
189                         }
190                         break;
191                     case "String":
192                         {
193                             var getter = Expression.Lambda<Func<string>>(func).Compile();
194                             value = "'" + getter() + "'";
195                         }
196                         break;
197                     case "DateTime":
198                         {
199                             var getter = Expression.Lambda<Func<DateTime>>(func).Compile();
200                             value = "'" + getter() + "'";
201                         }
202                         break;
203                     default:
204                         {
205                             var getter = Expression.Lambda<Func<object>>(func).Compile();
206                             value = getter();
207                         }
208                         break;
209                 }
210                 return value.ToString();
211             }
212         }
213         /// <summary>
214         /// 判断包含函数的表达式
215         /// </summary>
216         /// <param name="func"></param>
217         /// <returns></returns>
218         private static String VisitMethodCallExpression(MethodCallExpression func)
219         {
220             if (func.Method.Name.Contains("Contains"))
221             {
222                 if (func.Object.Type.Name.ToLower() == "string")
223                 {
224                     //获得调用者的内容元素
225                     var field = VisitMemberAccessExpression(func.Object as MemberExpression);
226                     //获得字段
227                     var caller = func.Arguments[0];
228                     var param = Expression.Lambda<Func<object>>(caller).Compile();
229                     return field + " like '%" + param() + "%'";
230                 }
231                 else
232                 {
233                     //获得调用者的内容元素
234                     var getter = Expression.Lambda<Func<object>>(func.Object).Compile();
235                     var data = getter() as IEnumerable;
236                     //获得字段
237                     var caller = func.Arguments[0];
238                     while (caller.NodeType == ExpressionType.Call)
239                     {
240                         caller = (caller as MethodCallExpression).Object;
241                     }
242                     var field = VisitMemberAccessExpression(caller as MemberExpression);
243                     var list = (from object i in data select "'" + i + "'").ToList();
244                     return field + " IN (" + string.Join(",", list.Cast<string>().ToArray()) + ") ";
245                 }
246             }
247             else
248             {
249                 throw new NotSupportedException("不支持的函数操作:" + func.Method.Name);
250             }
251         }
252         /// <summary> 
253         /// 判断包含二元运算符的表达式
254         /// </summary>
255         /// <remarks>注意，这个函数使用了递归，修改时注意不要修改了代码顺序和逻辑</remarks>
256         /// <param name="func"></param>
257         private static string VisitBinaryExpression(BinaryExpression func)
258         {
259             var result = "(";
260             var leftType = CheckExpressionType(func.Left);
261             switch (leftType)
262             {
263                 case EnumNodeType.BinaryOperator:
264                     result += VisitBinaryExpression(func.Left as BinaryExpression); break;
265                 case EnumNodeType.Constant:
266                     result += VisitConstantExpression(func.Left as ConstantExpression); break;
267                 case EnumNodeType.MemberAccess:
268                     result += VisitMemberAccessExpression(func.Left as MemberExpression); break;
269                 case EnumNodeType.UndryOperator:
270                     result += VisitUnaryExpression(func.Left as UnaryExpression); break;
271                 case EnumNodeType.Call:
272                     result += VisitMethodCallExpression(func.Left as MethodCallExpression); break;
273                 default:
274                     throw new NotSupportedException("不支持的操作在二元操作处理中：");
275             }
276 
277             result += ExpressionTypeToString(func.NodeType) + " ";
278 
279             var rightType = CheckExpressionType(func.Right);
280             switch (rightType)
281             {
282                 case EnumNodeType.BinaryOperator:
283                     result += VisitBinaryExpression(func.Right as BinaryExpression); break;
284                 case EnumNodeType.Constant:
285                     result += VisitConstantExpression(func.Right as ConstantExpression); break;
286                 case EnumNodeType.MemberAccess:
287                     result += VisitMemberAccessExpression(func.Right as MemberExpression); break;
288                 case EnumNodeType.UndryOperator:
289                     result += VisitUnaryExpression(func.Right as UnaryExpression); break;
290                 case EnumNodeType.Call:
291                     result += VisitMethodCallExpression(func.Right as MethodCallExpression); break;
292                 default:
293                     throw new NotSupportedException("不支持的操作在二元操作处理中：");
294             }
295 
296             result += ") ";
297             return result;
298         }
299     }
300 
301 
302     public enum EnumNodeType
303     {
304         [Description("二元运算符")]
305         BinaryOperator = 1,
306         [Description("一元运算符")]
307         UndryOperator = 2,
308         [Description("常量表达式")]
309         Constant = 3,
310         [Description("成员（变量）")]
311         MemberAccess = 4,
312         [Description("函数")]
313         Call = 5,
314         [Description("未知")]
315         Unknown = -99,
316         [Description("不支持")]
317         NotSupported = -98
318     }