예제 #1
0
        // 创建 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);
        }
예제 #2
0
        // 创建 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);
        }
        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);
        }
예제 #4
0
        // 创建 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);
        }