// 增加一个参数 protected override IDbDataParameter AddParameter(object value, ResolveToken token, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null) { // 补充 DbType MySqlParameter parameter = (MySqlParameter)base.AddParameter(value, token, dbType, size, precision, scale, direction); parameter.DbType(dbType); return(parameter); }
/// <summary> /// 访问 IDbQueryable.Contains 方法 /// </summary> /// <param name="m">方法表达式</param> protected override Expression VisitQueryableContains(MethodCallExpression m) { ResolveToken token = _builder.Token; var subQuery = m.Arguments[0].Evaluate().Value as IDbQueryable; subQuery.Parameterized = _builder.Parameterized; bool isDelete = token != null && token.Extendsions != null && token.Extendsions.ContainsKey("MySqlDelete"); var cmd = subQuery.Resolve(_builder.Indent + 1, false, new ResolveToken { Parameterized = token.Parameterized, Parameters = token.Parameters, AliasPrefix = "s", DbContext = token.DbContext }) as MappingCommand; if (this.NotOperands != null && this.NotOperands.Contains(m)) { _builder.Append("NOT "); } _builder.Append("EXISTS("); if (isDelete) { _builder.Append("SELECT 1 FROM("); _builder.Append(cmd.CommandText); _builder.Append(") s0 WHERE "); _builder.Append(cmd.PickColumnText); _builder.Append(" = "); _visitor.Visit(m.Arguments[1]); _builder.Append(")"); } else { _builder.Append(cmd.CommandText); if (cmd.WhereFragment.Length > 0) { _builder.Append(" AND "); } else { _builder.Append("WHERE "); } _builder.Append(cmd.PickColumnText); _builder.Append(" = "); _visitor.Visit(m.Arguments[1]); _builder.Append(")"); } return(m); }
// 增加一个参数 protected override IDbDataParameter AddParameter(object value, ResolveToken token, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null) { #if !netcore if (value is TimeSpan) { // 如果不是 netcore,需要将timespan转为datetime类型才可以保存 value = new DateTime(((TimeSpan)value).Ticks); dbType = NpgsqlDbType.Timestamp; } #endif // 补充 DbType NpgsqlParameter parameter = (NpgsqlParameter)base.AddParameter(value, token, dbType, size, precision, scale, direction); parameter.DbType(dbType); return(parameter); }
/// <summary> /// 访问 IDbQueryable.Contains 方法 /// </summary> /// <param name="m">字段或属性表达式</param> protected override Expression VisitQueryableContains(MethodCallExpression m) { ResolveToken token = _builder.Token; IDbQueryable subQuery = m.Arguments[0].Evaluate().Value as IDbQueryable; subQuery.Parameterized = _builder.Parameterized; var cmd = subQuery.Resolve(_builder.Indent + 1, false, token != null ? new ResolveToken { Parameterized = token.Parameterized, Parameters = token.Parameters, AliasPrefix = "s", DbContext = token.DbContext } : null) as MappingCommand; if (this.NotOperands != null && this.NotOperands.Contains(m)) { _builder.Append("NOT "); } _builder.Append("EXISTS("); _builder.Append(cmd.CommandText); if (((MappingCommand)cmd).WhereFragment.Length > 0) { _builder.Append(" AND "); } else { _builder.Append("WHERE "); } _builder.Append(cmd.PickColumnText); _builder.Append(" = "); // exists 不能用别名 if (token != null && token.Extendsions != null && token.Extendsions.ContainsKey("SQLiteDelete")) { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(((MemberExpression)m.Arguments[1]).Expression.Type); _builder.AppendMember(typeRuntime.TableName); _builder.Append('.'); } _visitor.Visit(m.Arguments[1]); _builder.Append(")"); return(m); }
/// <summary> /// 实例化 <see cref="OracleSqlBuilder"/> 类的新实例 /// </summary> /// <param name="provider">提供者</param> /// <param name="token">参数列表,NULL 或者 Parameters=NULL 时表示不使用参数化</param> public OracleSqlBuilder(IDbQueryProvider provider, ResolveToken token) : base(provider, token) { }
// 创建 UPDATE 命令 protected override Command ResolveUpdateCommand <T>(DbQueryableInfo_Update <T> dbQuery, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("UPDATE "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" t0 SET"); builder.AppendNewLine(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder whereBuilder = this.CreateSqlBuilder(token); bool useKey = false; int length = 0; foreach (var invoker in typeRuntime.Invokers) { var column = invoker.Column; if (column != null && column.IsIdentity) { goto gotoLabel; // fix issue# 自增列同时又是主键 } if (column != null && column.NoMapped) { continue; } if (invoker.ForeignKey != null) { continue; } if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } builder.AppendMember(invoker.Member.Name); builder.Append(" = "); gotoLabel: var value = invoker.Invoke(entity); var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); if (column == null || !column.IsIdentity) { builder.Append(seg); length = builder.Length; builder.Append(','); builder.AppendNewLine(); } if (column != null && column.IsKey) { useKey = true; whereBuilder.AppendMember(invoker.Member.Name); whereBuilder.Append(" = "); whereBuilder.Append(seg); whereBuilder.Append(" AND "); } } if (!useKey) { throw new XFrameworkException("Update<T>(T value) require T must have key column."); } builder.Length = length; whereBuilder.Length -= 5; builder.AppendNewLine(); builder.Append("WHERE "); builder.Append(whereBuilder); } else if (dbQuery.Expression != null) { TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery.SelectInfo, token); ExpressionVisitorBase visitor = null; visitor = new NpgUpdateExpressionVisitor(this, aliases, dbQuery.Expression); visitor.Write(builder); var cmd2 = new NpgMapperCommand(this, aliases, DbExpressionType.Update, token) { HasMany = dbQuery.SelectInfo.HasMany }; var visitor0 = new NpgJoinExpressionVisitor(this, aliases, dbQuery.SelectInfo.Joins, DbExpressionType.Update); visitor0.Write(cmd2); var visitor1 = new NpgWhereExpressionVisitor(this, aliases, dbQuery.SelectInfo.Condtion); visitor1.Write(cmd2.WhereFragment); cmd2.AddNavMembers(visitor1.NavMembers); builder.Append(cmd2.CommandText); } builder.Append(';'); return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
/// <summary> /// 实例化 <see cref="SqlServerMapperCommand"/> 类的新实例 /// </summary> /// <param name="context">数据查询提供者</param> /// <param name="aliases">别名</param> /// <param name="token">解析上下文参数</param> public SqlServerMapperCommand(SqlServerDbContext context, TableAliasCache aliases, ResolveToken token) : base(context.Provider, aliases, token) { _aliases = aliases; _context = context; _provider = context.Provider as SqlServerDbQueryProvider; }
/// <summary> /// 创建 DELETE 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveDeleteCommand <T>(IDbQueryableInfo_Delete dbQuery, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("DELETE FROM "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" "); if (dbQuery.Entity != null) { if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0) { throw new XFrameworkException("Delete<T>(T value) require entity must have key column."); } object entity = dbQuery.Entity; builder.AppendNewLine(); builder.Append("WHERE "); foreach (var m in typeRuntime.KeyMembers) { var column = m.Column; var value = m.Invoke(entity); var seg = this.DbValue.GetSqlValue(value, token, column); builder.AppendMember(m.Member.Name); builder.Append(" = "); builder.Append(seg); builder.Append(" AND "); } builder.Length -= 5; } else if (dbQuery.Query != null) { // 解析查询用来确定是否需要嵌套 var cmd = this.ResolveSelectCommand(dbQuery.Query, 1, false, null) as MappingCommand; if (token != null && token.Extendsions == null) { token.Extendsions = new Dictionary <string, object>(); if (!token.Extendsions.ContainsKey("SQLiteDelete")) { token.Extendsions.Add("SQLiteDelete", null); } } if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0) { // 最外层仅选择 RowID 列 var outQuery = dbQuery.Query; outQuery.Select = new DbExpression(DbExpressionType.Select, Expression.Constant("t0.RowId", typeof(string))); var iterator = outQuery; while (iterator.Subquery != null) { var subQuery = new SQLiteDbQueryableInfo_Select(iterator.Subquery); iterator.Subquery = subQuery; iterator = subQuery; } // 解析成 RowId IN 结构 cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, token); builder.Append("WHERE "); builder.AppendMember("RowID"); builder.Append(" IN("); builder.AppendNewLine(cmd.CommandText); builder.Append(')'); } else { TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token); ExpressionVisitorBase visitor = null; visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Query.Joins); visitor.Write(builder); visitor = new WhereExpressionVisitor(this, null, dbQuery.Query.Where); visitor.Write(builder); } } builder.Append(';'); return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
// 创建 SELECT 命令 RawCommand ResolveSelectCommandImpl(IDbQueryableInfo_Select dbQuery, int indent, bool isOuter, ResolveToken token) { // 说明: // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句 // 2.在有聚合函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询 // 3.'Any' 子句将翻译成 IF EXISTS... // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要 // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询 // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, var subQuery = dbQuery.Subquery as IDbQueryableInfo_Select; if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null) { dbQuery = subQuery; } bool useStatis = dbQuery.Aggregate != null; bool useNesting = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0; string alias0 = token != null && !string.IsNullOrEmpty(token.AliasPrefix) ? (token.AliasPrefix + "0") : "t0"; // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0)); TableAlias aliases = this.PrepareTableAlias(dbQuery, token); var result = new MappingCommand(this, aliases, token) { HasMany = dbQuery.HasMany }; ISqlBuilder jf = result.JoinFragment; ISqlBuilder wf = result.WhereFragment; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0); visitor_.Write(jf); result.AddNavMembers(visitor_.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } jf.Append("SELECT "); if (dbQuery.HasAny) { jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM ("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT 1 "); } if (useStatis && !useNesting) { // 如果有聚合函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy); visitor_.Write(jf); result.AddNavMembers(visitor_.NavMembers); } else { // DISTINCT 子句 if (dbQuery.HasDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!dbQuery.HasAny) { // SELECT 范围 var visitor2 = new SQLiteColumnExpressionVisitor(this, aliases, dbQuery); visitor2.Write(jf); result.PickColumns = visitor2.PickColumns; result.PickColumnText = visitor2.PickColumnText; result.PickNavDescriptors = visitor2.PickNavDescriptors; result.AddNavMembers(visitor2.NavMembers); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (dbQuery.Subquery != null) { // 子查询 jf.Append('('); RawCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Subquery, indent + 1, false, token); jf.Append(cmd.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Where); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (dbQuery.OrderBys.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 if (dbQuery.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token)); } else if (dbQuery.Take == 0 && dbQuery.Skip > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(-1, token)); } if (dbQuery.Skip > 0) { wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token)); } #endregion #region 嵌套查询 if (useStatis && useNesting) { result.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (dbQuery.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.Aggregate == null && !(subQuery.Skip > 0 || subQuery.Take > 0)) { result.CombineFragments(); visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (dbQuery.Unions != null && dbQuery.Unions.Count > 0) { result.CombineFragments(); for (int index = 0; index < dbQuery.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } RawCommand cmd = this.ResolveSelectCommand(dbQuery.Unions[index], indent, isOuter, token); jf.Append(cmd.CommandText); } } #endregion #region Any 子句 // 'Any' 子句 if (dbQuery.HasAny) { // 产生 WHERE 子句 result.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (dbQuery.Take == 0 && dbQuery.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion return(result); }
/// <summary> /// 创建 SQL 构造器 /// </summary> /// <param name="token">参数列表,NULL 或者 Parameters=NULL 时表示不使用参数化</param> /// <returns></returns> public override ISqlBuilder CreateSqlBuilder(ResolveToken token) { return(new SQLiteSqlBuilder(this, token)); }
/// <summary> /// 创建 DELETE 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveDeleteCommand <T>(IDbQueryableInfo_Delete dbQuery, ResolveToken token) { var context = (SqlServerDbContext)token.DbContext; ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("DELETE t0 FROM "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" t0 "); if (dbQuery.Entity != null) { if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0) { throw new XFrameworkException("Delete<T>(T value) require entity must have key column."); } object entity = dbQuery.Entity; builder.AppendNewLine(); builder.Append("WHERE "); foreach (var m in typeRuntime.KeyMembers) { var column = m.Column; var value = m.Invoke(entity); var seg = this.DbValue.GetSqlValue(value, token, column); builder.AppendMember("t0", m.Member.Name); builder.Append(" = "); builder.Append(seg); builder.Append(" AND "); } builder.Length -= 5; } else if (dbQuery.Query != null) { TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token); var cmd = new SqlServerMappingCommand(context, aliases, token) { HasMany = dbQuery.Query.HasMany }; ExpressionVisitorBase visitor = new SqlServerJoinExpressionVisitor(context, aliases, dbQuery.Query.Joins); visitor.Write(cmd.JoinFragment); visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Query.Where); visitor.Write(cmd.WhereFragment); cmd.AddNavMembers(visitor.NavMembers); builder.Append(cmd.CommandText); } return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
/// <summary> /// 创建 UPDATE 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update dbQuery, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("UPDATE "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" t0 SET"); builder.AppendNewLine(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder seg_Where = this.CreateSqlBuilder(token); bool useKey = false; int length = 0; foreach (var m in typeRuntime.Members) { var column = m.Column; if (column != null && column.IsIdentity) { goto gotoLabel; // Fix issue# 自增列同时又是主键 } if (column != null && column.NoMapped) { continue; } if (m.ForeignKey != null) { continue; } if (m.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } builder.AppendMember(m.Member.Name); builder.Append(" = "); gotoLabel: if (column == null || !column.IsIdentity) { var value = m.Invoke(entity); var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); builder.Append(seg); length = builder.Length; builder.Append(','); builder.AppendNewLine(); } if (column != null && column.IsKey) { useKey = true; } } if (!useKey) { throw new XFrameworkException("Update<T>(T value) require T must have key column."); } // ORACLE 需要注意参数顺序问题 int index = -1; foreach (var m in typeRuntime.KeyMembers) { var column = m.Column; var value = m.Invoke(entity); var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); index += 1; seg_Where.AppendMember(m.Member.Name); seg_Where.Append(" = "); seg_Where.Append(seg); if (index < typeRuntime.KeyMembers.Count - 1) { seg_Where.Append(" AND "); } } builder.Length = length; builder.AppendNewLine(); builder.Append("WHERE "); builder.Append(seg_Where); } else if (dbQuery.Expression != null) { // SELECT 表达式 LambdaExpression lambda = dbQuery.Expression as LambdaExpression; var body = lambda.Body; Expression expression = null; if (body.NodeType == ExpressionType.MemberInit) { var @init = body as MemberInitExpression; var bindings = new List <MemberBinding>(@init.Bindings); foreach (var m in typeRuntime.KeyMembers) { var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member); var binding = Expression.Bind(m.Member, member); if (!bindings.Any(x => x.Member == m.Member)) { bindings.Add(binding); } } expression = Expression.MemberInit(@init.NewExpression, bindings); } else if (body.NodeType == ExpressionType.New) { var newExpression = body as NewExpression; var bindings = new List <MemberBinding>(); for (int i = 0; i < newExpression.Members.Count; i++) { var m = typeRuntime.GetMember(newExpression.Members[i].Name); var binding = Expression.Bind(m.Member, newExpression.Arguments[i].Type != m.DataType ? Expression.Convert(newExpression.Arguments[i], m.DataType) : newExpression.Arguments[i]); bindings.Add(binding); } foreach (var m in typeRuntime.KeyMembers) { var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member); var binding = Expression.Bind(m.Member, member); if (!bindings.Any(x => x.Member == m.Member)) { bindings.Add(binding); } } var newExpression2 = Expression.New(typeRuntime.Constructor.Constructor); expression = Expression.MemberInit(newExpression2, bindings); } // 解析查询以确定是否需要嵌套 dbQuery.Query.Select = new DbExpression(DbExpressionType.Select, expression); var cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, null);//, token); if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0) { // 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样。UPDATE 不能用rowid // 有导航属性或者关联查询,使用 MERGE INTO 语法。要求必须有主键 if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0) { throw new XFrameworkException("Update<T>(Expression<Func<T, object>> updateExpression) require entity must have key column."); } builder.Length = 0; builder.Append("MERGE INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.AppendNewLine(" t0"); builder.Append("USING ("); cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, token); builder.AppendNewLine(cmd.CommandText); builder.Append(") t1 ON ("); foreach (var m in typeRuntime.KeyMembers) { builder.AppendMember("t0", m.Name); builder.Append(" = "); builder.AppendMember("t1", m.Name); builder.Append(" AND "); } builder.Length -= 5; builder.Append(')'); // UPDATE builder.AppendNewLine(); builder.AppendNewLine("WHEN MATCHED THEN UPDATE SET "); // SET 字段 var visitor = new OracleUpdateExpressionVisitor(this, null, dbQuery.Expression); visitor.Write(builder); } else { // 直接 SQL 的 UPDATE 语法 TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token); var visitor = new UpdateExpressionVisitor(this, aliases, dbQuery.Expression); visitor.Write(builder); var visitor_ = new WhereExpressionVisitor(this, aliases, dbQuery.Query.Where); visitor_.Write(builder); } } builder.Append(';'); return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
/// <summary> /// 解析 SQL 命令 /// <para> /// 返回的已经解析语义中执行批次用 null 分开 /// </para> /// </summary> /// <param name="dbQueryables">查询语句</param> /// <returns></returns> public override List <RawCommand> Resolve(List <object> dbQueryables) { bool haveBegin = false; ResolveToken token = null; List <RawCommand> sqlList = new List <RawCommand>(); for (int i = 0; i < dbQueryables.Count; i++) { object obj = dbQueryables[i]; if (obj == null) { continue; } if (obj is IDbQueryable) { IDbQueryable dbQueryable = (IDbQueryable)obj; dbQueryable.Parameterized = true; if (token == null) { token = new ResolveToken(); } if (token.Parameters == null) { token.Parameters = new List <IDbDataParameter>(8); } var cmd2 = dbQueryable.Resolve(0, true, token); if (cmd2 is MappingCommand) { // 查询单独执行 if (sqlList.Count > 0 && (i - 1) >= 0 && sqlList[sqlList.Count - 1] != null) { sqlList.Add(null); } sqlList.Add(cmd2); sqlList.Add(null); token = new ResolveToken(); token.Parameters = new List <IDbDataParameter>(8); } else { // 增删改 if (!haveBegin) { sqlList.Add(new RawCommand("BEGIN")); haveBegin = true; } sqlList.Add(cmd2); if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000) { // 1000个参数,就要重新分批 if (haveBegin) { sqlList.Add(new RawCommand("END;")); haveBegin = false; sqlList.Add(null); } token = new ResolveToken(); token.Parameters = new List <IDbDataParameter>(8); } if (i + 1 < dbQueryables.Count) { // 检查下一条是否是选择语句 bool isQuery = false; if (dbQueryables[i + 1] is IDbQueryable) { var queryInfo = ((IDbQueryable)dbQueryables[i + 1]).Parse(); isQuery = queryInfo is IDbQueryableInfo_Select; } else if ((dbQueryables[i + 1] is string)) { string sql = dbQueryables[i + 1].ToString(); string method = string.Empty; if (sql.Length > 6) { method = sql.Substring(0, 6).Trim().ToUpper(); } isQuery = method == "SELECT"; } else if (dbQueryables[i + 1] is RawSql) { string sql = ((RawSql)dbQueryables[i + 1]).CommandText; string method = string.Empty; if (sql.Length > 6) { method = sql.Substring(0, 6).Trim().ToUpper(); } isQuery = method == "SELECT"; } // 如果下一条是SELECT 语句,则需要结束当前语句块 if (isQuery) { if (haveBegin) { sqlList.Add(new RawCommand("END;")); haveBegin = false; sqlList.Add(null); } token = new ResolveToken(); token.Parameters = new List <IDbDataParameter>(8); } } } } else if (obj is RawSql || obj is string) { string sql = string.Empty; if (obj is string) { sql = obj.ToString(); } else { RawSql rawSql = (RawSql)obj; // 解析参数 object[] args = null; if (rawSql.Parameters != null) { args = rawSql.Parameters.Select(x => this.DbValue.GetSqlValue(x, token)).ToArray(); } sql = rawSql.CommandText; if (args != null && args.Length > 0) { sql = string.Format(sql, args); } } string methodName = string.Empty; if (sql.Length > 6) { methodName = sql.Substring(0, 6).Trim().ToUpper(); } if (methodName == "SELECT") { if (sqlList.Count > 0 && (i - 1) >= 0 && sqlList[sqlList.Count - 1] != null) { sqlList.Add(null); } } var cmd2 = new RawCommand(sql, token.Parameters, CommandType.Text); sqlList.Add(cmd2); if (methodName == "SELECT") { sqlList.Add(cmd2); sqlList.Add(null); token = new ResolveToken(); token.Parameters = new List <IDbDataParameter>(8); } else if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000) { // 1000个参数,就要重新分批 if (haveBegin) { sqlList.Add(new RawCommand("END;")); sqlList.Add(null); haveBegin = false; } token = new ResolveToken(); token.Parameters = new List <IDbDataParameter>(8); } } else { if (!haveBegin) { sqlList.Add(new RawCommand("BEGIN")); haveBegin = true; } // 解析批量插入操作 List <IDbQueryable> bulkList = obj as List <IDbQueryable>; if (bulkList != null && bulkList.Count > 0) { this.ResolveBulk(sqlList, bulkList); } } if (haveBegin && i == dbQueryables.Count - 1) { sqlList.Add(new RawCommand("END;")); } } return(sqlList); }
// 创建 INSRT 命令 protected override Command ParseInsertCommand <T>(DbQueryableInfo_Insert <T> nQueryInfo, ResolveToken token) { TableAliasCache aliases = new TableAliasCache(); ISqlBuilder builder = this.CreateSqlBuilder(token); TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); if (nQueryInfo.Entity != null) { object entity = nQueryInfo.Entity; ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token); ISqlBuilder valuesBuilder = this.CreateSqlBuilder(token); // 指定插入列 MemberInvokerCollection invokers = typeRuntime.Invokers; if (nQueryInfo.EntityColumns != null && nQueryInfo.EntityColumns.Count > 0) { invokers = new MemberInvokerCollection(); for (int i = 0; i < nQueryInfo.EntityColumns.Count; i++) { Expression curExpr = nQueryInfo.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("ERR {0}: Only support MemberAccess expression.", nQueryInfo.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; invokers[name] = typeRuntime.Invokers[name]; } } foreach (var invoker in invokers) { var column = invoker.Column; if (column != null && column.NoMapped) { continue; } if (invoker.ForeignKey != null) { continue; } if (invoker.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp) { continue; // 行版本号 } if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } if (invoker != nQueryInfo.AutoIncrement) { columnsBuilder.AppendMember(invoker.Member.Name); columnsBuilder.Append(','); var value = invoker.Invoke(entity); string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); valuesBuilder.Append(seg); valuesBuilder.Append(','); } } columnsBuilder.Length -= 1; valuesBuilder.Length -= 1; if (nQueryInfo.Bulk == null || !nQueryInfo.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); builder.Append(columnsBuilder); builder.Append(')'); builder.AppendNewLine(); builder.AppendNewTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(valuesBuilder); builder.Append(')'); if (nQueryInfo.Bulk != null && !nQueryInfo.Bulk.IsEndPos) { builder.Append(","); } if (nQueryInfo.Bulk == null && nQueryInfo.AutoIncrement != null) { builder.AppendNewLine(); builder.Append("SELECT SCOPE_IDENTITY()"); builder.AppendAs(Constant.AUTOINCREMENTNAME); } } else if (nQueryInfo.SelectInfo != null) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); int i = 0; MappingCommand cmd2 = this.ParseSelectCommand(nQueryInfo.SelectInfo, 0, true, token) as MappingCommand; foreach (Column column in cmd2.PickColumns) { builder.AppendMember(column.NewName); if (i < cmd2.PickColumns.Count - 1) { builder.Append(','); } i++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd2.CommandText); } return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
// 创建 SELECT 命令 protected override Command ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ResolveToken token) { // 说明: // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句 // 2.在有统计函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询 // 3.'Any' 子句将翻译成 IF EXISTS... // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要 // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询 // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, var subQueryInfo = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQueryInfo.HasMany && subQueryInfo != null && subQueryInfo.StatisExpression != null) { sQueryInfo = subQueryInfo; } bool useStatis = sQueryInfo.StatisExpression != null; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0"; bool useSubQuery = sQueryInfo.HasDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0; bool useOrderBy = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HasAny && (!sQueryInfo.SubQueryByMany || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0)); IDbQueryable dbQueryable = sQueryInfo.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQueryInfo, token); MappingCommand cmd = new MappingCommand(this, aliases, token) { HasMany = sQueryInfo.HasMany }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; jf.Indent = indent; #region 嵌套查询 if (useStatis && useSubQuery) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression, alias0); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; } #endregion 嵌套查询 #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } if (sQueryInfo.HasAny) { jf.Append("IF EXISTS("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); } jf.Append("SELECT "); if (useStatis && !useSubQuery) { // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQueryInfo.HasDistinct) { jf.Append("DISTINCT "); } // TOP 子句 if (sQueryInfo.Take > 0 && sQueryInfo.Skip == 0) { jf.AppendFormat("TOP({0})", this.DbValue.GetSqlValue(sQueryInfo.Take, token)); } // Any if (sQueryInfo.HasAny) { jf.Append("TOP 1 1"); } #region 字段 if (!sQueryInfo.HasAny) { // SELECT 范围 var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo); visitor2.Write(jf); cmd.PickColumns = visitor2.PickColumns; cmd.PickColumnText = visitor2.PickColumnText; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); } #endregion 字段 } #endregion 择子句 #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (sQueryInfo.SubQueryInfo != null) { // 子查询 jf.Append('('); Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQueryInfo.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); SqlServerDbContext context = (SqlServerDbContext)dbQueryable.DbContext; if (context.NoLock && !string.IsNullOrEmpty(this._widthNoLock)) { jf.Append(this._widthNoLock); } } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQueryInfo.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQueryInfo.WhereExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQueryInfo.HavingExpression, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQueryInfo.OrderBys.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion 顺序解析 #region 分页查询 if (sQueryInfo.Skip > 0) { if (sQueryInfo.OrderBys.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } wf.AppendNewLine(); wf.Append("OFFSET "); wf.Append(this.DbValue.GetSqlValue(sQueryInfo.Skip, token)); wf.Append(" ROWS"); if (sQueryInfo.Take > 0) { wf.Append(" FETCH NEXT "); wf.Append(this.DbValue.GetSqlValue(sQueryInfo.Take, token)); wf.Append(" ROWS ONLY "); } } #endregion 分页查询 #region 嵌套查询 if (useStatis && useSubQuery) { cmd.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } #endregion 嵌套查询 #region 嵌套导航 if (sQueryInfo.HasMany && subQueryInfo.StatisExpression == null && subQueryInfo != null && subQueryInfo.OrderBys.Count > 0 && !(subQueryInfo.Skip > 0 || subQueryInfo.Take > 0)) { // TODO Include 从表,没分页,OrderBy 报错 cmd.CombineFragments(); visitor = new OrderByExpressionVisitor(this, aliases, subQueryInfo.OrderBys); visitor.Write(jf); } #endregion 嵌套导航 #region 并集查询 // UNION 子句 if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0) { cmd.CombineFragments(); for (int index = 0; index < sQueryInfo.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token); jf.Append(cmd2.CommandText); } } #endregion 并集查询 #region Any 子句 // 'Any' 子句 if (sQueryInfo.HasAny) { cmd.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") SELECT 1 ELSE SELECT 0"); } #endregion Any 子句 return(cmd); }
// 创建 DELETE 命令 protected override Command ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQueryInfo, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("DELETE FROM "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" "); if (dQueryInfo.Entity != null) { if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0) { throw new XFrameworkException("Delete<T>(T value) require entity must have key column."); } object entity = dQueryInfo.Entity; builder.AppendNewLine(); builder.Append("WHERE "); foreach (var invoker in typeRuntime.KeyInvokers) { var column = invoker.Column; var value = invoker.Invoke(entity); var seg = this.DbValue.GetSqlValue(value, token, column); builder.AppendMember(invoker.Member.Name); builder.Append(" = "); builder.Append(seg); builder.Append(" AND "); } builder.Length -= 5; } else if (dQueryInfo.SelectInfo != null) { LambdaExpression lambda = null; var dbQuery = dQueryInfo.SourceQuery; if (dbQuery.DbExpressions != null && dbQuery.DbExpressions.Count > 1) { switch (dbQuery.DbExpressions[1].DbExpressionType) { case DbExpressionType.Join: case DbExpressionType.GroupJoin: case DbExpressionType.GroupRightJoin: lambda = (LambdaExpression)dbQuery.DbExpressions[1].Expressions[1]; break; case DbExpressionType.Select: case DbExpressionType.SelectMany: lambda = (LambdaExpression)dbQuery.DbExpressions[1].Expressions[0]; break; } } if (lambda == null) { DbExpression dbExpression = dQueryInfo.SelectInfo.SelectExpression; dbExpression = dQueryInfo.SelectInfo.WhereExpression; if (dbExpression != null && dbExpression.Expressions != null) { lambda = (LambdaExpression)dbExpression.Expressions[0]; } } // 解析查询以确定是否需要嵌套 var parameter = Expression.Parameter(typeof(SQLiteRowId), lambda != null ? lambda.Parameters[0].Name : "x"); var expression = Expression.MakeMemberAccess(parameter, (_rowIdExpression.Body as MemberExpression).Member); dQueryInfo.SelectInfo.SelectExpression = new DbExpression(DbExpressionType.Select, expression); var cmd = (MappingCommand)this.ParseSelectCommand <T>(dQueryInfo.SelectInfo, 1, false, null); if (token != null && token.Extendsions == null) { token.Extendsions = new Dictionary <string, object>(); if (!token.Extendsions.ContainsKey("SQLiteDelete")) { token.Extendsions.Add("SQLiteDelete", null); } } if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dQueryInfo.SelectInfo.Joins.Count > 0) { cmd = (MappingCommand)this.ParseSelectCommand <T>(dQueryInfo.SelectInfo, 1, false, token); builder.Append("WHERE "); builder.AppendMember("RowID"); builder.Append(" IN("); builder.AppendNewLine(cmd.CommandText); builder.Append(')'); } else { TableAliasCache aliases = this.PrepareAlias <T>(dQueryInfo.SelectInfo, token); ExpressionVisitorBase visitor = null; visitor = new JoinExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.Joins); visitor.Write(builder); visitor = new WhereExpressionVisitor(this, null, dQueryInfo.SelectInfo.WhereExpression); visitor.Write(builder); } } builder.Append(';'); return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
// 创建 SELECT 命令 protected override Command ResolveSelectCommand <T>(DbQueryableInfo_Select <T> dbQuery, int indent, bool isOuter, ResolveToken token) { // 说明: // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句 // 2.在有聚合函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询 // 3.'Any' 子句将翻译成 IF EXISTS... // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要 // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询 // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题 // 8.如果有分页,则使用嵌套 // 9.如果有分页还有OrderBy,则使用嵌套的嵌套 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, var subQuery = dbQuery.SubQueryInfo as DbQueryableInfo_Select <T>; if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null) { dbQuery = subQuery; } bool useStatis = dbQuery.Aggregate != null; bool useNesting = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0; string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0"; // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.SubQueryOfMany || (dbQuery.Skip > 0 || dbQuery.Take > 0)); TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery, token); MapperCommand cmd = new MapperCommand(this, aliases, token) { HasMany = dbQuery.HasMany }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; (jf as OracleSqlBuilder).IsOuter = isOuter; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; (jf as OracleSqlBuilder).IsOuter = false; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } jf.Append("SELECT "); if (dbQuery.HasAny) { jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM ("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT 1 "); } if (useStatis && !useNesting) { // 如果有聚合函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor2 = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (dbQuery.HasDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!dbQuery.HasAny) { // SELECT 范围 ISqlBuilder sf = this.CreateSqlBuilder(token); sf.Indent = jf.Indent + ((dbQuery.Skip > 0 || dbQuery.Take > 0) ? 2 : 0); (sf as OracleSqlBuilder).IsOuter = (dbQuery.Skip > 0 || dbQuery.Take > 0) ? false : (jf as OracleSqlBuilder).IsOuter; var visitor2 = new ColumnExpressionVisitor(this, aliases, dbQuery); visitor2.Write(sf); cmd.PickColumns = visitor2.PickColumns; cmd.PickColumnText = visitor2.PickColumnText; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); // 分页,产生两层嵌套 if (dbQuery.Skip > 0 || dbQuery.Take > 0) { // 第一层嵌套 int index = 0; jf.AppendNewLine(); foreach (var column in cmd.PickColumns) { jf.AppendMember(alias0, column.Name); jf.AppendAs(column.NewName); index += 1; if (index < cmd.PickColumns.Count) { jf.Append(','); jf.AppendNewLine(); } } jf.AppendNewLine(); jf.Append("FROM("); // 第二层嵌套 indent += 1; isOuter = false; jf.Indent = indent; (jf as OracleSqlBuilder).IsOuter = isOuter; jf.AppendNewLine(); jf.Append("SELECT"); jf.AppendNewLine(); foreach (var column in cmd.PickColumns) { jf.AppendMember(alias0, column.NewName); jf.AppendAs(column.NewName); jf.Append(','); jf.AppendNewLine(); } jf.Append("ROWNUM AS Row_Number0"); jf.AppendNewLine(); jf.Append("FROM("); // 第三层嵌套 indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT"); } jf.Append(sf); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (dbQuery.SubQueryInfo != null) { // 子查询 jf.Append('('); Command cmd2 = this.ResolveSelectCommand <T>(dbQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromEntityType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Condtion); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (dbQuery.OrderBys.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 嵌套查询 if (useStatis && useNesting) { cmd.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (dbQuery.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.Aggregate == null && !(subQuery.Skip > 0 || subQuery.Take > 0)) { // OrderBy("a.CloudServer.CloudServerName"); cmd.CombineFragments(); visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (dbQuery.Unions != null && dbQuery.Unions.Count > 0) { cmd.CombineFragments(); for (int index = 0; index < dbQuery.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } Command cmd2 = this.ResolveSelectCommand <T>(dbQuery.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token); jf.Append(cmd2.CommandText); } } #endregion #region 分页查询 if (dbQuery.Take > 0 || dbQuery.Skip > 0) { // 合并 WHERE cmd.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.AppendNewLine(); jf.Append("WHERE "); if (dbQuery.Skip > 0) { jf.AppendMember(alias0, "Row_Number0"); jf.Append(" > "); jf.Append(this.DbValue.GetSqlValue(dbQuery.Skip, token)); } if (dbQuery.Take > 0) { if (dbQuery.Skip > 0) { jf.Append(" AND "); } jf.AppendMember(alias0, "Row_Number0"); jf.Append(" <= "); jf.Append(this.DbValue.GetSqlValue((dbQuery.Skip + dbQuery.Take), token)); } } #endregion #region Any 子句 // 'Any' 子句 if (dbQuery.HasAny) { // 产生 WHERE 子句 cmd.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (dbQuery.Take == 0 && dbQuery.Skip == 0) { if (dbQuery.Condtion != null && dbQuery.Condtion.Expressions != null) { jf.Append(" AND ROWNUM <= 1"); } else { jf.AppendNewLine(); jf.Append("WHERE ROWNUM <= 1"); } } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion return(cmd); }
/// <summary> /// 创建 INSRT 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); TableAlias aliases = new TableAlias(); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); if (dbQuery.Entity != null) { // 如果没有Sequence列,使用 INSERT ALL INTO 语法,否则就一条一条逐行写入~~ // 批量 INSERT,自增列不会自动赋值 object entity = dbQuery.Entity; ISqlBuilder seg_Columns = this.CreateSqlBuilder(token); ISqlBuilder seg_Values = this.CreateSqlBuilder(token); // 指定插入列 MemberAccessorCollection memberAccessors = typeRuntime.Members; if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0) { memberAccessors = new MemberAccessorCollection(); for (int i = 0; i < dbQuery.EntityColumns.Count; i++) { Expression curExpr = dbQuery.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; memberAccessors[name] = typeRuntime.Members[name]; } } // 自增列参数 IDbDataParameter seqParameter = null; // 自增列标记 ColumnAttribute seqColumn = null; foreach (var m in memberAccessors) { var column = m.Column; if (column != null && column.NoMapped) { continue; } if (m.ForeignKey != null) { continue; } if (m.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } seg_Columns.AppendMember(m.Member.Name); seg_Columns.Append(','); if (m == typeRuntime.Identity) { seqColumn = column; if (dbQuery.Bulk == null) { // 非批量INSERT,产生一个 OUTPUT 类型的参数 string pName = string.Format("{0}p{1}", this.ParameterPrefix, token.Parameters.Count); var database = token.DbContext.Database; seqParameter = database.CreateParameter(pName, -1, direction: ParameterDirection.Output); token.Parameters.Add(seqParameter); seg_Values.Append(seqParameter.ParameterName); seg_Values.Append(','); } else { seg_Values.Append(((OracleColumnAttribute)column).SEQName); seg_Values.Append(".NEXTVAL"); seg_Values.Append(','); } } else { var value = m.Invoke(entity); string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); seg_Values.Append(seg); seg_Values.Append(','); } } seg_Columns.Length -= 1; seg_Values.Length -= 1; if (dbQuery.Bulk == null) { // 非批量INSERT,产生一个 OUTPUT 类型的参数 if (seqParameter != null) { seqParameter.Direction = ParameterDirection.Output; seqParameter.DbType = DbType.Int64; builder.Append("SELECT "); builder.Append(((OracleColumnAttribute)seqColumn).SEQName); builder.Append(".NEXTVAL INTO "); builder.Append(seqParameter.ParameterName); builder.Append(" FROM DUAL;"); builder.AppendNewLine(); //useSEQ = true; } builder.Append("INSERT "); } else { // 批量 INSERT if (!dbQuery.Bulk.OnlyValue || seqColumn != null) { builder.Append("INSERT "); } // 如果有自增列则不使用 INSERT ALL INTO 语法 if (!dbQuery.Bulk.OnlyValue && seqColumn == null) { builder.Append("ALL "); } } builder.Append("INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); builder.Append(seg_Columns); builder.Append(')'); builder.AppendNewLine(); builder.AppendTab(); builder.Append("VALUES"); builder.Append('('); builder.Append(seg_Values); builder.Append(')'); if (dbQuery.Bulk == null) { builder.Append(';'); } else { if (seqColumn != null) { if (dbQuery.Bulk.IsEndPos) { builder.Append(";"); } else { builder.AppendNewLine(";"); } } else { builder.AppendNewLine(); if (dbQuery.Bulk.IsEndPos) { builder.Append("SELECT 1 FROM DUAL;"); } } } } else if (dbQuery.Query != null) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); int i = 0; MappingCommand cmd = this.ResolveSelectCommand(dbQuery.Query, 0, false, token) as MappingCommand; foreach (var column in cmd.PickColumns) { builder.AppendMember(column.Name); if (i < cmd.PickColumns.Count - 1) { builder.Append(','); } i++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd.CommandText); builder.Append(';'); } var result = new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text); return(result); }
// 创建 SELECT 命令 protected override Command ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ResolveToken token) { var cmd = (MappingCommand)this.ParseSelectCommandImpl <T>(sQueryInfo, indent, isOuter, token); cmd.CombineFragments(); if (isOuter) { cmd.JoinFragment.Append(';'); } return(cmd); }
RawCommand ResolveSelectCommandImpl(IDbQueryableInfo_Select dbQuery, int indent, bool isOuter, ResolveToken token) { // 说明: // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句 // 2.在有聚合函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询 // 3.'Any' 子句将翻译成 IF EXISTS... // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要 // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询 // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题 // 8.如果只有 Skip 没有 Take,则使用 Row_Number() Over()分页语法,其它使用 LIMIT ## OFFSET 语法 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, var subQuery = dbQuery.Subquery as IDbQueryableInfo_Select; if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null) { dbQuery = subQuery; } bool useStatis = dbQuery.Aggregate != null; // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY string alias0 = token != null && !string.IsNullOrEmpty(token.AliasPrefix) ? (token.AliasPrefix + "0") : "t0"; bool useSubquery = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0; bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0)); TableAlias aliases = this.PrepareTableAlias(dbQuery, token); var result = new MappingCommand(this, aliases, token) { HasMany = dbQuery.HasMany }; ISqlBuilder jf = result.JoinFragment; ISqlBuilder wf = result.WhereFragment; ISqlBuilder sf = null; jf.Indent = indent; #region 嵌套查询 if (useStatis && useSubquery) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0); visitor_.Write(jf); result.AddNavMembers(visitor_.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } jf.Append("SELECT "); if (dbQuery.HasAny) { jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM ("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT 1 "); } if (useStatis && !useSubquery) { // 如果有聚合函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy); visitor_.Write(jf); result.AddNavMembers(visitor_.NavMembers); } else { // DISTINCT 子句 if (dbQuery.HasDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!dbQuery.HasAny) { // SELECT 范围 var visitor_ = new ColumnExpressionVisitor(this, aliases, dbQuery); if (dbQuery.Skip > 0 && dbQuery.Take == 0) { sf = this.CreateSqlBuilder(token); sf.Indent = jf.Indent + 1; visitor_.Write(sf); } else { visitor_.Write(jf); } result.PickColumns = visitor_.PickColumns; result.PickColumnText = visitor_.PickColumnText; result.PickNavDescriptors = visitor_.PickNavDescriptors; result.AddNavMembers(visitor_.NavMembers); if (sf != null) { // 第一层嵌套 int index = 0; jf.AppendNewLine(); foreach (var column in result.PickColumns) { jf.AppendMember(alias0, column.Name); jf.AppendAs(column.NewName); index += 1; if (index < result.PickColumns.Count) { jf.Append(','); jf.AppendNewLine(); } } jf.AppendNewLine(); jf.Append("FROM("); // 第二层嵌套 indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT"); jf.Append(sf); jf.Append(','); jf.AppendNewLine(); if (dbQuery.OrderBys.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } jf.Append("ROW_NUMBER() OVER("); var visitor3 = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy); visitor3.Write(jf, false); result.AddNavMembers(visitor3.NavMembers); jf.Append(") Row_Number0"); } } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (dbQuery.Subquery != null) { // 子查询 jf.Append("("); var cmd = this.ResolveSelectCommandImpl(dbQuery.Subquery, indent + 1, false, token); jf.Append(cmd.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Where); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (dbQuery.OrderBys.Count > 0 && useOrderBy)// && !groupByPaging) { visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 // LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。 // LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。 // 初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。 // Limit n,-1 语法不支持,使用ROW_Number()语法代替 if (dbQuery.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token)); wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token)); } #endregion #region 嵌套查询 if (useStatis && useSubquery) { result.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (dbQuery.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.Aggregate == null && !(subQuery.Skip > 0 || subQuery.Take > 0)) { result.CombineFragments(); visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (dbQuery.Unions != null && dbQuery.Unions.Count > 0) { result.CombineFragments(); for (int index = 0; index < dbQuery.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } RawCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Unions[index], indent, isOuter, token); jf.Append(cmd.CommandText); } } #endregion #region 分页查询 if (sf != null) { // 合并 WHERE result.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.AppendNewLine(); jf.Append("WHERE "); if (dbQuery.Skip > 0) { jf.AppendMember(alias0, "Row_Number0"); jf.Append(" > "); jf.Append(this.DbValue.GetSqlValue(dbQuery.Skip, token)); } } #endregion #region Any 子句 // 'Any' 子句 if (dbQuery.HasAny) { // 产生 WHERE 子句 result.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (dbQuery.Take == 0 && dbQuery.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 0,1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion return(result); }
// 创建 SELECT 命令 Command ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ResolveToken token) { // 说明: // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句 // 2.在有统计函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询 // 3.'Any' 子句将翻译成 IF EXISTS... // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要 // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询 // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, DbQueryableInfo_Select <T> subQuery = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQueryInfo.HasMany && subQuery != null && subQuery.StatisExpression != null) { sQueryInfo = subQuery; } bool useStatis = sQueryInfo.StatisExpression != null; bool useNesting = sQueryInfo.HasDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0; string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0"; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HasAny && (!sQueryInfo.SubQueryByMany || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0)); IDbQueryable dbQueryable = sQueryInfo.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQueryInfo, token); MappingCommand cmd = new MappingCommand(this, aliases, token) { HasMany = sQueryInfo.HasMany }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; (jf as NpgSqlBuilder).IsOuter = isOuter; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression, alias0); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; (jf as NpgSqlBuilder).IsOuter = false; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } jf.Append("SELECT "); if (sQueryInfo.HasAny) { jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM ("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT 1 "); } if (useStatis && !useNesting) { // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQueryInfo.HasDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!sQueryInfo.HasAny) { // SELECT 范围 var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo); visitor2.Write(jf); cmd.PickColumns = visitor2.PickColumns; cmd.PickColumnText = visitor2.PickColumnText; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (sQueryInfo.SubQueryInfo != null) { // 子查询 jf.Append('('); Command cmd2 = this.ParseSelectCommandImpl <T>(sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQueryInfo.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQueryInfo.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQueryInfo.WhereExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQueryInfo.HavingExpression, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQueryInfo.OrderBys.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 if (sQueryInfo.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(sQueryInfo.Take, token)); } if (sQueryInfo.Skip > 0) { wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(sQueryInfo.Skip, token)); } #endregion #region 嵌套查询 if (useStatis && useNesting) { cmd.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (sQueryInfo.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.StatisExpression == null && !(subQuery.Skip > 0 || subQuery.Take > 0)) { cmd.CombineFragments(); visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0) { cmd.CombineFragments(); for (int index = 0; index < sQueryInfo.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } Command cmd2 = this.ParseSelectCommandImpl <T>(sQueryInfo.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token); jf.Append(cmd2.CommandText); } } #endregion #region Any 子句 // 'Any' 子句 if (sQueryInfo.HasAny) { // 产生 WHERE 子句 cmd.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (sQueryInfo.Take == 0 && sQueryInfo.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion return(cmd); }
/// <summary> /// 创建 UPDATE 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update dbQuery, ResolveToken token) { var context = (SqlServerDbContext)token.DbContext; ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("UPDATE t0 SET"); builder.AppendNewLine(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder seg_Where = this.CreateSqlBuilder(token); bool useKey = false; int length = 0; foreach (var m in typeRuntime.Members) { var column = m.Column; if (column != null && column.IsIdentity) { goto gotoLabel; // Fix issue# 自增列同时又是主键 } if (column != null && column.NoMapped) { continue; } if (m.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp) { continue; // 行版本号 } if (m.ForeignKey != null) { continue; } if (m.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } builder.AppendMember("t0", m.Member.Name); builder.Append(" = "); gotoLabel: var value = m.Invoke(entity); var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); if (column == null || !column.IsIdentity) { builder.Append(seg); length = builder.Length; builder.Append(','); builder.AppendNewLine(); } if (column != null && column.IsKey) { useKey = true; seg_Where.AppendMember("t0", m.Member.Name); seg_Where.Append(" = "); seg_Where.Append(seg); seg_Where.Append(" AND "); } } if (!useKey) { throw new XFrameworkException("Update<T>(T value) require entity must have key column."); } builder.Length = length; seg_Where.Length -= 5; builder.AppendNewLine(); builder.Append("FROM "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" t0"); builder.AppendNewLine(); builder.Append("WHERE "); builder.Append(seg_Where); } else if (dbQuery.Expression != null) { TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token); ExpressionVisitorBase visitor = null; visitor = new UpdateExpressionVisitor(this, aliases, dbQuery.Expression); visitor.Write(builder); builder.AppendNewLine(); builder.Append("FROM "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.AppendAs("t0"); var cmd = new SqlServerMappingCommand(context, aliases, token) { HasMany = dbQuery.Query.HasMany }; visitor = new SqlServerJoinExpressionVisitor(context, aliases, dbQuery.Query.Joins); visitor.Write(cmd.JoinFragment); visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Query.Where); visitor.Write(cmd.WhereFragment); cmd.AddNavMembers(visitor.NavMembers); builder.Append(cmd.CommandText); } return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
/// <summary> /// 实例化 <see cref="SqlServerSqlBuilder"/> 类的新实例 /// </summary> /// <param name="provider">提供者</param> /// <param name="token">参数列表,NULL 或者 Parameters=NULL 时表示不使用参数化</param> public SqlServerSqlBuilder(IDbQueryProvider provider, ResolveToken token) : base(provider, token) { }
/// <summary> /// 解析 SELECT 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="indent">缩进</param> /// <param name="isOuter">指示是最外层查询</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveSelectCommand(IDbQueryableInfo_Select dbQuery, int indent, bool isOuter, ResolveToken token) { var cmd = (MappingCommand)this.ResolveSelectCommandImpl(dbQuery, indent, isOuter, token); cmd.CombineFragments(); if (isOuter) { cmd.JoinFragment.Append(';'); } return(cmd); }
// 创建 INSRT 命令 protected override Command ResolveInsertCommand <T>(DbQueryableInfo_Insert <T> dbQuery, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); TableAliasCache aliases = new TableAliasCache(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token); ISqlBuilder valuesBuilder = this.CreateSqlBuilder(token); // 指定插入列 MemberInvokerCollection invokers = typeRuntime.Invokers; if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0) { invokers = new MemberInvokerCollection(); for (int i = 0; i < dbQuery.EntityColumns.Count; i++) { Expression curExpr = dbQuery.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; invokers[name] = typeRuntime.Invokers[name]; } } foreach (var invoker in invokers) { var column = invoker.Column; if (column != null && column.NoMapped) { continue; } if (invoker.ForeignKey != null) { continue; } if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } if (invoker != dbQuery.AutoIncrement) { columnsBuilder.AppendMember(invoker.Member.Name); columnsBuilder.Append(','); var value = invoker.Invoke(entity); string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); valuesBuilder.Append(seg); valuesBuilder.Append(','); } } columnsBuilder.Length -= 1; valuesBuilder.Length -= 1; if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); builder.Append(columnsBuilder); builder.Append(')'); builder.AppendNewLine(); builder.AppendNewTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(valuesBuilder); builder.Append(')'); if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos) { builder.Append(","); } if (dbQuery.Bulk == null && dbQuery.AutoIncrement != null) { builder.Append(';'); builder.AppendNewLine(); // sequence,命名原则是 tablename_columnname_seq. builder.AppendFormat("SELECT CURRVAL('{0}_{1}_seq')", typeRuntime.TableName, dbQuery.AutoIncrement.Member.Name); builder.Append(" AS "); builder.Append(this.QuotePrefix); builder.Append(Constant.AUTOINCREMENTNAME); builder.Append(this.QuoteSuffix); } } else if (dbQuery.SelectInfo != null) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); int i = 0; MapperCommand cmd2 = this.ResolveSelectCommandImpl(dbQuery.SelectInfo, 0, false, token) as MapperCommand; //for (int i = 0; i < seg.Columns.Count; i++) foreach (var column in cmd2.PickColumns) { builder.AppendMember(column.Name); if (i < cmd2.PickColumns.Count - 1) { builder.Append(','); } i++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd2.CommandText); } if (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos) { builder.Append(';'); } return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
/// <summary> /// 创建 INSRT 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token) { TableAlias aliases = new TableAlias(); ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder seg_Columns = this.CreateSqlBuilder(token); ISqlBuilder seg_Values = this.CreateSqlBuilder(token); // 指定插入列 MemberAccessorCollection memberAccessors = typeRuntime.Members; if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0) { memberAccessors = new MemberAccessorCollection(); for (int i = 0; i < dbQuery.EntityColumns.Count; i++) { Expression curExpr = dbQuery.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; memberAccessors[name] = typeRuntime.Members[name]; } } foreach (var m in memberAccessors) { var column = m.Column; if (column != null && column.NoMapped) { continue; } if (m.ForeignKey != null) { continue; } if (m.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } if (m != typeRuntime.Identity) { seg_Columns.AppendMember(m.Member.Name); seg_Columns.Append(','); var value = m.Invoke(entity); string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); seg_Values.Append(seg); seg_Values.Append(','); } } seg_Columns.Length -= 1; seg_Values.Length -= 1; if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); builder.Append(seg_Columns); builder.Append(')'); builder.AppendNewLine(); builder.AppendTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(seg_Values); builder.Append(')'); if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos) { builder.Append(","); } if (dbQuery.Bulk == null && typeRuntime.Identity != null) { builder.Append(';'); builder.AppendNewLine(); builder.AppendFormat("SELECT LAST_INSERT_ROWID()"); builder.Append(" AS "); builder.Append(this.QuotePrefix); builder.Append(Constant.AUTO_INCREMENT_NAME); builder.Append(this.QuoteSuffix); } } else if (dbQuery.Query != null) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); int i = 0; MappingCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Query, 0, false, token) as MappingCommand; //for (int i = 0; i < seg.Columns.Count; i++) foreach (var column in cmd.PickColumns) { builder.AppendMember(column.Name); if (i < cmd.PickColumns.Count - 1) { builder.Append(','); } i++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd.CommandText); } if (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos) { builder.Append(';'); } return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
// 创建 DELETE 命令 protected override Command ResolveDeleteCommand <T>(DbQueryableInfo_Delete <T> dbQuery, ResolveToken token) { TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); ISqlBuilder builder = this.CreateSqlBuilder(token); builder.Append("DELETE FROM "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" t0 "); if (dbQuery.Entity != null) { if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0) { throw new XFrameworkException("Delete<T>(T value) require entity must have key column."); } object entity = dbQuery.Entity; builder.AppendNewLine(); builder.Append("WHERE "); foreach (var invoker in typeRuntime.KeyInvokers) { var column = invoker.Column; var value = invoker.Invoke(entity); var seg = this.DbValue.GetSqlValue(value, token, column); builder.AppendMember("t0", invoker.Member.Name); builder.Append(" = "); builder.Append(seg); builder.Append(" AND "); } builder.Length -= 5; } else if (dbQuery.SelectInfo != null) { TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery.SelectInfo, token); var cmd2 = new NpgMapperCommand(this, aliases, DbExpressionType.Delete, token) { HasMany = dbQuery.SelectInfo.HasMany }; var visitor0 = new NpgJoinExpressionVisitor(this, aliases, dbQuery.SelectInfo.Joins, DbExpressionType.Delete); visitor0.Write(cmd2); var visitor1 = new NpgWhereExpressionVisitor(this, aliases, dbQuery.SelectInfo.Condtion); visitor1.Write(cmd2.WhereFragment); cmd2.AddNavMembers(visitor1.NavMembers); builder.Append(cmd2.CommandText); } builder.Append(';'); return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
/// <summary> /// 创建 UPDATE 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update dbQuery, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("UPDATE "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" SET"); builder.AppendNewLine(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder seg_Where = this.CreateSqlBuilder(token); bool useKey = false; int length = 0; foreach (var m in typeRuntime.Members) { var column = m.Column; if (column != null && column.IsIdentity) { goto gotoLabel; // Fix issue# 自增列同时又是主键 } if (column != null && column.NoMapped) { continue; } if (m.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp) { continue; // 行版本号 } if (m.ForeignKey != null) { continue; } if (m.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } builder.AppendMember(m.Member.Name); builder.Append(" = "); gotoLabel: var value = m.Invoke(entity); var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); if (column == null || !column.IsIdentity) { builder.Append(seg); length = builder.Length; builder.Append(','); builder.AppendNewLine(); } if (column != null && column.IsKey) { useKey = true; seg_Where.AppendMember(m.Member.Name); seg_Where.Append(" = "); seg_Where.Append(seg); seg_Where.Append(" AND "); } } if (!useKey) { throw new XFrameworkException("Update<T>(T value) require entity must have key column."); } builder.Length = length; seg_Where.Length -= 5; builder.AppendNewLine(); builder.Append("WHERE "); builder.Append(seg_Where); } else if (dbQuery.Expression != null) { // SELECT 表达式 LambdaExpression lambda = dbQuery.Expression as LambdaExpression; var body = lambda.Body; Expression expression = null; if (body.NodeType == ExpressionType.MemberInit) { var memberInit = body as MemberInitExpression; var bindings = new List <MemberBinding>(memberInit.Bindings); foreach (var m in typeRuntime.KeyMembers) { var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member); var binding = Expression.Bind(m.Member, member); if (!bindings.Any(x => x.Member == m.Member)) { bindings.Add(binding); } } expression = Expression.MemberInit(memberInit.NewExpression, bindings); } else if (body.NodeType == ExpressionType.New) { var newExpression = body as NewExpression; var bindings = new List <MemberBinding>(); for (int i = 0; i < newExpression.Members.Count; i++) { var m = typeRuntime.GetMember(newExpression.Members[i].Name); var binding = Expression.Bind(m.Member, newExpression.Arguments[i].Type != m.DataType ? Expression.Convert(newExpression.Arguments[i], m.DataType) : newExpression.Arguments[i]); bindings.Add(binding); } foreach (var m in typeRuntime.KeyMembers) { var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member); var binding = Expression.Bind(m.Member, member); if (!bindings.Any(x => x.Member == m.Member)) { bindings.Add(binding); } } var newExpression2 = Expression.New(typeRuntime.Constructor.Constructor); expression = Expression.MemberInit(newExpression2, bindings); } // 解析查询以确定是否需要嵌套 dbQuery.Query.Select = new DbExpression(DbExpressionType.Select, expression); var cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, null); if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0) { if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0) { throw new XFrameworkException("Update<T>(Expression<Func<T, object>> updateExpression) require entity must have key column."); } // SET 字段 var visitor = new SQLiteUpdateExpressionVisitor(this, null, dbQuery, null); visitor.ParseCommand = this.ResolveSelectCommand; visitor.Write(builder); // WHERE部分 builder.AppendNewLine(); builder.Append("WHERE EXISTS"); visitor.VisitArgument(dbQuery.Query.Select.Expressions[0], true); } else { // 直接 SQL 的 UPDATE 语法 TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token); var visitor = new SQLiteUpdateExpressionVisitor(this, aliases, dbQuery, null); visitor.ParseCommand = this.ResolveSelectCommand; visitor.Write(builder); var visitor2 = new WhereExpressionVisitor(this, null, dbQuery.Query.Where); visitor2.Write(builder); } } builder.Append(';'); return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
// 创建 DELETE 命令 protected override Command ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQueryInfo, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); builder.Append("DELETE t0 FROM "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append(" t0 "); if (dQueryInfo.Entity != null) { if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0) { throw new XFrameworkException("Delete<T>(T value) require entity must have key column."); } object entity = dQueryInfo.Entity; builder.AppendNewLine(); builder.Append("WHERE "); foreach (var invoker in typeRuntime.KeyInvokers) { var column = invoker.Column; var value = invoker.Invoke(entity); var seg = this.DbValue.GetSqlValue(value, token, column); builder.AppendMember("t0", invoker.Member.Name); builder.Append(" = "); builder.Append(seg); builder.Append(" AND "); } builder.Length -= 5; } else if (dQueryInfo.SelectInfo != null) { TableAliasCache aliases = this.PrepareAlias <T>(dQueryInfo.SelectInfo, token); var cmd2 = new MappingCommand(this, aliases, token) { HasMany = dQueryInfo.SelectInfo.HasMany }; if (token != null && token.Extendsions == null) { token.Extendsions = new Dictionary <string, object>(); if (!token.Extendsions.ContainsKey("MySqlDelete")) { token.Extendsions.Add("MySqlDelete", null); } } ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.Joins); visitor.Write(cmd2.JoinFragment); visitor = new WhereExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.WhereExpression); visitor.Write(cmd2.WhereFragment); cmd2.AddNavMembers(visitor.NavMembers); builder.Append(cmd2.CommandText); } builder.Append(';'); return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }