public override DbExpression Visit(DbUpdateExpression exp) { this._sqlBuilder.Append("UPDATE "); this.AppendTable(exp.Table); this._sqlBuilder.Append(" SET "); bool first = true; foreach (var item in exp.UpdateColumns) { if (first) { first = false; } else { this._sqlBuilder.Append(","); } this.QuoteName(item.Key.Name); this._sqlBuilder.Append("="); DbExpression valExp = DbExpressionExtension.StripInvalidConvert(item.Value); AmendDbInfo(item.Key, valExp); valExp.Accept(this.ValueExpressionVisitor); } this.BuildWhereState(exp.Condition); return(exp); }
public override DbExpression Visit(DbConvertExpression exp) { DbExpression stripedExp = DbExpressionExtension.StripInvalidConvert(exp); if (stripedExp.NodeType != DbExpressionType.Convert) { stripedExp.Accept(this); return(exp); } exp = (DbConvertExpression)stripedExp; string dbTypeString; if (TryGetCastTargetDbTypeString(exp.Operand.Type, exp.Type, out dbTypeString, false)) { this.BuildCastState(exp.Operand, dbTypeString); } else { exp.Operand.Accept(this); } return(exp); }
/// <summary> /// 尝试将 exp 转换成 DbParameterExpression。 /// </summary> /// <param name="exp"></param> /// <returns></returns> public static DbExpression OptimizeDbExpression(this DbExpression exp) { DbExpression stripedExp = DbExpressionExtension.StripInvalidConvert(exp); DbExpression tempExp = stripedExp; List <DbConvertExpression> cList = null; while (tempExp.NodeType == DbExpressionType.Convert) { if (cList == null) { cList = new List <DbConvertExpression>(); } DbConvertExpression c = (DbConvertExpression)tempExp; cList.Add(c); tempExp = c.Operand; } if (tempExp.NodeType == DbExpressionType.Constant || tempExp.NodeType == DbExpressionType.Parameter) { return(stripedExp); } if (tempExp.NodeType == DbExpressionType.MemberAccess) { DbMemberExpression dbMemberExp = (DbMemberExpression)tempExp; if (ExistDateTime_NowOrDateTime_UtcNow(dbMemberExp)) { return(stripedExp); } DbParameterExpression val; if (DbExpressionExtension.TryConvertToParameterExpression(dbMemberExp, out val)) { if (cList != null) { if (val.Value == DBNull.Value)//如果是 null,则不需要 Convert 了,在数据库里没意义 { return(val); } DbConvertExpression c = null; for (int i = cList.Count - 1; i > -1; i--) { DbConvertExpression item = cList[i]; c = new DbConvertExpression(item.Type, val); } return(c); } return(val); } } return(stripedExp); }
public void Process(DbMethodCallExpression exp, SqlGenerator generator) { DbExpression left = exp.Arguments[0]; DbExpression right = exp.Arguments[1]; left = DbExpressionExtension.StripInvalidConvert(left); right = DbExpressionExtension.StripInvalidConvert(right); //明确 left right 其中一边一定为 null if (DbExpressionExtension.AffirmExpressionRetValueIsNull(right)) { left.Accept(generator); generator.SqlBuilder.Append(" IS NOT NULL"); return; } if (DbExpressionExtension.AffirmExpressionRetValueIsNull(left)) { right.Accept(generator); generator.SqlBuilder.Append(" IS NOT NULL"); return; } SqlGenerator.AmendDbInfo(left, right); left.Accept(generator); generator.SqlBuilder.Append(" <> "); right.Accept(generator); }
static void Method_Sql_Equals(DbMethodCallExpression exp, SqlGenerator generator) { DbExpression left = exp.Arguments[0]; DbExpression right = exp.Arguments[1]; left = DbExpressionExtension.StripInvalidConvert(left); right = DbExpressionExtension.StripInvalidConvert(right); //明确 left right 其中一边一定为 null if (DbExpressionHelper.AffirmExpressionRetValueIsNullOrEmpty(right)) { left.Accept(generator); generator.SqlBuilder.Append(" IS NULL"); return; } if (DbExpressionHelper.AffirmExpressionRetValueIsNullOrEmpty(left)) { right.Accept(generator); generator.SqlBuilder.Append(" IS NULL"); return; } SqlGenerator.AmendDbInfo(left, right); left.Accept(generator); generator.SqlBuilder.Append(" = "); right.Accept(generator); }
public override DbExpression Visit(DbEqualExpression exp) { DbExpression left = exp.Left; DbExpression right = exp.Right; left = DbExpressionExtension.StripInvalidConvert(left); right = DbExpressionExtension.StripInvalidConvert(right); MethodInfo method_Sql_Equals = PublicConstants.MethodInfo_Sql_Equals.MakeGenericMethod(left.Type); /* Sql.Equals(left, right) */ DbMethodCallExpression left_equals_right = DbExpression.MethodCall(null, method_Sql_Equals, new List <DbExpression>(2) { left, right }); if (right.NodeType == DbExpressionType.Parameter || right.NodeType == DbExpressionType.Constant || left.NodeType == DbExpressionType.Parameter || left.NodeType == DbExpressionType.Constant || right.NodeType == DbExpressionType.SubQuery || left.NodeType == DbExpressionType.SubQuery || !left.Type.CanNull() || !right.Type.CanNull()) { /* * a.Name == name --> a.Name == name * a.Id == (select top 1 T.Id from T) --> a.Id == (select top 1 T.Id from T) * 对于上述查询,我们不考虑 null */ left_equals_right.Accept(this); return(exp); } /* * a.Name == a.XName --> a.Name == a.XName or (a.Name is null and a.XName is null) */ /* Sql.Equals(left, null) */ var left_is_null = DbExpression.MethodCall(null, method_Sql_Equals, new List <DbExpression>(2) { left, DbExpression.Constant(null, left.Type) }); /* Sql.Equals(right, null) */ var right_is_null = DbExpression.MethodCall(null, method_Sql_Equals, new List <DbExpression>(2) { right, DbExpression.Constant(null, right.Type) }); /* Sql.Equals(left, null) && Sql.Equals(right, null) */ var left_is_null_and_right_is_null = DbExpression.And(left_is_null, right_is_null); /* Sql.Equals(left, right) || (Sql.Equals(left, null) && Sql.Equals(right, null)) */ var left_equals_right_or_left_is_null_and_right_is_null = DbExpression.Or(left_equals_right, left_is_null_and_right_is_null); left_equals_right_or_left_is_null_and_right_is_null.Accept(this); return(exp); }
public override DbExpression Visit(DbInsertExpression exp) { string separator = ""; this._sqlBuilder.Append("INSERT INTO "); this.AppendTable(exp.Table); this._sqlBuilder.Append("("); separator = ""; foreach (var item in exp.InsertColumns) { this._sqlBuilder.Append(separator); this.QuoteName(item.Key.Name); separator = ","; } this._sqlBuilder.Append(")"); if (exp.Returns.Count > 0) { this._sqlBuilder.Append(" output "); separator = ""; foreach (DbColumn returnColumn in exp.Returns) { this._sqlBuilder.Append(separator); this._sqlBuilder.Append("inserted."); this.QuoteName(returnColumn.Name); separator = ","; } } this._sqlBuilder.Append(" VALUES("); separator = ""; foreach (var item in exp.InsertColumns) { this._sqlBuilder.Append(separator); DbExpression valExp = DbExpressionExtension.StripInvalidConvert(item.Value); AmendDbInfo(item.Key, valExp); valExp.Accept(this.ValueExpressionVisitor); separator = ","; } this._sqlBuilder.Append(")"); return(exp); }
public override DbExpression Visit(DbConvertExpression exp) { DbExpression stripedExp = DbExpressionExtension.StripInvalidConvert(exp); if (stripedExp.NodeType != DbExpressionType.Convert) { EnsureDbExpressionReturnCSharpBoolean(stripedExp).Accept(this); return(exp); } exp = (DbConvertExpression)stripedExp; if (exp.Type == PublicConstants.TypeOfString) { this.SqlBuilder.Append("TO_CHAR("); exp.Operand.Accept(this); this.SqlBuilder.Append(")"); return(exp); } if (exp.Type == PublicConstants.TypeOfDateTime) { this.SqlBuilder.Append("TO_TIMESTAMP("); exp.Operand.Accept(this); this.SqlBuilder.Append(",'yyyy-mm-dd hh24:mi:ssxff')"); return(exp); } string dbTypeString; if (TryGetCastTargetDbTypeString(exp.Operand.Type, exp.Type, out dbTypeString, false)) { this.BuildCastState(EnsureDbExpressionReturnCSharpBoolean(exp.Operand), dbTypeString); } else { EnsureDbExpressionReturnCSharpBoolean(exp.Operand).Accept(this); } return(exp); }
public override DbExpression Visit(DbConvertExpression exp) { DbExpression stripedExp = DbExpressionExtension.StripInvalidConvert(exp); if (stripedExp.NodeType != DbExpressionType.Convert) { stripedExp.Accept(this); return(exp); } exp = (DbConvertExpression)stripedExp; string dbTypeString; if (TryGetCastTargetDbTypeString(exp.Operand.Type, exp.Type, out dbTypeString, false)) { this.BuildCastState(exp.Operand, dbTypeString); } else { Type targetType = ReflectionExtension.GetUnderlyingType(exp.Type); if (targetType == PublicConstants.TypeOfDateTime) { /* DATETIME('2016-08-06 09:01:24') */ this.SqlBuilder.Append("DATETIME("); exp.Operand.Accept(this); this.SqlBuilder.Append(")"); } else { exp.Operand.Accept(this); } } return(exp); }
public override DbExpression Visit(DbInsertExpression exp) { string separator = ""; this._sqlBuilder.Append("INSERT INTO "); this.AppendTable(exp.Table); this._sqlBuilder.Append("("); separator = ""; foreach (var item in exp.InsertColumns) { this._sqlBuilder.Append(separator); this.QuoteName(item.Key.Name); separator = ","; } this._sqlBuilder.Append(")"); this.AppendOutputClause(exp.Returns); this._sqlBuilder.Append(" VALUES("); separator = ""; foreach (var item in exp.InsertColumns) { this._sqlBuilder.Append(separator); DbExpression valExp = DbExpressionExtension.StripInvalidConvert(item.Value); AmendDbInfo(item.Key, valExp); DbValueExpressionTransformer.Transform(valExp).Accept(this); separator = ","; } this._sqlBuilder.Append(")"); return(exp); }
public override DbExpression Visit(DbNotEqualExpression exp) { DbExpression left = exp.Left; DbExpression right = exp.Right; left = DbExpressionExtension.StripInvalidConvert(left); right = DbExpressionExtension.StripInvalidConvert(right); MethodInfo method_Sql_NotEquals = UtilConstants.MethodInfo_Sql_NotEquals.MakeGenericMethod(left.Type); /* Sql.NotEquals(left, right) */ DbMethodCallExpression left_not_equals_right = DbExpression.MethodCall(null, method_Sql_NotEquals, new List <DbExpression>(2) { left, right }); //明确 left right 其中一边一定为 null if (DbExpressionExtension.AffirmExpressionRetValueIsNull(right) || DbExpressionExtension.AffirmExpressionRetValueIsNull(left)) { /* * a.Name != null --> a.Name != null */ left_not_equals_right.Accept(this); return(exp); } if (right.NodeType == DbExpressionType.SubQuery || left.NodeType == DbExpressionType.SubQuery) { /* * a.Id != (select top 1 T.Id from T) --> a.Id <> (select top 1 T.Id from T),对于这种查询,我们不考虑 null */ left_not_equals_right.Accept(this); return(exp); } MethodInfo method_Sql_Equals = UtilConstants.MethodInfo_Sql_Equals.MakeGenericMethod(left.Type); if (left.NodeType == DbExpressionType.Parameter || left.NodeType == DbExpressionType.Constant) { var t = right; right = left; left = t; } if (right.NodeType == DbExpressionType.Parameter || right.NodeType == DbExpressionType.Constant) { /* * 走到这说明 name 不可能为 null * a.Name != name --> a.Name <> name or a.Name is null */ if (left.NodeType != DbExpressionType.Parameter && left.NodeType != DbExpressionType.Constant) { /* * a.Name != name --> a.Name <> name or a.Name is null */ /* Sql.Equals(left, null) */ var left_is_null1 = DbExpression.MethodCall(null, method_Sql_Equals, new List <DbExpression>(2) { left, DbExpression.Constant(null, left.Type) }); /* Sql.NotEquals(left, right) || Sql.Equals(left, null) */ var left_not_equals_right_or_left_is_null = DbExpression.Or(left_not_equals_right, left_is_null1); left_not_equals_right_or_left_is_null.Accept(this); } else { /* * name != name1 --> name <> name,其中 name 和 name1 都为变量且都不可能为 null */ left_not_equals_right.Accept(this); } return(exp); } /* * a.Name != a.XName --> a.Name <> a.XName or (a.Name is null and a.XName is not null) or (a.Name is not null and a.XName is null) * ## a.Name != a.XName 不能翻译成:not (a.Name == a.XName or (a.Name is null and a.XName is null)),因为数据库里的 not 有时候并非真正意义上的“取反”! * 当 a.Name 或者 a.XName 其中一个字段有为 NULL,另一个字段有值时,会查不出此条数据 ## */ DbConstantExpression null_Constant = DbExpression.Constant(null, left.Type); /* Sql.Equals(left, null) */ var left_is_null = DbExpression.MethodCall(null, method_Sql_Equals, new List <DbExpression>(2) { left, null_Constant }); /* Sql.NotEquals(left, null) */ var left_is_not_null = DbExpression.MethodCall(null, method_Sql_NotEquals, new List <DbExpression>(2) { left, null_Constant }); /* Sql.Equals(right, null) */ var right_is_null = DbExpression.MethodCall(null, method_Sql_Equals, new List <DbExpression>(2) { right, null_Constant }); /* Sql.NotEquals(right, null) */ var right_is_not_null = DbExpression.MethodCall(null, method_Sql_NotEquals, new List <DbExpression>(2) { right, null_Constant }); /* Sql.Equals(left, null) && Sql.NotEquals(right, null) */ var left_is_null_and_right_is_not_null = DbExpression.And(left_is_null, right_is_not_null); /* Sql.NotEquals(left, null) && Sql.Equals(right, null) */ var left_is_not_null_and_right_is_null = DbExpression.And(left_is_not_null, right_is_null); /* (Sql.Equals(left, null) && Sql.NotEquals(right, null)) || (Sql.NotEquals(left, null) && Sql.Equals(right, null)) */ var left_is_null_and_right_is_not_null_or_left_is_not_null_and_right_is_null = DbExpression.Or(left_is_null_and_right_is_not_null, left_is_not_null_and_right_is_null); /* Sql.NotEquals(left, right) || (Sql.Equals(left, null) && Sql.NotEquals(right, null)) || (Sql.NotEquals(left, null) && Sql.Equals(right, null)) */ var e = DbExpression.Or(left_not_equals_right, left_is_null_and_right_is_not_null_or_left_is_not_null_and_right_is_null); e.Accept(this); return(exp); }