lambda表达式转换sql

这是我在博客园的第一遍文章，想分享下lambda表达式转换sql。

喜欢EF的便捷与优雅，不喜欢生成的一坨sql。（PS：公司封装了一套访问数据库的方法，所以不确定是不是EF的问题，反正就是一坨密密麻麻的的sql，我有点点处女座小纠结，虽然我是天蝎座）

好了，废话少说。

  1     public class LambdaToSqlHelper
  2     {
  3         /// <summary>
  4         /// NodeType枚举
  5         /// </summary>
  6         private enum EnumNodeType
  7         {
  8             /// <summary>
  9             /// 二元运算符
 10             /// </summary>
 11             [Description("二元运算符")]
 12             BinaryOperator = 1,
 13 
 14             /// <summary>
 15             /// 一元运算符
 16             /// </summary>
 17             [Description("一元运算符")]
 18             UndryOperator = 2,
 19 
 20             /// <summary>
 21             /// 常量表达式
 22             /// </summary>
 23             [Description("常量表达式")]
 24             Constant = 3,
 25 
 26             /// <summary>
 27             /// 成员（变量）
 28             /// </summary>
 29             [Description("成员（变量）")]
 30             MemberAccess = 4,
 31 
 32             /// <summary>
 33             /// 函数
 34             /// </summary>
 35             [Description("函数")]
 36             Call = 5,
 37 
 38             /// <summary>
 39             /// 未知
 40             /// </summary>
 41             [Description("未知")]
 42             Unknown = -99,
 43 
 44             /// <summary>
 45             /// 不支持
 46             /// </summary>
 47             [Description("不支持")]
 48             NotSupported = -98
 49         }
 50 
 51         /// <summary>
 52         /// 判断表达式类型
 53         /// </summary>
 54         /// <param name="exp">lambda表达式</param>
 55         /// <returns></returns>
 56         private static EnumNodeType CheckExpressionType(Expression exp)
 57         {
 58             switch (exp.NodeType)
 59             {
 60                 case ExpressionType.AndAlso:
 61                 case ExpressionType.OrElse:
 62                 case ExpressionType.Equal:
 63                 case ExpressionType.GreaterThanOrEqual:
 64                 case ExpressionType.LessThanOrEqual:
 65                 case ExpressionType.GreaterThan:
 66                 case ExpressionType.LessThan:
 67                 case ExpressionType.NotEqual:
 68                     return EnumNodeType.BinaryOperator;
 69                 case ExpressionType.Constant:
 70                     return EnumNodeType.Constant;
 71                 case ExpressionType.MemberAccess:
 72                     return EnumNodeType.MemberAccess;
 73                 case ExpressionType.Call:
 74                     return EnumNodeType.Call;
 75                 case ExpressionType.Not:
 76                 case ExpressionType.Convert:
 77                     return EnumNodeType.UndryOperator;
 78                 default:
 79                     return EnumNodeType.Unknown;
 80             }
 81         }
 82 
 83         /// <summary>
 84         /// 表达式类型转换
 85         /// </summary>
 86         /// <param name="type"></param>
 87         /// <returns></returns>
 88         private static string ExpressionTypeCast(ExpressionType type)
 89         {
 90             switch (type)
 91             {
 92                 case ExpressionType.And:
 93                 case ExpressionType.AndAlso:
 94                     return " and ";
 95                 case ExpressionType.Equal:
 96                     return " = ";
 97                 case ExpressionType.GreaterThan:
 98                     return " > ";
 99                 case ExpressionType.GreaterThanOrEqual:
100                     return " >= ";
101                 case ExpressionType.LessThan:
102                     return " < ";
103                 case ExpressionType.LessThanOrEqual:
104                     return " <= ";
105                 case ExpressionType.NotEqual:
106                     return " <> ";
107                 case ExpressionType.Or:
108                 case ExpressionType.OrElse:
109                     return " or ";
110                 case ExpressionType.Add:
111                 case ExpressionType.AddChecked:
112                     return " + ";
113                 case ExpressionType.Subtract:
114                 case ExpressionType.SubtractChecked:
115                     return " - ";
116                 case ExpressionType.Divide:
117                     return " / ";
118                 case ExpressionType.Multiply:
119                 case ExpressionType.MultiplyChecked:
120                     return " * ";
121                 default:
122                     return null;
123             }
124         }
125 
126         private static string BinarExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
127         {
128             BinaryExpression be = exp as BinaryExpression;
129             Expression left = be.Left;
130             Expression right = be.Right;
131             ExpressionType type = be.NodeType;
132             string sb = "(";
133             //先处理左边
134             sb += ExpressionRouter(left, listSqlParaModel);
135             sb += ExpressionTypeCast(type);
136             //再处理右边
137             string sbTmp = ExpressionRouter(right, listSqlParaModel);
138             if (sbTmp == "null")
139             {
140                 if (sb.EndsWith(" = "))
141                     sb = sb.Substring(0, sb.Length - 2) + " is null";
142                 else if (sb.EndsWith(" <> "))
143                     sb = sb.Substring(0, sb.Length - 2) + " is not null";
144             }
145             else
146                 sb += sbTmp;
147             return sb += ")";
148         }
149 
150         private static string ConstantExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
151         {
152             ConstantExpression ce = exp as ConstantExpression;
153             if (ce.Value == null)
154             {
155                 return "null";
156             }
157             else if (ce.Value is ValueType)
158             {
159                 GetSqlParaModel(listSqlParaModel, GetValueType(ce.Value));
160                 return "@para" + listSqlParaModel.Count;
161             }
162             else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
163             {
164                 GetSqlParaModel(listSqlParaModel, GetValueType(ce.Value));
165                 return "@para" + listSqlParaModel.Count;
166             }
167             return "";
168         }
169 
170         private static string LambdaExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
171         {
172             LambdaExpression le = exp as LambdaExpression;
173             return ExpressionRouter(le.Body, listSqlParaModel);
174         }
175 
176         private static string MemberExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
177         {
178             if (!exp.ToString().StartsWith("value"))
179             {
180                 MemberExpression me = exp as MemberExpression;
181                 if (me.Member.Name == "Now")
182                 {
183                     GetSqlParaModel(listSqlParaModel, DateTime.Now);
184                     return "@para" + listSqlParaModel.Count;
185                 }
186                 return me.Member.Name;
187             }
188             else
189             {
190                 var result = Expression.Lambda(exp).Compile().DynamicInvoke();
191                 if (result == null)
192                 {
193                     return "null";
194                 }
195                 else if (result is ValueType)
196                 {
197                     GetSqlParaModel(listSqlParaModel, GetValueType(result));
198                     return "@para" + listSqlParaModel.Count;
199                 }
200                 else if (result is string || result is DateTime || result is char)
201                 {
202                     GetSqlParaModel(listSqlParaModel, GetValueType(result));
203                     return "@para" + listSqlParaModel.Count;
204                 }
205                 else if (result is int[])
206                 {
207                     var rl = result as int[];
208                     StringBuilder sbTmp = new StringBuilder();
209                     foreach (var r in rl)
210                     {
211                         GetSqlParaModel(listSqlParaModel, r.ToString().ToInt32());
212                         sbTmp.Append("@para" + listSqlParaModel.Count + ",");
213                     }
214                     return sbTmp.ToString().Substring(0, sbTmp.ToString().Length - 1);
215                 }
216                 else if (result is string[])
217                 {
218                     var rl = result as string[];
219                     StringBuilder sbTmp = new StringBuilder();
220                     foreach (var r in rl)
221                     {
222                         GetSqlParaModel(listSqlParaModel, r.ToString());
223                         sbTmp.Append("@para" + listSqlParaModel.Count + ",");
224                     }
225                     return sbTmp.ToString().Substring(0, sbTmp.ToString().Length - 1);
226                 }                
227             }
228             return "";
229         }
230 
231         private static string MethodCallExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
232         {
233             MethodCallExpression mce = exp as MethodCallExpression;
234             if (mce.Method.Name == "Contains")
235             {
236                 if (mce.Object == null)
237                 {
238                     return string.Format("{0} in ({1})", ExpressionRouter(mce.Arguments[1], listSqlParaModel), ExpressionRouter(mce.Arguments[0], listSqlParaModel));
239                 }
240                 else
241                 {
242                     if (mce.Object.NodeType == ExpressionType.MemberAccess)
243                     {
244                         //w => w.name.Contains("1")
245                         var _name = ExpressionRouter(mce.Object, listSqlParaModel);
246                         var _value = ExpressionRouter(mce.Arguments[0], listSqlParaModel);
247                         var index = _value.RetainNumber().ToInt32() - 1;
248                         listSqlParaModel[index].value = "%{0}%".FormatWith(listSqlParaModel[index].value);
249                         return string.Format("{0} like {1}", _name, _value);
250                     }
251                 }
252             }
253             else if (mce.Method.Name == "OrderBy")
254             {
255                 return string.Format("{0} asc", ExpressionRouter(mce.Arguments[1], listSqlParaModel));
256             }
257             else if (mce.Method.Name == "OrderByDescending")
258             {
259                 return string.Format("{0} desc", ExpressionRouter(mce.Arguments[1], listSqlParaModel));
260             }
261             else if (mce.Method.Name == "ThenBy")
262             {
263                 return string.Format("{0},{1} asc", MethodCallExpressionProvider(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
264             }
265             else if (mce.Method.Name == "ThenByDescending")
266             {
267                 return string.Format("{0},{1} desc", MethodCallExpressionProvider(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
268             }
269             else if (mce.Method.Name == "Like")
270             {
271                 return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel).Replace("'", ""));
272             }
273             else if (mce.Method.Name == "NotLike")
274             {
275                 return string.Format("({0} not like '%{1}%')", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel).Replace("'", ""));
276             }
277             else if (mce.Method.Name == "In")
278             {
279                 return string.Format("{0} in ({1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
280             }
281             else if (mce.Method.Name == "NotIn")
282             {
283                 return string.Format("{0} not in ({1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel));
284             }
285             return "";
286         }
287 
288         private static string NewArrayExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
289         {
290             NewArrayExpression ae = exp as NewArrayExpression;
291             StringBuilder sbTmp = new StringBuilder();
292             foreach (Expression ex in ae.Expressions)
293             {
294                 sbTmp.Append(ExpressionRouter(ex, listSqlParaModel));
295                 sbTmp.Append(",");
296             }
297             return sbTmp.ToString(0, sbTmp.Length - 1);
298         }
299 
300         private static string ParameterExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
301         {
302             ParameterExpression pe = exp as ParameterExpression;
303             return pe.Type.Name;
304         }
305 
306         private static string UnaryExpressionProvider(Expression exp, List<SqlParaModel> listSqlParaModel)
307         {
308             UnaryExpression ue = exp as UnaryExpression;
309             var result = ExpressionRouter(ue.Operand, listSqlParaModel);
310             ExpressionType type = exp.NodeType;
311             if (type == ExpressionType.Not)
312             {
313                 if (result.Contains(" in "))
314                 {
315                     result = result.Replace(" in ", " not in ");
316                 }
317                 if (result.Contains(" like "))
318                 {
319                     result = result.Replace(" like ", " not like ");
320                 }
321             }
322             return result;
323         }
324 
325         /// <summary>
326         /// 路由计算
327         /// </summary>
328         /// <param name="exp"></param>
329         /// <param name="listSqlParaModel"></param>
330         /// <returns></returns>
331         private static string ExpressionRouter(Expression exp, List<SqlParaModel> listSqlParaModel)
332         {
333             var nodeType = exp.NodeType;
334             if (exp is BinaryExpression)    //表示具有二进制运算符的表达式
335             {
336                 return BinarExpressionProvider(exp, listSqlParaModel);
337             }
338             else if (exp is ConstantExpression) //表示具有常数值的表达式
339             {
340                 return ConstantExpressionProvider(exp, listSqlParaModel);
341             }
342             else if (exp is LambdaExpression)   //介绍 lambda 表达式。 它捕获一个类似于 .NET 方法主体的代码块
343             {
344                 return LambdaExpressionProvider(exp, listSqlParaModel);
345             }
346             else if (exp is MemberExpression)   //表示访问字段或属性
347             {
348                 return MemberExpressionProvider(exp, listSqlParaModel);
349             }
350             else if (exp is MethodCallExpression)   //表示对静态方法或实例方法的调用
351             {
352                 return MethodCallExpressionProvider(exp, listSqlParaModel);
353             }
354             else if (exp is NewArrayExpression) //表示创建一个新数组，并可能初始化该新数组的元素
355             {
356                 return NewArrayExpressionProvider(exp, listSqlParaModel);
357             }
358             else if (exp is ParameterExpression)    //表示一个命名的参数表达式。
359             {
360                 return ParameterExpressionProvider(exp, listSqlParaModel);
361             }
362             else if (exp is UnaryExpression)    //表示具有一元运算符的表达式
363             {
364                 return UnaryExpressionProvider(exp, listSqlParaModel);
365             }
366             return null;
367         }
368 
369         /// <summary>
370         /// 值类型转换
371         /// </summary>
372         /// <param name="_value"></param>
373         /// <returns></returns>
374         private static object GetValueType(object _value)
375         {
376             var _type = _value.GetType().Name;
377             switch (_type)
378             {
379                 case "Decimal ": return _value.ToDecimal();
380                 case "Int32": return _value.ToInt32();
381                 case "DateTime": return _value.ToDateTime();
382                 case "String": return _value.ToString();
383                 case "Char":return _value.ToChar();
384                 case "Boolean":return _value.ToBoolean();
385                 default: return _value;
386             }
387         }
388 
389         /// <summary>
390         /// sql参数
391         /// </summary>
392         /// <param name="listSqlParaModel"></param>
393         /// <param name="val"></param>
394         private static void GetSqlParaModel(List<SqlParaModel> listSqlParaModel, object val)
395         {
396             SqlParaModel p = new SqlParaModel();
397             p.name = "para" + (listSqlParaModel.Count + 1);
398             p.value = val;
399             listSqlParaModel.Add(p);
400         }
401 
402         /// <summary>
403         /// lambda表达式转换sql
404         /// </summary>
405         /// <typeparam name="T"></typeparam>
406         /// <param name="where"></param>
407         /// <param name="listSqlParaModel"></param>
408         /// <returns></returns>
409         public static string GetWhereSql<T>(Expression<Func<T, bool>> where, List<SqlParaModel> listSqlParaModel) where T : class
410         {
411             string result = string.Empty;
412             if (where != null)
413             {
414                 Expression exp = where.Body as Expression;
415                 result = ExpressionRouter(exp, listSqlParaModel);
416             }
417             if (result != string.Empty)
418             {
419                 result = " where " + result;
420             }
421             return result;
422         }
423 
424         /// <summary>
425         /// lambda表达式转换sql
426         /// </summary>
427         /// <typeparam name="T"></typeparam>
428         /// <param name="orderBy"></param>
429         /// <returns></returns>
430         public static string GetOrderBySql<T>(Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy) where T : class
431         {
432             string result = string.Empty;
433             if (orderBy != null && orderBy.Body is MethodCallExpression)
434             {
435                 MethodCallExpression exp = orderBy.Body as MethodCallExpression;
436                 List<SqlParaModel> listSqlParaModel = new List<SqlParaModel>();
437                 result = MethodCallExpressionProvider(exp, listSqlParaModel);
438             }
439             if (result != string.Empty)
440             {
441                 result = " order by " + result;
442             }
443             return result;
444         }
445 
446         /// <summary>
447         /// lambda表达式转换sql
448         /// </summary>
449         /// <typeparam name="T"></typeparam>
450         /// <param name="fields"></param>
451         /// <returns></returns>
452         public static string GetQueryField<T>(Expression<Func<T, object>> fields)
453         {
454             StringBuilder sbSelectFields = new StringBuilder();
455             if (fields.Body is NewExpression)
456             {
457                 NewExpression ne = fields.Body as NewExpression;
458                 for (var i = 0; i < ne.Members.Count; i++)
459                 {
460                     sbSelectFields.Append(ne.Members[i].Name + ",");
461                 }
462             }
463             else if (fields.Body is ParameterExpression)
464             {
465                 sbSelectFields.Append("*");
466             }
467             else
468             {
469                 sbSelectFields.Append("*");
470             }
471             if (sbSelectFields.Length > 1)
472             {
473                 sbSelectFields = sbSelectFields.Remove(sbSelectFields.Length - 1, 1);
474             }
475             return sbSelectFields.ToString();
476         }
477 
478     }

View Code

SqlParaModel如下：

 1     public class SqlParaModel
 2     {
 3         /// <summary>
 4         /// 
 5         /// </summary>
 6         public string name { set; get; }
 7 
 8         /// <summary>
 9         /// 
10         /// </summary>
11         public object value { set; get; }
12     }

demo：

 1     class MyClass
 2     {
 3         public string id;
 4         public string name;
 5         public string desc;
 6         public decimal price;
 7         public int stock;
 8         public bool isShow;
 9         public DateTime createTime;
10     }

 1     class Program
 2     {
 3 
 4         static void Main(string[] args)
 5         {
 6             //Expression<Func<MyClass, bool>> where = w => w.id == "123456";
 7             Expression<Func<MyClass, bool>> where = w => w.id.Contains("1");
 8             List<SqlParaModel> listSqlParaModel = new List<SqlParaModel>();
 9             var sql = LambdaToSqlHelper.GetWhereSql(where, listSqlParaModel);
10         }
11 
12     }

参考：

http://www.cnblogs.com/kakura/p/6108950.html
http://www.cnblogs.com/zhyue/p/5690807.html

posted @ 2017-07-10 18:03 初冬十月 Views(6448) Comments(9) Edit 收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

初冬十月

每天进步一点点，努力靠近大神们。一名待业的妹子。坐标广州。

lambda表达式转换sql