C#，傳入lambda表達式，轉化為where條件sql語句。 ...
我們在開發時，一般都會編寫自已的資料庫幫助類，如：DbHelper。DbHelper類中可能都有類擬有：一個根據條件查詢得到一個實體集的方法:
/// <summary>
/// 根據條件語句 ，查實體列表集
/// 調用：如Users實體：欄位：ID、Name
/// List<Users> list = GetListByWhereString <Users>(“ Id >0 and Name like ‘FengCodes%’ ”);
/// </summary>
/// <typeparam name="T">實體類（實體名與數據表的名相同）</typeparam>
/// <param name="strWhere">條件語句（不含where）</param>
/// <returns></returns>
public List<T> GetListByWhereString<T>(string strWhere) where T : class,new()
{
//根據T獲得數據表名
T t = new T();
string tableName= t.GetType().Name;
//接著拼接sql語句
string sql=string.format(“select * from {0} ”,tableName);
If(!string.IsNullOrEmpty(strWhere))
{
sql=sql+” where ”+strWhere;
}
//獲得sql語句後，就可下一步操作，略
// ……
}
實體類：
/// <summary>
///測試的實體類
/// </summary>
public class Users
{
public int Id{set;get;}
public string Name{set;get;}
}
至這裡，有人會想 strWhere查詢語句，又要自已手寫拼接條件sql，能不能方便一些的方法呢？如 ：lambda表達式。
/// <summary>
/// 根據條件語句 ，查實體列表集
/// 調用：User實體：欄位：ID、Name
/// List<Users> list = GetList<Users>(“ Id >0 and Name like ‘FengCodes%’ ”);
/// </summary>
/// <typeparam name="T">實體類（實體名與數據表的名相同）</typeparam>
/// <param name="strWhere">條件語句（不含where）</param>
/// <returns></returns>
public List<T> GetList<T>(Expression<Func<T, bool>> wherePredicate) where T : class,new()
{
//有個方法據 wherePredicate的lambda表達式轉為 strWhere拼接查詢條件
string strWhere=GetWhereByLambda(wherePredicate);
//調用上面方法返回
return GetListByWhereString<T>(strWhere);
｝
說到這裡,就要入主題：如何把lambda表達式轉為where條件sql? 如何實現 GetWhereByLambda方法？
參考了國外網站的一些方法後，結合本構想：可以重寫 ExpressionVisitor類來實現!
上代碼：
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
namespace LambdaToWhereSqlTest.Common
{
#region Expression
public abstract class ExpressionVisitor35
{
protected ExpressionVisitor35() { }
protected virtual Expression Visit(Expression exp)
{
if (exp == null)
return exp;
switch (exp.NodeType)
{
case ExpressionType.Negate:
case ExpressionType.NegateChecked:
case ExpressionType.Not:
case ExpressionType.Convert:
case ExpressionType.ConvertChecked:
case ExpressionType.ArrayLength:
case ExpressionType.Quote:
case ExpressionType.TypeAs:
return this.VisitUnary((UnaryExpression)exp);
case ExpressionType.Add:
case ExpressionType.AddChecked:
case ExpressionType.Subtract:
case ExpressionType.SubtractChecked:
case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked:
case ExpressionType.Divide:
case ExpressionType.Modulo:
case ExpressionType.And:
case ExpressionType.AndAlso:
case ExpressionType.Or:
case ExpressionType.OrElse:
case ExpressionType.LessThan:
case ExpressionType.LessThanOrEqual:
case ExpressionType.GreaterThan:
case ExpressionType.GreaterThanOrEqual:
case ExpressionType.Equal:
case ExpressionType.NotEqual:
case ExpressionType.Coalesce:
case ExpressionType.ArrayIndex:
case ExpressionType.RightShift:
case ExpressionType.LeftShift:
case ExpressionType.ExclusiveOr:
return this.VisitBinary((BinaryExpression)exp);
case ExpressionType.TypeIs:
return this.VisitTypeIs((TypeBinaryExpression)exp);
case ExpressionType.Conditional:
return this.VisitConditional((ConditionalExpression)exp);
case ExpressionType.Constant:
return this.VisitConstant((ConstantExpression)exp);
case ExpressionType.Parameter:
return this.VisitParameter((ParameterExpression)exp);
case ExpressionType.MemberAccess:
return this.VisitMemberAccess((MemberExpression)exp);
case ExpressionType.Call:
return this.VisitMethodCall((MethodCallExpression)exp);
case ExpressionType.Lambda:
return this.VisitLambda((LambdaExpression)exp);
case ExpressionType.New:
return this.VisitNew((NewExpression)exp);
case ExpressionType.NewArrayInit:
case ExpressionType.NewArrayBounds:
return this.VisitNewArray((NewArrayExpression)exp);
case ExpressionType.Invoke:
return this.VisitInvocation((InvocationExpression)exp);
case ExpressionType.MemberInit:
return this.VisitMemberInit((MemberInitExpression)exp);
case ExpressionType.ListInit:
return this.VisitListInit((ListInitExpression)exp);
default:
throw new Exception(string.Format("Unhandled expression type: '{0}'", exp.NodeType));
}
}
protected virtual MemberBinding VisitBinding(MemberBinding binding)
{
switch (binding.BindingType)
{
case MemberBindingType.Assignment:
return this.VisitMemberAssignment((MemberAssignment)binding);
case MemberBindingType.MemberBinding:
return this.VisitMemberMemberBinding((MemberMemberBinding)binding);
case MemberBindingType.ListBinding:
return this.VisitMemberListBinding((MemberListBinding)binding);
default:
throw new Exception(string.Format("Unhandled binding type '{0}'", binding.BindingType));
}
}
protected virtual ElementInit VisitElementInitializer(ElementInit initializer)
{
ReadOnlyCollection<Expression> arguments = this.VisitExpressionList(initializer.Arguments);
if (arguments != initializer.Arguments)
{
return Expression.ElementInit(initializer.AddMethod, arguments);
}
return initializer;
}
protected virtual Expression VisitUnary(UnaryExpression u)
{
Expression operand = this.Visit(u.Operand);
if (operand != u.Operand)
{
return Expression.MakeUnary(u.NodeType, operand, u.Type, u.Method);
}
return u;
}
protected virtual Expression VisitBinary(BinaryExpression b)
{
Expression left = this.Visit(b.Left);
Expression right = this.Visit(b.Right);
Expression conversion = this.Visit(b.Conversion);
if (left != b.Left || right != b.Right || conversion != b.Conversion)
{
if (b.NodeType == ExpressionType.Coalesce && b.Conversion != null)
return Expression.Coalesce(left, right, conversion as LambdaExpression);
else
return Expression.MakeBinary(b.NodeType, left, right, b.IsLiftedToNull, b.Method);
}
return b;
}
protected virtual Expression VisitTypeIs(TypeBinaryExpression b)
{
Expression expr = this.Visit(b.Expression);
if (expr != b.Expression)
{
return Expression.TypeIs(expr, b.TypeOperand);
}
return b;
}
protected virtual Expression VisitConstant(ConstantExpression c)
{
return c;
}
protected virtual Expression VisitConditional(ConditionalExpression c)
{
Expression test = this.Visit(c.Test);
Expression ifTrue = this.Visit(c.IfTrue);
Expression ifFalse = this.Visit(c.IfFalse);
if (test != c.Test || ifTrue != c.IfTrue || ifFalse != c.IfFalse)
{
return Expression.Condition(test, ifTrue, ifFalse);
}
return c;
}
protected virtual Expression VisitParameter(ParameterExpression p)
{
return p;
}
protected virtual Expression VisitMemberAccess(MemberExpression m)
{
Expression exp = this.Visit(m.Expression);
if (exp != m.Expression)
{
return Expression.MakeMemberAccess(exp, m.Member);
}
return m;
}
protected virtual Expression VisitMethodCall(MethodCallExpression m)
{
//MethodCallExpression mce = m;
//if (mce.Method.Name == "Like")
// return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
//else if (mce.Method.Name == "NotLike")
// return string.Format("({0} Not like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
//else if (mce.Method.Name == "In")
// return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
//else if (mce.Method.Name == "NotIn")
// return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
//MethodCallExpression mce = m;
Expression obj = this.Visit(m.Object);
IEnumerable<Expression> args = this.VisitExpressionList(m.Arguments);
if (obj != m.Object || args != m.Arguments)
{
return Expression.Call(obj, m.Method, args);
}
return m;
}
protected virtual ReadOnlyCollection<Expression> VisitExpressionList(ReadOnlyCollection<Expression> original)
{
List<Expression> list = null;
for (int i = 0, n = original.Count; i < n; i++)
{
Expression p = this.Visit(original[i]);
if (list != null)
{
list.Add(p);
}
else if (p != original[i])
{
list = new List<Expression>(n);
for (int j = 0; j < i; j++)
{
list.Add(original[j]);
}
list.Add(p);
}
}
if (list != null)
{
return list.AsReadOnly();
}
return original;
}
protected virtual MemberAssignment VisitMemberAssignment(MemberAssignment assignment)
{
Expression e = this.Visit(assignment.Expression);
if (e != assignment.Expression)
{
return Expression.Bind(assignment.Member, e);
}
return assignment;
}
protected virtual MemberMemberBinding VisitMemberMemberBinding(MemberMemberBinding binding)
{
IEnumerable<MemberBinding> bindings = this.VisitBindingList(binding.Bindings);
if (bindings != binding.Bindings)
{
return Expression.MemberBind(binding.Member, bindings);
}
return binding;
}
protected virtual MemberListBinding VisitMemberListBinding(MemberListBinding binding)
{
IEnumerable<ElementInit> initializers = this.VisitElementInitializerList(binding.Initializers);
if (initializers != binding.Initializers)
{
return Expression.ListBind(binding.Member, initializers);
}
return binding;
}
protected virtual IEnumerable<MemberBinding> VisitBindingList(ReadOnlyCollection<MemberBinding> original)
{
List<MemberBinding> list = null;
for (int i = 0, n = original.Count; i < n; i++)
{
MemberBinding b = this.VisitBinding(original[i]);
if (list != null)
{
list.Add(b);
}
else if (b != original[i])
{
list = new List<MemberBinding>(n);
for (int j = 0; j < i; j++)
{
list.Add(original[j]);
}
list.Add(b);
}
}
if (list != null)
return list;
return original;
}
protected virtual IEnumerable<ElementInit> VisitElementInitializerList(ReadOnlyCollection<ElementInit> original)
{
List<ElementInit> list = null;
for (int i = 0, n = original.Count; i < n; i++)
{
ElementInit init = this.VisitElementInitializer(original[i]);
if (list != null)
{
list.Add(init);
}
else if (init != original[i])
{
list = new List<ElementInit>(n);
for (int j = 0; j < i; j++)
{
list.Add(original[j]);
}
list.Add(init);
}
}
if (list != null)
return list;
return original;
}
protected virtual Expression VisitLambda(LambdaExpression lambda)
{
Expression body = this.Visit(lambda.Body);
if (body != lambda.Body)
{
return Expression.Lambda(lambda.Type, body, lambda.Parameters);
}
return lambda;
}
protected virtual NewExpression VisitNew(NewExpression nex)
{
IEnumerable<Expression> args = this.VisitExpressionList(nex.Arguments);
if (args != nex.Arguments)
{
if (nex.Members != null)
return Expression.New(nex.Constructor, args, nex.Members);
else
return Expression.New(nex.Constructor, args);
}
return nex;
}
protected virtual Expression VisitMemberInit(MemberInitExpression init)
{
NewExpression n = this.VisitNew(init.NewExpression);
IEnumerable<MemberBinding> bindings = this.VisitBindingList(init.Bindings);
if (n != init.NewExpression || bindings != init.Bindings)
{
return Expression.MemberInit(n, bindings);
}
return init;
}
protected virtual Expression VisitListInit(ListInitExpression init)
{
NewExpression n = this.VisitNew(init.NewExpression);
IEnumerable<ElementInit> initializers = this.VisitElementInitializerList(init.Initializers);
if (n != init.NewExpression || initializers != init.Initializers)
{
return Expression.ListInit(n, initializers);
}
return init;
}
protected virtual Expression VisitNewArray(NewArrayExpression na)
{
IEnumerable<Expression> exprs = this.VisitExpressionList(na.Expressions);
if (exprs != na.Expressions)
{
if (na.NodeType == ExpressionType.NewArrayInit)
{
return Expression.NewArrayInit(na.Type.GetElementType(), exprs);
}
else
{
return Expression.NewArrayBounds(na.Type.GetElementType(), exprs);
}
}
return na;
}
protected virtual Expression VisitInvocation(InvocationExpression iv)
{
IEnumerable<Expression> args = this.VisitExpressionList(iv.Arguments);
Expression expr = this.Visit(iv.Expression);
if (args != iv.Arguments || expr != iv.Expression)
{
return Expression.Invoke(expr, args);
}
return iv;
}
}
public class PartialEvaluator : ExpressionVisitor35
{
private Func<Expression, bool> m_fnCanBeEvaluated;
private HashSet<Expression> m_candidates;
public PartialEvaluator()
: this(CanBeEvaluatedLocally)
{ }
public PartialEvaluator(Func<Expression, bool> fnCanBeEvaluated)
{
this.m_fnCanBeEvaluated = fnCanBeEvaluated;
}
public Expression Eval(Expression exp)
{
this.m_candidates = new Nominator(this.m_fnCanBeEvaluated).Nominate(exp);
return this.Visit(exp);
}
protected override Expression Visit(Expression exp)
{
if (exp == null)
{
return null;
}
if (this.m_candidates.Contains(exp))
{
return this.Evaluate(exp);
}
return base.Visit(exp);
}
private Expression Evaluate(Expression e)
{
if (e.NodeType == ExpressionType.Constant)
{
return e;
}
LambdaExpression lambda = Expression.Lambda(e);
Delegate fn = lambda.Compile();
return Expression.Constant(fn.DynamicInvoke(null), e.Type);
}
private static bool CanBeEvaluatedLocally(Expression exp)
{
return exp.NodeType != ExpressionType.Parameter;
}
#region Nominator
/// <summary>
/// Performs bottom-up analysis to determine which nodes can possibly
/// be part of an evaluated sub-tree.
/// </summary>
private class Nominator : ExpressionVisitor35
{
private Func<Expression, bool> m_fnCanBeEvaluated;
private HashSet<Expression> m_candidates;
private bool m_cannotBeEvaluated;
internal Nominator(Func<Expression, bool> fnCanBeEvaluated)
{
this.m_fnCanBeEvaluated = fnCanBeEvaluated;
}
internal HashSet<Expression> Nominate(Expression expression)
{
this.m_candidates = new HashSet<Expression>();
this.Visit(expression);
return this.m_candidates;
}
protected override Expression Visit(Expression expression)
{
if (expression != null)
{
bool saveCannotBeEvaluated = this.m_cannotBeEvaluated;
this.m_cannotBeEvaluated = false;
base.Visit(expression);
if (!this.m_cannotBeEvaluated)
{
if (this.m_fnCanBeEvaluated(expression))
{
this.m_candidates.Add(expression);
}
else
{
this.m_cannotBeEvaluated = true;
}
}
this.m_cannotBeEvaluated |= saveCannotBeEvaluated;
}
return expression;
}
}
#endregion
}
internal class ConditionBuilder : ExpressionVisitor35
{
/// <summary>
/// 資料庫類型
/// </summary>
private string m_dataBaseType = "";
/// <summary>
/// 欄位是否加引號
/// </summary>
private bool m_ifWithQuotationMarks = false;
private List<object> m_arguments;
private Stack<string> m_conditionParts;
public string Condition { get; private set; }
public object[] Arguments { get; private set; }
#region 加雙引號
/// <summary>
/// 加雙引號
/// </summary>
/// <param name="str">字串</param>
/// <returns></returns>
public static string AddQuotationMarks(string str)
{
if (str != null)
{
return "\"" + str.Trim() + "\"";
}
else
{
return str;
}
}
#endregion
#region 設置是否加雙引號
public void SetIfWithQuotationMarks(bool ifwith)
{
m_ifWithQuotationMarks = ifwith;
}
#endregion
#region 設置是資料庫類型
public void SetDataBaseType(string databaseType)
{
if(!string.IsNullOrEmpty(databaseType))
{
m_dataBaseType = databaseType;
}
}
#endregion
public void Build(Expression expression)
{
PartialEvaluator evaluator = new PartialEvaluator();
Expression evaluatedExpression = evaluator.Eval(expression);
this.m_arguments = new List<object>();
this.m_conditionParts = new Stack<string>();
this.Visit(evaluatedExpression);
this.Arguments = this.m_arguments.ToArray();
this.Condition = this.m_conditionParts.Count > 0 ? this.m_conditionParts.Pop() : null;
}
protected override Expression VisitBinary(BinaryExpression b)
{
if (b == null) return b;
string opr;
switch (b.NodeType)
{
case ExpressionType.Equal:
opr = "=";
break;
case ExpressionType.NotEqual:
opr = "<>";
break;
case ExpressionType.GreaterThan:
opr = ">";
break;
case ExpressionType.GreaterThanOrEqual:
opr = ">=";
break;
case ExpressionType.LessThan:
opr = "<";
break;
case ExpressionType.LessThanOrEqual:
opr = "<=";
break;
case ExpressionType.AndAlso:
opr = "AND";
break;
case ExpressionType.OrElse:
opr = "OR";
break;
case ExpressionType.Add:
opr = "+";
break;
case ExpressionType.Subtract:
opr = "-";
break;
case ExpressionType.Multiply:
opr = "*";
break;
case ExpressionType.Divide:
opr = "/";
break;
default:
throw new NotSupportedException(b.NodeType + "is not supported.");
}
this.Visit(b.Left);
this.Visit(b.Right);
string right = this.m_conditionParts.Pop();
string left = this.m_conditionParts.Pop();
string condition = String.Format("({0} {1} {2})", left, opr, right);
this.m_conditionParts.Push(condition);
return b;
}
protected override Expression VisitConstant(ConstantExpression c)
{
if (c == null) return c;
this.m_arguments.Add(c.Value);
this.m_conditionParts.Push(String.Format("{{{0}}}", this.m_arguments.Count - 1));
return c;
}
protected override Expression VisitMemberAccess(MemberExpression m)
{
if (m == null) return m;
PropertyInfo propertyInfo = m.Member as PropertyInfo;
if (propertyInfo == null) return m;
// this.m_conditionParts.Push(String.Format("(Where.{0})", propertyInfo.Name));
//是否添加引號
if (m_ifWithQuotationMarks)
{
this.m_conditionParts.Push(String.Format(" {0} ",AddQuotationMarks( propertyInfo.Name)));
}
else
{
// this.m_conditionParts.Push(String.Format("[{0}]", propertyInfo.Name));
this.m_conditionParts.Push(String.Format(" {0} ", propertyInfo.Name));
}
return m;
}
#region 其他
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 - 1) + " is null";
else if (sb.EndsWith("<>"))
sb = sb.Substring(0, sb.Length - 1) + " is not null";
}
else
sb += tmpStr;
return sb += ")";
}
static string ExpressionRouter(Expression exp)
{
string sb = string.Empty;
if (exp is BinaryExpression)
{
BinaryExpression be = ((BinaryExpression)exp);
return BinarExpressionProvider(be.Left, be.Right, be.NodeType);
}
else if (exp is MemberExpression)
{
MemberExpression me = ((MemberExpression)exp);
return me.Member.Name;
}
else if (exp is NewArrayExpression)
{
NewArrayExpression ae = ((NewArrayExpression)exp);
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)
{
MethodCallExpression mce = (MethodCallExpression)exp;
if (mce.Method.Name == "Like")
return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
else if (mce.Method.Name == "NotLike")
return string.Format("({0} Not like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
else if (mce.Method.Name == "In")
return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
else if (mce.Method.Name == "NotIn")
return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
else if (mce.Method.Name == "StartWith")
return string.Format("{0} like '{1}%'", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
}
else if (exp is ConstantExpression)
{
ConstantExpression ce = ((ConstantExpression)exp);
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;
}
static string ExpressionTypeCast(ExpressionType type)
{
switch (type)
{
case ExpressionType.And:
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:
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;
}
}
#endregion
/// <summary>
/// ConditionBuilder 並不支持生成Like操作，如 字元串的 StartsWith，Contains，EndsWith 並不能生成這樣的SQL： Like ‘xxx%’, Like ‘%xxx%’ , Like ‘%xxx’ . 只要override VisitMethodCall 這個方法即可實現上述功能。
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
protected override Expression VisitMethodCall(MethodCallExpression m)
{
string connectorWords = GetLikeConnectorWords(m_dataBaseType); //獲取like鏈接符
if (m == null) return m;
string format;
switch (m.Method.Name)
{
case "StartsWith":
format = "({0} LIKE ''"+ connectorWords+ "{1}"+ connectorWords+"'%')";
break;
case "Contains":
format = "({0} LIKE '%'" + connectorWords + "{1}" + connectorWords + "'%')";
break;
case "EndsWith":
format = "({0} LIKE '%'" + connectorWords + "{1}" + connectorWords + "'')";
break;
case "Equals":
// not in 或者 in 或 not like
format = "({0} {1} )";
break;
default:
throw new NotSupportedException(m.NodeType + " is not supported!");
}
this.Visit(m.Object);
this.Visit(m.Arguments[0]);
string right = this.m_conditionParts.Pop();
string left = this.m_conditionParts.Pop();
this.m_conditionParts.Push(String.Format(format, left, right));
return m;
}
/// <summary>
/// 獲得like語句鏈接符
/// </summary>
/// <param name="databaseType"></param>
/// <returns></returns>
public static string GetLikeConnectorWords(string databaseType)
{
string result = "+";
switch (databaseType.ToLower())
{
case DataBaseType.PostGreSql:
case DataBaseType.Oracle:
case DataBaseType.MySql:
case DataBaseType.Sqlite:
result = "||";
break;
}
return result;
}
}
#endregion
}
轉化類 方法：
sing System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
namespace LambdaToWhereSqlTest.Common
{
/// <summary>
/// lambda表達式轉為where條件sql
/// </summary>
public class SqlSugor
{
#region Expression 轉成 where
/// <summary>
/// Expression 轉成 Where String
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <param name="databaseType">數據類型（用於欄位是否加引號）</param>
/// <returns></returns>
public static string GetWhereByLambda<T>(Expression<Func<T, bool>> predicate, string databaseType)
{
bool withQuotationMarks = GetWithQuotationMarks(databaseType);
ConditionBuilder conditionBuilder = new ConditionBuilder();
conditionBuilder.SetIfWithQuotationMarks(withQuotationMarks); //欄位是否加引號（PostGreSql,Oracle）
conditionBuilder.SetDataBaseType(databaseType);
conditionBuilder.Build(predicate);
for (int i = 0; i < conditionBuilder.Arguments.Length; i++)
{
object ce = conditionBuilder.Arguments[i];
if (ce == null)
{
conditionBuilder.Arguments[i] = DBNull.Value;
}
else if (ce is string || ce is char)
{
if (ce.ToString().ToLower().Trim().IndexOf(@"in(") == 0 ||
ce.ToString().ToLower().Trim().IndexOf(@"not in(") == 0 ||
ce.ToString().ToLower().Trim().IndexOf(@" like '") == 0 ||
ce.ToString().ToLower().Trim().IndexOf(@"not like") == 0)
{
conditionBuilder.Arguments[i] = string.Format(" {0} ", ce.ToString());
}
else
{
//****************************************
conditionBuilder.Arguments[i] = string.Format("'{0}'", ce.ToString());
}
}
else if (ce is DateTime)
{
conditionBuilder.Arguments[i] = string.Format("'{0}'", ce.ToString());
}
else if (ce is int || ce is long || ce is short || ce is decimal || ce is double || ce is float || ce is bool || ce is byte || ce is sbyte)
{
conditionBuilder.Arguments[i] = ce.ToString();
}
else if (ce is ValueType)
{
conditionBuilder.Arguments[i] = ce.ToString();
}
else
{
conditionBuilder.Arguments[i] = string.Format("'{0}'", ce.ToString());
}
}
string strWhere = string.Format(conditionBuilder.Condition, conditionBuilder.Arguments);
return strWhere;
}
/// <summary>
/// 獲取是否欄位加雙引號
/// </summary>
/// <param name="databaseType"></param>
/// <returns></returns>
public static bool GetWithQuotationMarks(string databaseType)
{
bool result = false;
switch (databaseType.ToLower())
{
case DataBaseType.PostGreSql:
case DataBaseType.Oracle:
result = true;
break;
}
return result;
}
#endregion
}
}
資料庫類型:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LambdaToWhereSqlTest.Common
{
#region 資料庫類型
/// <summary>
///類據庫類型
/// </summary>
public class DataBaseType
{
/// <summary>
/// sql server
/// </summary>
public const string SqlServer = "sqlserver";
/// <summary>
/// OleDb
/// </summary>
public const string Access = "access";
/// <summary>
///MySql
/// </summary>
public const string MySql = "mysql";
/// <summary>
/// Oracle
/// </summary>
public const string Oracle = "oracle";
/// <summary>
/// Postgresql
/// </summary>
public const string PostGreSql = "postgresql";
/// <summary>
/// Sqlite
/// </summary>
public const string Sqlite = "sqlite";
}
#endregion
}
註：
Lambda表式中的
StartsWith表示 like ‘字串%’ 如： SqlSugor.GetWhereByLambda<Users>(x=>x. StartsWith(“test”)&&(x.Id>2))
Contains表示 like ‘%字串%’ 如： SqlSugor.GetWhereByLambda<Users>(x=>x. Contains (“test”) ||x.Id==2)
EndsWith表示 like ‘字串%’ 如：SqlSugor.GetWhereByLambda<Users>( x=>x. Contains (“test”) &&(x.Id>4||x.Id==2));
建一個測試項目來演示：【LambdaToWhereSqlTest】
選擇資料庫類型，然後按生成按鍵
//生成按鍵事件:
private void btnCreate_Click(object sender, EventArgs e)
{
string dataType = cbxDataType.SelectedItem.ToString();
string sql1 = SqlSugor.GetWhereByLambda<Users>(x => x.Name.StartsWith("test") && x.Id > 2, dataType);
string sql2 = SqlSugor.GetWhereByLambda<Users>(x => x.Name.EndsWith("test") && (x.Id >4||x.Id==3), dataType);
string sql3 = SqlSugor.GetWhereByLambda<Users>(x => x.Name.Contains("test") && (x.Id > 4 && x.Id <=8), dataType);
string sql4 = SqlSugor.GetWhereByLambda<Users>(x => x.Name=="FengCode" && (x.Id >=1), dataType);
richTextBoxWhere.Text = sql1 + "\n\r" + sql2 + "\n\r" + sql3 + "\n\r" + sql4;
}
winform代碼：
using LambdaToWhereSqlTest.Common;
using LambdaToWhereSqlTest.Model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace LambdaToWhereSqlTest
{
public partial class Form1 : Form
{
private System.Windows.Forms.Button btnCreate;
private System.Windows.Forms.ComboBox cbxDataType;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.RichTextBox richTextBoxWhere;
#region Windows 窗體設計器生成的代碼
/// <summary>
/// 設計器支持所需的方法 - 不要修改
/// 使用代碼編輯器修改此方法的內容。
/// </summary>
private void InitializeComponent()
{
this.btnCreate = new System.Windows.Forms.Button();
this.cbxDataType = new System.Windows.Forms.ComboBox();
this.label1 = new System.Windows.Forms.Label();
this.richTextBoxWhere = new System.Windows.Forms.RichTextBox();
this.SuspendLayout();
//
// btnCreate
//
this.btnCreate.Location = new System.Drawing.Point(510, 31);
this.btnCreate.Name = "btnCreate";
this.btnCreate.Size = new System.Drawing.Size(201, 23);
this.btnCreate.TabIndex = 0;
this.btnCreate.Text = "生成";
this.btnCreate.UseVisualStyleBackColor = true;
this.btnCreate.Click += new System.EventHandler(this.btnCreate_Click);
//
// cbxDataType
//
this.cbxDataType.FormattingEnabled = true;
this.cbxDataType.Items.AddRange(new object[] {
"SqlServer"});
this.cbxDataType.Location = new System.Drawing.Point(130, 34);
this.cbxDataType.Name = "cbxDataType";
this.cbxDataType.Size = new System.Drawing.Size(374, 20);
this.cbxDataType.TabIndex = 1;
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(12, 37);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(101, 12);
this.label1.TabIndex = 2;
this.label1.Text = "選取資料庫類型：";
//
// richTextBoxWhere
//
this.richTextBoxWhere.Location = new System.Drawing.Point(14, 78);
this.richTextBoxWhere.Name = "richTextBoxWhere";
this.richTextBoxWhere.Size = new System.Drawing.Size(697, 221);
this.richTextBoxWhere.TabIndex = 3;
this.richTextBoxWhere.Text = "";
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(764, 346);
this.Controls.Add(this.richTextBoxWhere);
this.Controls.Add(this.label1);
this.Controls.Add(this.cbxDataType);
this.Controls.Add(this.btnCreate);
this.Name = "Form1";
this.Text = "C#--lambda表達式轉為where條件sql字串的方法;如有問題，可以通過[email protected] 聯繫我，非常感謝";
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
public class ComboxItem
{
public string Text = "";
public string Value = "";
public ComboxItem(string _Text, string _Value)
{
Text = _Text;
Value = _Value;
}
public override string ToString()
{
return Text;
}
}
public Form1()
{
InitializeComponent();
//初始化
Init();
}
/// <summary>
/// 初始化選擇數據類型
/// </summary>
private void Init()
{
//初始化選擇數據類型
cbxDataType.Items.Add(new ComboxItem( DataBaseType.SqlServer, DataBaseType.SqlServer) );
cbxDataType.Items.Add(new ComboxItem(DataBaseType.Access, DataBaseType.Access));
cbxDataType.Items.Add(new ComboxItem(DataBaseType.MySql, DataBaseType.MySql));
cbxDataType.Items.Add(new ComboxItem(DataBaseType.Oracle, DataBaseType.Oracle));
cbxDataType.Items.Add(new ComboxItem(DataBaseType.PostGreSql, DataBaseType.PostGreSql));
cbxDataType.Items.Add(new ComboxItem(DataBaseType.Sqlite, DataBaseType.Sqlite));
cbxDataType.SelectedIndex = 0;
}
//生成按鍵事件:
private void btnCreate_Click(object sender, EventArgs e)
{
string dataType = cbxDataType.SelectedItem.ToString();
string sql1 = SqlSugor.GetWhereByLambda<Users>(x => x.Name.StartsWith("test") && x.Id > 2, dataType);
string sql2 = SqlSugor.GetWhereByLambda<Users>(x => x.Name.EndsWith("test") && (x.Id >4||x.Id==3), dataType);
string sql3 = SqlSugor.GetWhereByLambda<Users>(x => x.Name.Contains("test") && (x.Id > 4 && x.Id <=8), dataType);
string sql4 = SqlSugor.GetWhereByLambda<Users>(x => x.Name=="FengCode" && (x.Id >=1), dataType);
richTextBoxWhere.Text = sql1 + "\n\r" + sql2 + "\n\r" + sql3 + "\n\r" + sql4;
}
private void Form1_Load(object sender, EventArgs e)
{
}
}
}
至此，實現lambda表達式轉為where條件sql字串的方法！
版權聲明：
本文由FengCodes原創併發布於博客園，歡迎轉載，未經本人同意必須保留此段聲明，且在文章頁面明顯位置給出原文鏈接 ，否則保留追究法律責任的權利。如有問題，可以通過[email protected] 聯繫我，非常感謝。
更多內容，敬請觀註博客：http://www.cnblogs.com/FengCodes/
