lamber表达式sql_(.NET Framework)Lambda表达式转换为sql

///

/// 表达式转sql帮助类

///

public static class LambdaToSqlHelper

{

///

/// 从表达式获取sql

///

///

///

///

public static string GetSqlFromExpression(Expression> func)

{

if (func != null && func.Body is BinaryExpression be)

{

return BinarExpressionProvider(be.Left, be.Right, be.NodeType);

}

else

{

return " ( 1 = 1 ) ";

}

}

///

/// 拆分、拼接sql

///

///

///

///

///

static string BinarExpressionProvider(Expression left, Expression right, ExpressionType type)

{

string sb = "(";

sb += ExpressionRouter(left);

sb += ExpressionTypeCast(type);

string tmpStr = ExpressionRouter(right);

if (tmpStr == "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";

}

else sb += tmpStr;

return sb += ")";

}

///

/// 拆分、拼接sql

///

///

///

static string ExpressionRouter(Expression exp)

{

string sb = string.Empty;

if (exp is BinaryExpression be)

{

return BinarExpressionProvider(be.Left, be.Right, be.NodeType);

}

else if (exp is MemberExpression me)

{

return me.Member.Name;

}

else if (exp is NewArrayExpression ae)

{

StringBuilder tmpstr = new StringBuilder();

foreach (Expression ex in ae.Expressions)

{

tmpstr.Append(ExpressionRouter(ex));

tmpstr.Append(",");

}

return tmpstr.ToString(0, tmpstr.Length - 1);

}

else if (exp is MethodCallExpression mce)

{

var attributeData = mce.Method.GetCustomAttributes(typeof(ToSqlFormat), false).First();

return string.Format(((ToSqlFormat)attributeData).Format, ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));

}

else if (exp is ConstantExpression ce)

{

if (ce.Value == null)

return "null";

else if (ce.Value is ValueType)

return ce.Value.ToString();

else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)

return string.Format("'{0}'", ce.Value.ToString());

}

else if (exp is UnaryExpression)

{

UnaryExpression ue = ((UnaryExpression)exp);

return ExpressionRouter(ue.Operand);

}

return null;

}

///

/// 介绍表达式树节点的节点类型 转换为 sql关键字

///

///

///

static string ExpressionTypeCast(ExpressionType type)

{

switch (type)

{

case ExpressionType.And:

return " & ";

case ExpressionType.AndAlso:

return " AND ";

case ExpressionType.Equal:

return " =";

case ExpressionType.GreaterThan:

return " >";

case ExpressionType.GreaterThanOrEqual:

return ">=";

case ExpressionType.LessThan:

return "

case ExpressionType.LessThanOrEqual:

return "<=";

case ExpressionType.NotEqual:

return "<>";

case ExpressionType.Or:

return " | ";

case ExpressionType.OrElse:

return " Or ";

case ExpressionType.Add:

case ExpressionType.AddChecked:

return "+";

case ExpressionType.Subtract:

case ExpressionType.SubtractChecked:

return "-";

case ExpressionType.Divide:

return "/";

case ExpressionType.Multiply:

case ExpressionType.MultiplyChecked:

return "*";

default:

return null;

}

}

[ToSqlFormat("{0} IN ({1})")]

public static bool In(this T obj, T[] array)

{

return true;

}

[ToSqlFormat("{0} NOT IN ({1})")]

public static bool NotIn(this T obj, T[] array)

{

return true;

}

[ToSqlFormat("{0} LIKE {1}")]

public static bool Like(this string str, string likeStr)

{

return true;

}

[ToSqlFormat("{0} NOT LIKE {1}")]

public static bool NotLike(this string str, string likeStr)

{

return true;

}

}

public class ToSqlFormat : Attribute

{

public string Format { get; set; }

public ToSqlFormat(string str)

{

Format = str;

}

}