摘要：
usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Linq;usingSystem.Linq.Expressions;usingSystem.Text;usingSystem.Threading.Tasks;namespaceMaiCore{/////////˂/s
usingSystem; usingSystem.Collections.Generic; usingSystem.ComponentModel; usingSystem.Linq; usingSystem.Linq.Expressions; usingSystem.Text; usingSystem.Threading.Tasks; namespaceMaiCore { /// <summary> /// /// </summary> public classLambdaToSqlHelper { /// <summary> ///NodeType枚举 /// </summary> private enumEnumNodeType { /// <summary> ///二元运算符 /// </summary> [Description("二元运算符")] BinaryOperator = 1, /// <summary> ///一元运算符 /// </summary> [Description("一元运算符")] UndryOperator = 2, /// <summary> ///常量表达式 /// </summary> [Description("常量表达式")] Constant = 3, /// <summary> ///成员（变量） /// </summary> [Description("成员（变量）")] MemberAccess = 4, /// <summary> ///函数 /// </summary> [Description("函数")] Call = 5, /// <summary> ///未知 /// </summary> [Description("未知")] Unknown = -99, /// <summary> ///不支持 /// </summary> [Description("不支持")] NotSupported = -98} /// <summary> ///判断表达式类型 /// </summary> /// <param name="exp">lambda表达式</param> /// <returns></returns> private staticEnumNodeType CheckExpressionType(Expression exp) { switch(exp.NodeType) { caseExpressionType.AndAlso: caseExpressionType.OrElse: caseExpressionType.Equal: caseExpressionType.GreaterThanOrEqual: caseExpressionType.LessThanOrEqual: caseExpressionType.GreaterThan: caseExpressionType.LessThan: caseExpressionType.NotEqual: returnEnumNodeType.BinaryOperator; caseExpressionType.Constant: returnEnumNodeType.Constant; caseExpressionType.MemberAccess: returnEnumNodeType.MemberAccess; caseExpressionType.Call: returnEnumNodeType.Call; caseExpressionType.Not: caseExpressionType.Convert: returnEnumNodeType.UndryOperator; default: returnEnumNodeType.Unknown; } } /// <summary> ///表达式类型转换 /// </summary> /// <param name="type"></param> /// <returns></returns> private static stringExpressionTypeCast(ExpressionType type) { switch(type) { caseExpressionType.And: caseExpressionType.AndAlso: return "and "; caseExpressionType.Equal: return "= "; caseExpressionType.GreaterThan: return "> "; caseExpressionType.GreaterThanOrEqual: return ">= "; caseExpressionType.LessThan: return "< "; caseExpressionType.LessThanOrEqual: return "<= "; caseExpressionType.NotEqual: return "<> "; caseExpressionType.Or: caseExpressionType.OrElse: return "or "; caseExpressionType.Add: caseExpressionType.AddChecked: return "+ "; caseExpressionType.Subtract: caseExpressionType.SubtractChecked: return "- "; caseExpressionType.Divide: return "/ "; caseExpressionType.Multiply: caseExpressionType.MultiplyChecked: return "* "; default: return null; } } private static string BinarExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { BinaryExpression be = exp asBinaryExpression; Expression left =be.Left; Expression right =be.Right; ExpressionType type =be.NodeType; string sb = "("; //先处理左边 sb +=ExpressionRouter(left, listSqlParaModel); sb +=ExpressionTypeCast(type); //再处理右边 string sbTmp =ExpressionRouter(right, listSqlParaModel); if (sbTmp == "null") { if (sb.EndsWith("= ")) sb = sb.Substring(0, sb.Length - 2) + "is null"; else if (sb.EndsWith("<> ")) sb = sb.Substring(0, sb.Length - 2) + "is not null"; } elsesb +=sbTmp; return sb += ")"; } private static string ConstantExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { ConstantExpression ce = exp asConstantExpression; if (ce.Value == null) { return "null"; } else if (ce.Value isValueType) { GetSqlParaModel(listSqlParaModel, GetValueType(ce.Value)); return "@para" +listSqlParaModel.Count; } else if (ce.Value is string || ce.Value is DateTime || ce.Value is char) { GetSqlParaModel(listSqlParaModel, GetValueType(ce.Value)); return "@para" +listSqlParaModel.Count; } return ""; } private static string LambdaExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { LambdaExpression le = exp asLambdaExpression; returnExpressionRouter(le.Body, listSqlParaModel); } private static string MemberExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { if (!exp.ToString().StartsWith("value")) { MemberExpression me = exp asMemberExpression; if (me.Member.Name == "Now") { GetSqlParaModel(listSqlParaModel, DateTime.Now); return "@para" +listSqlParaModel.Count; } returnme.Member.Name; } else{ var result =Expression.Lambda(exp).Compile().DynamicInvoke(); if (result == null) { return "null"; } else if (result isValueType) { GetSqlParaModel(listSqlParaModel, GetValueType(result)); return "@para" +listSqlParaModel.Count; } else if (result is string || result is DateTime || result is char) { GetSqlParaModel(listSqlParaModel, GetValueType(result)); return "@para" +listSqlParaModel.Count; } else if (result is int[]) { var rl = result as int[]; StringBuilder sbTmp = newStringBuilder(); foreach (var r inrl) { GetSqlParaModel(listSqlParaModel, r.ToString().ToInt32()); sbTmp.Append("@para" + listSqlParaModel.Count + ","); } return sbTmp.ToString().Substring(0, sbTmp.ToString().Length - 1); } else if (result is string[]) { var rl = result as string[]; StringBuilder sbTmp = newStringBuilder(); foreach (var r inrl) { GetSqlParaModel(listSqlParaModel, r.ToString()); sbTmp.Append("@para" + listSqlParaModel.Count + ","); } return sbTmp.ToString().Substring(0, sbTmp.ToString().Length - 1); } } return ""; } private static string MethodCallExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { MethodCallExpression mce = exp asMethodCallExpression; if (mce.Method.Name == "Contains") { if (mce.Object == null) { return string.Format("{0} in ({1})", ExpressionRouter(mce.Arguments[1], listSqlParaModel), ExpressionRouter(mce.Arguments[0], listSqlParaModel)); } else{ if (mce.Object.NodeType ==ExpressionType.MemberAccess) { //w => w.name.Contains("1") var _name =ExpressionRouter(mce.Object, listSqlParaModel); var _value = ExpressionRouter(mce.Arguments[0], listSqlParaModel); var index = _value.RetainNumber().ToInt32() - 1; listSqlParaModel[index].value = "%{0}%".FormatWith(listSqlParaModel[index].value); return string.Format("{0} like {1}", _name, _value); } } } else if (mce.Method.Name == "OrderBy") { return string.Format("{0} asc", ExpressionRouter(mce.Arguments[1], listSqlParaModel)); } else if (mce.Method.Name == "OrderByDescending") { return string.Format("{0} desc", ExpressionRouter(mce.Arguments[1], listSqlParaModel)); } else if (mce.Method.Name == "ThenBy") { return string.Format("{0},{1} asc", MethodCallExpressionProvider(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel)); } else if (mce.Method.Name == "ThenByDescending") { return string.Format("{0},{1} desc", MethodCallExpressionProvider(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel)); } else if (mce.Method.Name == "Like") { return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel).Replace("'", "")); } else if (mce.Method.Name == "NotLike") { return string.Format("({0} not like '%{1}%')", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel).Replace("'", "")); } else if (mce.Method.Name == "In") { return string.Format("{0} in ({1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel)); } else if (mce.Method.Name == "NotIn") { return string.Format("{0} not in ({1})", ExpressionRouter(mce.Arguments[0], listSqlParaModel), ExpressionRouter(mce.Arguments[1], listSqlParaModel)); } return ""; } private static string NewArrayExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { NewArrayExpression ae = exp asNewArrayExpression; StringBuilder sbTmp = newStringBuilder(); foreach (Expression ex inae.Expressions) { sbTmp.Append(ExpressionRouter(ex, listSqlParaModel)); sbTmp.Append(","); } return sbTmp.ToString(0, sbTmp.Length - 1); } private static string ParameterExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { ParameterExpression pe = exp asParameterExpression; returnpe.Type.Name; } private static string UnaryExpressionProvider(Expression exp, List<SqlParaModel>listSqlParaModel) { UnaryExpression ue = exp asUnaryExpression; var result =ExpressionRouter(ue.Operand, listSqlParaModel); ExpressionType type =exp.NodeType; if (type ==ExpressionType.Not) { if (result.Contains("in ")) { result = result.Replace("in ", "not in "); } if (result.Contains("like ")) { result = result.Replace("like ", "not like "); } } returnresult; } /// <summary> ///路由计算 /// </summary> /// <param name="exp"></param> /// <param name="listSqlParaModel"></param> /// <returns></returns> private static string ExpressionRouter(Expression exp, List<SqlParaModel>listSqlParaModel) { var nodeType =exp.NodeType; if (exp is BinaryExpression) //表示具有二进制运算符的表达式 { returnBinarExpressionProvider(exp, listSqlParaModel); } else if (exp is ConstantExpression) //表示具有常数值的表达式 { returnConstantExpressionProvider(exp, listSqlParaModel); } else if (exp is LambdaExpression) //介绍 lambda 表达式。 它捕获一个类似于 .NET 方法主体的代码块 { returnLambdaExpressionProvider(exp, listSqlParaModel); } else if (exp is MemberExpression) //表示访问字段或属性 { returnMemberExpressionProvider(exp, listSqlParaModel); } else if (exp is MethodCallExpression) //表示对静态方法或实例方法的调用 { returnMethodCallExpressionProvider(exp, listSqlParaModel); } else if (exp is NewArrayExpression) //表示创建一个新数组，并可能初始化该新数组的元素 { returnNewArrayExpressionProvider(exp, listSqlParaModel); } else if (exp is ParameterExpression) //表示一个命名的参数表达式。 { returnParameterExpressionProvider(exp, listSqlParaModel); } else if (exp is UnaryExpression) //表示具有一元运算符的表达式 { returnUnaryExpressionProvider(exp, listSqlParaModel); } return null; } /// <summary> ///值类型转换 /// </summary> /// <param name="_value"></param> /// <returns></returns> private static object GetValueType(object_value) { var _type =_value.GetType().Name; switch(_type) { case "Decimal ": return_value.ToDecimal(); case "Int32": return_value.ToInt32(); case "DateTime": return_value.ToDateTime(); case "String": return_value.ToString(); case "Char":return_value.ToChar(); case "Boolean":return_value.ToBoolean(); default: return_value; } } /// <summary> ///sql参数 /// </summary> /// <param name="listSqlParaModel"></param> /// <param name="val"></param> private static void GetSqlParaModel(List<SqlParaModel> listSqlParaModel, objectval) { SqlParaModel p = newSqlParaModel(); p.name = "para" + (listSqlParaModel.Count + 1); p.value =val; listSqlParaModel.Add(p); } /// <summary> ///lambda表达式转换sql /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <param name="listSqlParaModel"></param> /// <returns></returns> public static string GetWhereSql<T>(Expression<Func<T, bool>> where, List<SqlParaModel> listSqlParaModel) where T : class{ string result = string.Empty; if (where != null) { Expression exp = where.Body asExpression; result =ExpressionRouter(exp, listSqlParaModel); } if (result != string.Empty) { result = "where " +result; } returnresult; } /// <summary> ///lambda表达式转换sql /// </summary> /// <typeparam name="T"></typeparam> /// <param name="orderBy"></param> /// <returns></returns> public static string GetOrderBySql<T>(Expression<Func<IQueryable<T>, IOrderedQueryable<T>>> orderBy) where T : class{ string result = string.Empty; if (orderBy != null && orderBy.Body isMethodCallExpression) { MethodCallExpression exp = orderBy.Body asMethodCallExpression; List<SqlParaModel> listSqlParaModel = new List<SqlParaModel>(); result =MethodCallExpressionProvider(exp, listSqlParaModel); } if (result != string.Empty) { result = "order by " +result; } returnresult; } /// <summary> ///lambda表达式转换sql /// </summary> /// <typeparam name="T"></typeparam> /// <param name="fields"></param> /// <returns></returns> public static string GetQueryField<T>(Expression<Func<T, object>>fields) { StringBuilder sbSelectFields = newStringBuilder(); if (fields.Body isNewExpression) { NewExpression ne = fields.Body asNewExpression; for (var i = 0; i < ne.Members.Count; i++) { sbSelectFields.Append(ne.Members[i].Name + ","); } } else if (fields.Body isParameterExpression) { sbSelectFields.Append("*"); } else{ sbSelectFields.Append("*"); } if (sbSelectFields.Length > 1) { sbSelectFields = sbSelectFields.Remove(sbSelectFields.Length - 1, 1); } returnsbSelectFields.ToString(); } } } ----------------------------------------------------------------------------------------------- demo： classProgram { static void Main(string[] args) { //Expression<Func<MyClass, bool>> where = w => w.id == "123456"; Expression<Func<MyClass, bool>> where = w => w.id.Contains("1"); List<SqlParaModel> listSqlParaModel = new List<SqlParaModel>(); var sql = LambdaToSqlHelper.GetWhereSql(where, listSqlParaModel); } } classMyClass { public stringid; public stringname; public stringdesc; public decimalprice; public intstock; public boolisShow; publicDateTime createTime; }