『壹』 如何將where條件的lambda表達式 轉成 sql where語句
public static class LambdaToSqlHelper
{
#region 基礎方法
#region 獲取條件語句方法
private static string GetWhereSql<T>(Expression<Func<T, bool>> func, List<ParMODEL> parModelList) where T : class
{
string res = "";
if (func.Body is BinaryExpression)
{
//起始參數
BinaryExpression be = ((BinaryExpression)func.Body);
res = BinarExpressionProvider(be.Left, be.Right, be.NodeType, parModelList);
}
else if (func.Body is MethodCallExpression)
{
MethodCallExpression be = ((MethodCallExpression)func.Body);
res = ExpressionRouter(func.Body, parModelList);
}
else
{
res = " ";
}
return res;
}
#endregion 獲取條件語句方法
#region 獲取排序語句 order by
private static string GetOrderSql<T>(Expression<Func<T, object>> exp) where T : class
{
var res = "";
if (exp.Body is UnaryExpression)
{
UnaryExpression ue = ((UnaryExpression)exp.Body);
List<ParMODEL> parModelList = new List<ParMODEL>();
res = "order by `" + ExpressionRouter(ue.Operand, parModelList).ToLower() + "`";
}
else
{
MemberExpression order = ((MemberExpression)exp.Body);
res = "order by `" + order.Member.Name.ToLower() + "`";
}
return res;
}
#endregion 獲取排序語句 order by
#endregion 基礎方法
#region 底層
public static bool In<T>(this T obj, T[] array)
{
return true;
}
public static bool NotIn<T>(this T obj, T[] array)
{
return true;
}
public static bool Like(this string str, string likeStr)
{
return true;
}
public static bool NotLike(this string str, string likeStr)
{
return true;
}
private static string GetValueStringByType(object oj)
{
if (oj == null)
{
return "null";
}
else if (oj is ValueType)
{
return oj.ToString();
}
else if (oj is string || oj is DateTime || oj is char)
{
return string.Format("'{0}'", oj.ToString());
}
else
{
return string.Format("'{0}'", oj.ToString());
}
}
private static string BinarExpressionProvider(Expression left, Expression right, ExpressionType type, List<ParMODEL> parModelList)
{
string sb = "(";
//先處理左邊
string reLeftStr = ExpressionRouter(left, parModelList);
sb += reLeftStr;
sb += ExpressionTypeCast(type);
//再處理右邊
string tmpStr = ExpressionRouter(right, parModelList);
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 += ")";
}
private static string ExpressionRouter(Expression exp, List<ParMODEL> parModelList)
{
string sb = string.Empty;
if (exp is BinaryExpression)
{
BinaryExpression be = ((BinaryExpression)exp);
return BinarExpressionProvider(be.Left, be.Right, be.NodeType, parModelList);
}
else if (exp is MemberExpression)
{
MemberExpression me = ((MemberExpression)exp);
if (!exp.ToString().StartsWith("value"))
{
return me.Member.Name;
}
else
{
var result = Expression.Lambda(exp).Compile().DynamicInvoke();
if (result == null)
{
return "null";
}
else if (result is ValueType)
{
ParMODEL p = new ParMODEL();
p.name = "par" + (parModelList.Count + 1);
p.value = result.ToString().ToIntByStr();
parModelList.Add(p);
//return ce.Value.ToString();
return "@par" + parModelList.Count;
}
else if (result is string || result is DateTime || result is char)
{
ParMODEL p = new ParMODEL();
p.name = "par" + (parModelList.Count + 1);
p.value = result.ToString();
parModelList.Add(p);
//return string.Format("'{0}'", ce.Value.ToString());
return "@par" + parModelList.Count;
}
else if (result is int[])
{
var rl = result as int[];
StringBuilder sbIntStr = new StringBuilder();
foreach (var r in rl)
{
ParMODEL p = new ParMODEL();
p.name = "par" + (parModelList.Count + 1);
p.value = r.ToString().ToIntByStr();
parModelList.Add(p);
//return string.Format("'{0}'", ce.Value.ToString());
sbIntStr.Append("@par" + parModelList.Count + ",");
}
return sbIntStr.ToString().Substring(0, sbIntStr.ToString().Length - 1);
}
else if (result is string[])
{
var rl = result as string[];
StringBuilder sbIntStr = new StringBuilder();
foreach (var r in rl)
{
ParMODEL p = new ParMODEL();
p.name = "par" + (parModelList.Count + 1);
p.value = r.ToString();
parModelList.Add(p);
//return string.Format("'{0}'", ce.Value.ToString());
sbIntStr.Append("@par" + parModelList.Count + ",");
}
return sbIntStr.ToString().Substring(0, sbIntStr.ToString().Length - 1);
}
}
}
else if (exp is NewArrayExpression)
{
NewArrayExpression ae = ((NewArrayExpression)exp);
StringBuilder tmpstr = new StringBuilder();
foreach (Expression ex in ae.Expressions)
{
tmpstr.Append(ExpressionRouter(ex, parModelList));
tmpstr.Append(",");
}
//添加參數
return tmpstr.ToString(0, tmpstr.Length - 1);
}
else if (exp is MethodCallExpression)
{
MethodCallExpression mce = (MethodCallExpression)exp;
string par = ExpressionRouter(mce.Arguments[0], parModelList);
if (mce.Method.Name == "Like")
{
//添加參數用
return string.Format("({0} like {1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
}
else if (mce.Method.Name == "NotLike")
{
//添加參數用
return string.Format("({0} Not like {1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
}
else if (mce.Method.Name == "In")
{
//添加參數用
return string.Format("{0} In ({1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
}
else if (mce.Method.Name == "NotIn")
{
//添加參數用
return string.Format("{0} Not In ({1})", par, ExpressionRouter(mce.Arguments[1], parModelList));
}
}
else if (exp is ConstantExpression)
{
ConstantExpression ce = ((ConstantExpression)exp);
if (ce.Value == null)
{
return "null";
}
else if (ce.Value is ValueType)
{
ParMODEL p = new ParMODEL();
p.name = "par" + (parModelList.Count + 1);
p.value = ce.Value.ToString().ToIntByStr();
parModelList.Add(p);
//return ce.Value.ToString();
return "@par" + parModelList.Count;
}
else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
{
ParMODEL p = new ParMODEL();
p.name = "par" + (parModelList.Count + 1);
p.value = ce.Value.ToString();
parModelList.Add(p);
//return string.Format("'{0}'", ce.Value.ToString());
return "@par" + parModelList.Count;
}