// 创建 SELECT 命令 DbCommandDefinition ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, List <IDbDataParameter> parameters = null) { // 说明: // 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, DbQueryableInfo_Select <T> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null) { sQuery = innerQuery; } bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0; bool useStatis = sQuery.Statis != null; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0)); IDbQueryable dbQueryable = sQuery.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQuery); DbCommandDefinition_Select cmd = new DbCommandDefinition_Select(this, aliases, parameters) { HaveListNavigation = sQuery.HaveListNavigation }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; ISqlBuilder sf = null; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0"); 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(); } jf.Append("SELECT "); if (sQuery.HaveAny) { 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, sQuery.Statis, sQuery.GroupBy); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQuery.HaveDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!sQuery.HaveAny) { // SELECT 范围 var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery); if (sQuery.Skip > 0 && sQuery.Take == 0) { sf = this.CreateSqlBuilder(parameters); sf.Indent = jf.Indent + 1; visitor2.Write(sf); } else { visitor2.Write(jf); } cmd.Columns = visitor2.Columns; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); if (sf != null) { // 第一层嵌套 int index = 0; jf.AppendNewLine(); foreach (var entry in cmd.Columns) { jf.AppendMember("t0", entry.Key); jf.AppendAs(entry.Key); index += 1; if (index < cmd.Columns.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 (sQuery.OrderBy.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } jf.Append("ROW_NUMBER() OVER("); var visitor3 = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy); visitor3.Write(jf, false); cmd.AddNavMembers(visitor3.NavMembers); jf.Append(") Row_Number0"); } } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (sQuery.SubQueryInfo != null) { // 子查询 jf.Append("("); var cmd2 = this.ParseSelectCommandImpl <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") t0 "); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(" t0 "); //if (dbQueryable.DbContext.NoLock && !string.IsNullOrEmpty(this.WidthNoLock)) jf.Append(this.WidthNoLock); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQuery.OrderBy.Count > 0 && useOrderBy)// && !groupByPaging) { visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 // LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。 // LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。 // 初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。 // Limit n,-1 语法不支持,使用ROW_Number()语法代替 if (sQuery.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", wf.GetSqlValue(sQuery.Take)); wf.AppendFormat(" OFFSET {0}", wf.GetSqlValue(sQuery.Skip)); } #endregion #region 嵌套查询 if (useStatis && useNesting) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(" ) t0"); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0)) { cmd.Convergence(); visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (sQuery.Union != null && sQuery.Union.Count > 0) { cmd.Convergence(); for (int index = 0; index < sQuery.Union.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } DbCommandDefinition cmd2 = this.ParseSelectCommandImpl <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters); jf.Append(cmd2.CommandText); } } #endregion #region 分页查询 if (sf != null) { // 合并 WHERE cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") t0"); jf.AppendNewLine(); jf.Append("WHERE "); if (sQuery.Skip > 0) { jf.Append("t0.Row_Number0 > "); jf.Append(jf.GetSqlValue(sQuery.Skip)); } } #endregion #region Any 子句 // 'Any' 子句 if (sQuery.HaveAny) { // 产生 WHERE 子句 cmd.Convergence(); // 如果没有分页,则显式指定只查一笔记录 if (sQuery.Take == 0 && sQuery.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 0,1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") t0"); } #endregion return(cmd); }
// 创建 SELECT 命令 protected override DbCommandDefinition ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQuery, int indent = 0, bool isOuter = true, List <IDbDataParameter> parameters = null) { // 说明: // 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, DbQueryableInfo_Select <T> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null) { sQuery = innerQuery; } bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0; bool useStatis = sQuery.Statis != null; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0)); IDbQueryable dbQueryable = sQuery.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQuery); DbCommandDefinition_Select cmd = new DbCommandDefinition_Select(this, aliases, parameters) { HaveListNavigation = sQuery.HaveListNavigation }; 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 StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0"); 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 (sQuery.HaveAny) { 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, sQuery.Statis, sQuery.GroupBy); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQuery.HaveDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!sQuery.HaveAny) { // SELECT 范围 ISqlBuilder sf = this.CreateSqlBuilder(jf.Parameters); sf.Indent = jf.Indent + ((sQuery.Skip > 0 || sQuery.Take > 0) ? 2 : 0); (sf as OracleSqlBuilder).IsOuter = (sQuery.Skip > 0 || sQuery.Take > 0) ? false : (jf as OracleSqlBuilder).IsOuter; var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery); visitor2.Write(sf); cmd.Columns = visitor2.Columns; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); // 分页,产生两层嵌套 if (sQuery.Skip > 0 || sQuery.Take > 0) { // 第一层嵌套 int index = 0; jf.AppendNewLine(); foreach (var entry in cmd.Columns) { jf.AppendMember("t0", entry.Key); jf.AppendAs(entry.Key); index += 1; if (index < cmd.Columns.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 entry in cmd.Columns) { jf.AppendMember("t0", entry.Key); jf.AppendAs(entry.Key); 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 (sQuery.SubQueryInfo != null) { // 子查询 jf.Append('('); DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") t0 "); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(" t0 "); //if (dbQueryable.DbContext.NoLock && !string.IsNullOrEmpty(this.WidthNoLock)) jf.Append(this.WidthNoLock); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQuery.OrderBy.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 嵌套查询 if (useStatis && useNesting) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(" ) t0"); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0)) { // OrderBy("a.CloudServer.CloudServerName"); cmd.Convergence(); visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (sQuery.Union != null && sQuery.Union.Count > 0) { cmd.Convergence(); for (int index = 0; index < sQuery.Union.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters); jf.Append(cmd2.CommandText); } } #endregion #region 分页查询 if (sQuery.Take > 0 || sQuery.Skip > 0) { // 合并 WHERE cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") t0"); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") t0"); jf.AppendNewLine(); jf.Append("WHERE "); if (sQuery.Skip > 0) { jf.Append("t0.Row_Number0 > "); jf.Append(jf.GetSqlValue(sQuery.Skip)); } if (sQuery.Take > 0) { if (sQuery.Skip > 0) { jf.Append(" AND "); } jf.Append("t0.Row_Number0 <= "); jf.Append(jf.GetSqlValue((sQuery.Skip + sQuery.Take))); } } #endregion #region Any 子句 // 'Any' 子句 if (sQuery.HaveAny) { // 产生 WHERE 子句 cmd.Convergence(); // 如果没有分页,则显式指定只查一笔记录 if (sQuery.Take == 0 && sQuery.Skip == 0) { if (sQuery.Where != null && sQuery.Where.Expressions != null) { jf.Append(" AND ROWNUM <= 1"); } else { jf.AppendNewLine(); jf.Append("WHERE ROWNUM <= 1"); } } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") t0"); } #endregion return(cmd); }
// 创建 SELECT 命令 protected override DbCommandDefinition ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQuery, int indent = 0, bool isOuter = true, List <IDbDataParameter> parameters = null) { // 说明: // 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> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null) { sQuery = innerQuery; } bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0; bool useStatis = sQuery.Statis != null; // 分组分页 bool groupByPaging = sQuery.GroupBy != null && sQuery.Skip > 0; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0)); IDbQueryable dbQueryable = sQuery.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQuery); DbCommandDefinition_Select cmd = new DbCommandDefinition_Select(this, aliases, parameters) { HaveListNavigation = sQuery.HaveListNavigation }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0"); 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 (sQuery.HaveAny) { jf.Append("IF EXISTS("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); } jf.Append("SELECT "); if (useStatis && !useNesting) { // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQuery.HaveDistinct) { jf.Append("DISTINCT "); } // TOP 子句 if (sQuery.Take > 0 && sQuery.Skip == 0) { jf.AppendFormat("TOP({0})", jf.GetSqlValue(sQuery.Take)); } // Any if (sQuery.HaveAny) { jf.Append("TOP 1 1"); } #region 字段 if (!sQuery.HaveAny) { // SELECT 范围 var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery); visitor2.Write(jf); cmd.Columns = visitor2.Columns; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (sQuery.SubQueryInfo != null) { // 子查询 jf.Append('('); DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") t0 "); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(" t0 "); SqlDbContext context = (SqlDbContext)dbQueryable.DbContext; if (context.NoLock && !string.IsNullOrEmpty(this._widthNoLock)) { jf.Append(this._widthNoLock); } } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQuery.OrderBy.Count > 0 && useOrderBy)// && !groupByPaging) { visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 if (sQuery.Skip > 0) { if (sQuery.OrderBy.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } wf.AppendNewLine(); wf.Append("OFFSET "); wf.Append(wf.GetSqlValue(sQuery.Skip)); wf.Append(" ROWS"); if (sQuery.Take > 0) { wf.Append(" FETCH NEXT "); wf.Append(wf.GetSqlValue(sQuery.Take)); wf.Append(" ROWS ONLY "); } } #endregion #region 嵌套查询 if (useStatis && useNesting) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(" ) t0"); } #endregion #region 嵌套导航 if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0)) { // TODO Include 从表,没分页,OrderBy 报错 // OrderBy("a.CloudServer.CloudServerName"); cmd.Convergence(); visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (sQuery.Union != null && sQuery.Union.Count > 0) { cmd.Convergence(); for (int index = 0; index < sQuery.Union.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters); jf.Append(cmd2.CommandText); } } #endregion #region Any 子句 // 'Any' 子句 if (sQuery.HaveAny) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") SELECT 1 ELSE SELECT 0"); } #endregion return(cmd); }