示例#1
0
        private string PrepareAncestorSelectSql(QxCompilationContext ctx, IEnumerable <QueryexBase> selectAtoms)
        {
            List <string> selects = new List <string>(selectAtoms.Count());

            foreach (var exp in selectAtoms)
            {
                var(sql, type, _) = exp.CompileNative(ctx);
                if (type == QxType.Boolean || type == QxType.Geography)
                {
                    // Those three types are not supported for loading into C#
                    throw new QueryException($"A select expression {exp} cannot have a type {type}.");
                }
                else if (type == QxType.HierarchyId)
                {
                    continue; // In the ancestors
                }
                else
                {
                    sql = sql.DeBracket(); // e.g.: [P].[Name] AS [C3]
                    selects.Add(sql);
                }
            }

            string selectSql = $"SELECT DISTINCT " + string.Join(", ", selects);

            return(selectSql);
        }
示例#2
0
        /// <summary>
        /// Prepares the ORDER BY clause of the SQL query using the <see cref="OrderBy"/> argument: ORDER BY ABC
        /// </summary>
        private string PrepareOrderBySql(QxCompilationContext ctx)
        {
            var orderByAtomsCount = OrderBy?.Count() ?? 0;

            if (orderByAtomsCount == 0)
            {
                return("");
            }

            var orderbys = new List <string>(orderByAtomsCount);

            foreach (var expression in OrderBy)
            {
                string orderby = expression.CompileToNonBoolean(ctx);
                if (expression.IsDescending)
                {
                    orderby += " DESC";
                }
                else
                {
                    orderby += " ASC";
                }

                orderbys.Add(orderby);
            }

            // If Entity has Id, we always make sure Id is in the OrderBy
            // clause to guarantee a deterministic order when paging
            if (ctx.Joins.EntityDescriptor.HasId)
            {
示例#3
0
        /// <summary>
        /// If this query has a principal query, this method returns the SQL of the principal query in the form of an
        /// INNER JOIN to restrict the result to those entities that are related to the principal query
        /// </summary>
        private string PreparePrincipalQuerySql(QxCompilationContext ctx)
        {
            string principalQuerySql = "";

            if (PrincipalQuery != null)
            {
                // Get the inner sql and append 4 spaces before each line for aesthetics
                string innerSql = PrincipalQuery.PrepareStatementAsPrincipal(
                    ctx.Sources,
                    ctx.Variables,
                    ctx.Parameters,
                    IsAncestorExpand,
                    PathToCollectionPropertyInPrincipal,
                    ctx.UserId,
                    ctx.Today);

                innerSql = innerSql.IndentLines();

                if (IsAncestorExpand)
                {
                    principalQuerySql = $@"INNER JOIN (
{innerSql}
) As [S] ON [S].[Node].IsDescendantOf([P].[Node]) = 1 AND [S].[Node] <> [P].[Node]";
                }
                else
                {
                    // This works since when there is a principal query, there is no WHERE clause
                    principalQuerySql = $@"WHERE [P].[{ForeignKeyToPrincipalQuery}] IN (
{innerSql}
)";
                }
            }

            return(principalQuerySql);
        }
示例#4
0
        /// <summary>
        /// Prepares the ORDER BY clause of the SQL query using the <see cref="OrderBy"/> argument: ORDER BY ABC
        /// </summary>
        private string PrepareOrderBySql(QxCompilationContext ctx)
        {
            var orderByAtomsCount = OrderBy?.Count() ?? 0;

            if (orderByAtomsCount == 0)
            {
                return("");
            }

            List <string> orderbys = new List <string>(orderByAtomsCount);

            foreach (var expression in OrderBy)
            {
                string orderby = expression.CompileToNonBoolean(ctx);
                if (expression.IsDescending)
                {
                    orderby += " DESC";
                }
                else
                {
                    orderby += " ASC";
                }

                orderbys.Add(orderby);
            }

            return("ORDER BY " + string.Join(", ", orderbys));
        }
示例#5
0
        /// <summary>
        /// Create a <see cref="SqlEntityStatement"/> that contains all the needed information to execute the query
        /// as an INNER JOIN of any one of the other queries that uses it as a principal query
        /// IMPORTANT: Calling this method will keep a permanent cache of some parts of the result, therefore
        /// if the arguments need to change after that, a new <see cref="EntityQueryInternal"/> must be created
        /// </summary>
        private string PrepareStatementAsPrincipal(
            Func <Type, string> sources,
            SqlStatementVariables vars,
            SqlStatementParameters ps,
            bool isAncestorExpand,
            ArraySegment <string> pathToCollectionProperty,
            int userId,
            DateTime?userToday,
            DateTimeOffset?userNow)
        {
            // (1) Prepare the JOIN's clause
            JoinTrie joinTrie = PrepareJoin(pathToCollectionProperty);
            var      joinSql  = joinTrie.GetSql(sources);

            // Compilation context
            var today = userToday ?? DateTime.Today;
            var now   = userNow ?? DateTimeOffset.Now;
            var ctx   = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);

            // (2) Prepare the SELECT clause
            SqlSelectClause selectClause = PrepareSelectAsPrincipal(joinTrie, pathToCollectionProperty, isAncestorExpand);
            var             selectSql    = selectClause.ToSql(IsAncestorExpand);

            // (3) Prepare the inner join with the principal query (if any)
            string principalQuerySql = PreparePrincipalQuerySql(ctx);

            // (4) Prepare the WHERE clause
            string whereSql = PrepareWhereSql(ctx);

            // (5) Prepare the ORDERBY clause
            string orderbySql = PrepareOrderBySql(ctx);

            // (6) Prepare the OFFSET and FETCH clauses
            string offsetFetchSql = PrepareOffsetFetch();

            if (string.IsNullOrWhiteSpace(offsetFetchSql))
            {
                // In a principal query, order by is only added if there is an offset-fetch (usually in the root query)
                orderbySql = "";
            }

            // (7) Finally put together the final SQL statement and return it
            string sql = QueryTools.CombineSql(
                selectSql: selectSql,
                joinSql: joinSql,
                principalQuerySql: principalQuerySql,
                whereSql: whereSql,
                orderbySql: orderbySql,
                offsetFetchSql: offsetFetchSql,
                groupbySql: null,
                havingSql: null,
                selectFromTempSql: null
                );

            // (8) Return the result
            return(sql);
        }
示例#6
0
        /// <summary>
        /// Create a <see cref="SqlStatement"/> that contains all the needed information to execute the query against a SQL Server database and load and hydrate the entities
        /// IMPORTANT: Calling this method will keep a permanent cache of some parts of the result, therefore if the arguments need to change after
        /// that, a new <see cref="QueryInternal"/> must be created
        /// </summary>
        public SqlStatement PrepareStatement(
            Func <Type, string> sources,
            SqlStatementVariables vars,
            SqlStatementParameters ps,
            int userId,
            DateTime?userToday)
        {
            // (1) Prepare the JOIN's clause
            var joinTrie = PrepareJoin();
            var joinSql  = joinTrie.GetSql(sources, FromSql);

            // Compilation context
            var today = userToday ?? DateTime.Today;
            var now   = DateTimeOffset.Now;
            var ctx   = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);

            // (2) Prepare the SELECT clause
            SqlSelectClause selectClause = PrepareSelect(joinTrie);
            var             selectSql    = selectClause.ToSql(IsAncestorExpand);

            // (3) Prepare the inner join with the principal query (if any)
            string principalQuerySql = PreparePrincipalQuerySql(ctx);

            // (4) Prepare the WHERE clause
            string whereSql = PrepareWhereSql(ctx);

            // (5) Prepare the ORDERBY clause
            string orderbySql = PrepareOrderBySql(ctx);

            // (6) Prepare the OFFSET and FETCH clauses
            string offsetFetchSql = PrepareOffsetFetch();

            // (7) Finally put together the final SQL statement and return it
            string sql = QueryTools.CombineSql(
                selectSql: selectSql,
                joinSql: joinSql,
                principalQuerySql: principalQuerySql,
                whereSql: whereSql,
                orderbySql: orderbySql,
                offsetFetchSql: offsetFetchSql,
                groupbySql: null,
                havingSql: null,
                selectFromTempSql: null
                );

            // (8) Return the result
            return(new SqlStatement
            {
                Sql = sql,
                ResultDescriptor = ResultDescriptor,
                ColumnMap = selectClause.GetColumnMap(),
                Query = this,
            });
        }
示例#7
0
        /// <summary>
        /// Prepares the WHERE clause of the SQL query from the <see cref="_filterExp"/> argument: WHERE ABC
        /// </summary>
        private string PrepareWhereSql(QxCompilationContext ctx)
        {
            string whereSql = _filter?.Expression?.CompileToBoolean(ctx)?.DeBracket();

            // Add the "WHERE" keyword
            if (!string.IsNullOrEmpty(whereSql))
            {
                whereSql = "WHERE " + whereSql;
            }

            return(whereSql);
        }
示例#8
0
        // Functionality

        public string PrepareCountSql(
            Func <Type, string> sources,
            SqlStatementVariables vars,
            SqlStatementParameters ps,
            int userId,
            DateTime?userToday,
            DateTimeOffset?userNow,
            int maxCount)
        {
            // (1) Prepare the JOIN's clause
            var joinTrie = PrepareJoin();
            var joinSql  = joinTrie.GetSql(sources);

            // Compilation context
            var today = userToday ?? DateTime.Today;
            var now   = userNow ?? DateTimeOffset.Now;
            var ctx   = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);

            // (2) Prepare the SELECT clause
            string selectSql = maxCount > 0 ? $"SELECT TOP {maxCount} [P].*" : "SELECT [P].*";

            // (3) Prepare the WHERE clause
            string whereSql = PrepareWhereSql(ctx);

            // (4) Finally put together the final SQL statement and return it
            string sql = QueryTools.CombineSql(
                selectSql: selectSql,
                joinSql: joinSql,
                principalQuerySql: null,
                whereSql: whereSql,
                orderbySql: null,
                offsetFetchSql: null,
                groupbySql: null,
                havingSql: null,
                selectFromTempSql: null
                );

            sql = $@"SELECT COUNT(*) As [Count] FROM (
{sql.IndentLines()}
) AS [Q]";

            return(sql);
        }
示例#9
0
        private (string select, int count) PrepareSelectSql(QxCompilationContext ctx)
        {
            var selects = new List <string>(_select.Count());

            foreach (var exp in _select)
            {
                var(sql, type, _) = exp.CompileNative(ctx);
                if (type == QxType.Boolean || type == QxType.HierarchyId || type == QxType.Geography)
                {
                    // Those three types are not supported for loading into C#
                    throw new QueryException($"A select expression {exp} cannot have a type {type}.");
                }
                else
                {
                    selects.Add(sql.DeBracket());// e.g.: [P].[Name]
                }
            }

            var columnCount = selects.Count; // The columns added later will not be loaded
            var selectSql   = $"SELECT " + string.Join(", ", selects);

            return(selectSql, columnCount);
        }
示例#10
0
        /// <summary>
        /// Prepares the WHERE clause of the SQL query from the <see cref="Filter"/> argument: WHERE ABC
        /// </summary>
        private string PrepareWhereSql(QxCompilationContext ctx)
        {
            var ps = ctx.Parameters;

            // WHERE is cached
            if (_cachedWhere == null)
            {
                string whereFilter       = null;
                string whereInIds        = null;
                string whereInParentIds  = null;
                string whereInPropValues = null;

                if (Filter != null)
                {
                    whereFilter = Filter.Expression.CompileToBoolean(ctx);
                }

                if (Ids != null && Ids.Any())
                {
                    if (Ids.Count() == 1)
                    {
                        string paramName = ps.AddParameter(Ids.Single());
                        whereInIds = $"[P].[Id] = @{paramName}";
                    }
                    else
                    {
                        var isIntKey    = KeyType == KeyType.Int; // (Nullable.GetUnderlyingType(KeyType) ?? KeyType) == typeof(int);
                        var isStringKey = KeyType == KeyType.String;

                        // Prepare the ids table
                        DataTable idsTable = isIntKey ? RepositoryUtilities.DataTable(Ids.Select(id => new IdListItem {
                            Id = (int)id
                        }))
                            : isStringKey?RepositoryUtilities.DataTable(Ids.Select(id => new StringListItem {
                            Id = id.ToString()
                        }))
                                                 : throw new InvalidOperationException("Only string and Integer Ids are supported");

                        //
                        var idsTvp = new SqlParameter("@Ids", idsTable)
                        {
                            TypeName = isIntKey ? "[dbo].[IdList]" : isStringKey ? "[dbo].[StringList]" : throw new InvalidOperationException("Only string and Integer Ids are supported"),
                                             SqlDbType = SqlDbType.Structured
                        };

                        ps.AddParameter(idsTvp);
                        whereInIds = $"[P].[Id] IN (SELECT Id FROM @Ids)";
                    }
                }

                if (ParentIds != null)
                {
                    if (!ParentIds.Any())
                    {
                        if (IncludeRoots)
                        {
                            whereInParentIds = $"[P].[ParentId] IS NULL";
                        }
                    }
                    else if (ParentIds.Count() == 1)
                    {
                        string paramName = ps.AddParameter(ParentIds.Single());
                        whereInParentIds = $"[P].[ParentId] = @{paramName}";
                        if (IncludeRoots)
                        {
                            whereInParentIds += " OR [P].[ParentId] IS NULL";
                        }
                    }
                    else
                    {
                        var isIntKey    = KeyType == KeyType.Int; // (Nullable.GetUnderlyingType(KeyType) ?? KeyType) == typeof(int);
                        var isStringKey = KeyType == KeyType.String;

                        // Prepare the data table
                        var    parentIdsTable = new DataTable();
                        string idName         = "Id";
                        var    idType         = KeyType switch
                        {
                            KeyType.String => typeof(string),
                            KeyType.Int => typeof(int),
                            _ => throw new InvalidOperationException("Bug: Only string and Integer ParentIds are supported"),
                        };

                        var column = new DataColumn(idName, idType);
                        if (isStringKey)
                        {
                            column.MaxLength = 450; // Just for performance
                        }
                        parentIdsTable.Columns.Add(column);
                        foreach (var id in ParentIds.Where(e => e != null))
                        {
                            DataRow row = parentIdsTable.NewRow();
                            row[idName] = id;
                            parentIdsTable.Rows.Add(row);
                        }

                        // Prepare the TVP
                        var parentIdsTvp = new SqlParameter("@ParentIds", parentIdsTable)
                        {
                            TypeName = KeyType == KeyType.Int ? "[dbo].[IdList]" : KeyType == KeyType.String ? "[dbo].[StringList]" : throw new InvalidOperationException("Bug: Only string and Integer ParentIds are supported"),
                                             SqlDbType = SqlDbType.Structured
                        };

                        ps.AddParameter(parentIdsTvp);
                        whereInParentIds = $"[P].[ParentId] IN (SELECT Id FROM @ParentIds)";
                        if (IncludeRoots)
                        {
                            whereInParentIds += " OR [P].[ParentId] IS NULL";
                        }
                    }
                }

                if (!string.IsNullOrWhiteSpace(PropName) && Values != null && Values.Any())
                {
                    var propDesc = ResultDescriptor.Property(PropName);
                    var propType = propDesc.Type;

                    var isIntKey    = propType == typeof(int?) || propType == typeof(int);
                    var isStringKey = propType == typeof(string);

                    // Prepare the ids table
                    DataTable valuesTable =
                        isStringKey ? RepositoryUtilities.DataTable(Values.Select(id => new StringListItem {
                        Id = id.ToString()
                    })) :
                        isIntKey?RepositoryUtilities.DataTable(Values.Select(id => new IdListItem {
                        Id = (int)id
                    })) :
                        throw new InvalidOperationException("Only string and Integer Ids are supported");

                    var valuesTvp = new SqlParameter("@Values", valuesTable)
                    {
                        TypeName = isIntKey ? "[dbo].[IdList]" : isStringKey ? "[dbo].[StringList]" : throw new InvalidOperationException("Only string and Integer values are supported"),
                                         SqlDbType = SqlDbType.Structured
                    };

                    ps.AddParameter(valuesTvp);
                    whereInPropValues = $"[P].[{propDesc.Name}] IN (SELECT Id FROM @Values)";
                }

                // The final WHERE clause (if any)
                string whereSql = "";

                var clauses = new List <string> {
                    whereFilter, whereInIds, whereInParentIds, whereInPropValues
                }.Where(e => !string.IsNullOrWhiteSpace(e));
                if (clauses.Any())
                {
                    whereSql = clauses.Aggregate((c1, c2) => $"{c1}) AND ({c2}");
                    whereSql = $"WHERE ({whereSql})";
                }

                _cachedWhere = whereSql;
            }

            return(_cachedWhere);
        }
示例#11
0
        private async Task <(List <DynamicRow>, int count)> ToListAndCountInnerAsync(bool includeCount, int maxCount, QueryContext ctx2, CancellationToken cancellation)
        {
            var queryArgs = await _factory(cancellation);

            var connString = queryArgs.ConnectionString;
            var sources    = queryArgs.Sources;
            var loader     = queryArgs.Loader;

            var userId    = ctx2.UserId;
            var userToday = ctx2.UserToday;

            // ------------------------ Validation Step

            // SELECT Validation
            if (_select == null)
            {
                string message = $"The select argument is required";
                throw new InvalidOperationException(message);
            }

            // Make sure that measures are well formed: every column access is wrapped inside an aggregation function
            foreach (var exp in _select)
            {
                var aggregation = exp.Aggregations().FirstOrDefault();
                if (aggregation != null)
                {
                    throw new QueryException($"Select cannot contain an aggregation function like: {aggregation.Name}.");
                }
            }

            // ORDER BY Validation
            if (_orderby != null)
            {
                foreach (var exp in _orderby)
                {
                    // Order by cannot be a constant
                    if (!exp.ContainsAggregations && !exp.ContainsColumnAccesses)
                    {
                        throw new QueryException("OrderBy parameter cannot be a constant, every orderby expression must contain either an aggregation or a column access.");
                    }
                }
            }

            // FILTER Validation
            if (_filter != null)
            {
                var conditionWithAggregation = _filter.Expression.Aggregations().FirstOrDefault();
                if (conditionWithAggregation != null)
                {
                    throw new QueryException($"Filter contains a condition with an aggregation function: {conditionWithAggregation}.");
                }
            }

            // ------------------------ Preparation Step

            // If all is good Prepare some universal variables and parameters
            var vars  = new SqlStatementVariables();
            var ps    = new SqlStatementParameters();
            var today = userToday ?? DateTime.Today;
            var now   = DateTimeOffset.Now;

            // ------------------------ The SQL Generation Step

            // (1) Prepare the JOIN's clause
            var joinTrie = PrepareJoin();
            var joinSql  = joinTrie.GetSql(sources);

            // Compilation context
            var ctx = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);

            // (2) Prepare all the SQL clauses
            var(selectSql, columnCount) = PrepareSelectSql(ctx);
            string whereSql       = PrepareWhereSql(ctx);
            string orderbySql     = PrepareOrderBySql(ctx);
            string offsetFetchSql = PrepareOffsetFetch();

            // (3) Put together the final SQL statement and return it
            string sql = QueryTools.CombineSql(
                selectSql: selectSql,
                joinSql: joinSql,
                principalQuerySql: null,
                whereSql: whereSql,
                orderbySql: orderbySql,
                offsetFetchSql: offsetFetchSql,
                groupbySql: null,
                havingSql: null,
                selectFromTempSql: null
                );

            // ------------------------ Prepare the Count SQL
            string countSql = null;

            if (includeCount)
            {
                string countSelectSql = maxCount > 0 ? $"SELECT TOP {maxCount} [P].*" : "SELECT [P].*";

                countSql = QueryTools.CombineSql(
                    selectSql: countSelectSql,
                    joinSql: joinSql,
                    principalQuerySql: null,
                    whereSql: whereSql,
                    orderbySql: null,
                    offsetFetchSql: null,
                    groupbySql: null,
                    havingSql: null,
                    selectFromTempSql: null
                    );

                countSql = $@"SELECT COUNT(*) As [Count] FROM (
{countSql.IndentLines()}
) AS [Q]";
            }

            // ------------------------ Execute SQL and return Result

            var principalStatement = new SqlDynamicStatement(sql, columnCount);
            var args = new DynamicLoaderArguments
            {
                CountSql                     = countSql, // No counting in aggregate functions
                PrincipalStatement           = principalStatement,
                DimensionAncestorsStatements = null,     // No ancestors
                Variables                    = vars,
                Parameters                   = ps,
            };

            var result = await loader.LoadDynamic(connString, args, cancellation);

            return(result.Rows, result.Count);
        }
示例#12
0
 public override (string, QxType, QxNullity) CompileNative(QxCompilationContext ctx)
 {
     return("NULL", QxType.Null, QxNullity.Null);
 }
示例#13
0
        public async Task <(List <DynamicRow> result, IEnumerable <TreeDimensionResult> trees)> ToListAsync(CancellationToken cancellation)
        {
            var queryArgs = await _factory(cancellation);

            var conn      = queryArgs.Connection;
            var sources   = queryArgs.Sources;
            var userId    = queryArgs.UserId;
            var userToday = queryArgs.UserToday;
            var localizer = queryArgs.Localizer;
            var logger    = queryArgs.Logger;

            // ------------------------ Validation Step

            // SELECT Validation
            if (_select == null)
            {
                string message = $"The select argument is required";
                throw new InvalidOperationException(message);
            }

            // Make sure that measures are well formed: every column access is wrapped inside an aggregation function
            foreach (var exp in _select)
            {
                if (exp.ContainsAggregations) // This is a measure
                {
                    // Every column access must descend from an aggregation function
                    var exposedColumnAccess = exp.UnaggregatedColumnAccesses().FirstOrDefault();
                    if (exposedColumnAccess != null)
                    {
                        throw new QueryException($"Select parameter contains a measure with a column access {exposedColumnAccess} that is not included within an aggregation.");
                    }
                }
            }

            // ORDER BY Validation
            if (_orderby != null)
            {
                foreach (var exp in _orderby)
                {
                    // Order by cannot be a constant
                    if (!exp.ContainsAggregations && !exp.ContainsColumnAccesses)
                    {
                        throw new QueryException("OrderBy parameter cannot be a constant, every orderby expression must contain either an aggregation or a column access.");
                    }
                }
            }

            // FILTER Validation
            if (_filter != null)
            {
                var conditionWithAggregation = _filter.Expression.Aggregations().FirstOrDefault();
                if (conditionWithAggregation != null)
                {
                    throw new QueryException($"Filter contains a condition with an aggregation function: {conditionWithAggregation}");
                }
            }


            // HAVING Validation
            if (_having != null)
            {
                // Every column access must descend from an aggregation function
                var exposedColumnAccess = _having.Expression.UnaggregatedColumnAccesses().FirstOrDefault();
                if (exposedColumnAccess != null)
                {
                    throw new QueryException($"Having parameter contains a column access {exposedColumnAccess} that is not included within an aggregation.");
                }
            }

            // ------------------------ Preparation Step

            // If all is good Prepare some universal variables and parameters
            var vars  = new SqlStatementVariables();
            var ps    = new SqlStatementParameters();
            var today = userToday ?? DateTime.Today;
            var now   = DateTimeOffset.Now;

            // ------------------------ Tree Analysis Step

            // By convention if A.B.Id AND A.B.ParentId are both in the select expression,
            // then this is a tree dimension and we return all the ancestors of A.B,
            // What do we select for the ancestors? All non-aggregated expressions in
            // the original select that contain column accesses exclusively starting with A.B
            var additionalNodeSelects = new List <QueryexColumnAccess>();
            var ancestorsStatements   = new List <DimensionAncestorsStatement>();
            {
                // Find all column access atoms that terminate with ParentId, those are the potential tree dimensions
                var parentIdSelects = _select
                                      .Where(e => e is QueryexColumnAccess ca && ca.Property == "ParentId")
                                      .Cast <QueryexColumnAccess>();

                foreach (var parentIdSelect in parentIdSelects)
                {
                    var pathToTreeEntity = parentIdSelect.Path; // A.B

                    // Confirm it's a tree dimension
                    var idSelect = _select.FirstOrDefault(e => e is QueryexColumnAccess ca && ca.Property == "Id" && ca.PathEquals(pathToTreeEntity));
                    if (idSelect != null)
                    {
                        // Prepare the Join Trie
                        var treeType = TypeDescriptor.Get <T>();
                        foreach (var step in pathToTreeEntity)
                        {
                            treeType = treeType.NavigationProperty(step)?.TypeDescriptor ??
                                       throw new QueryException($"Property {step} does not exist on type {treeType.Name}.");
                        }

                        // Create or Get the name of the Node column
                        string nodeColumnName = NodeColumnName(additionalNodeSelects.Count);
                        additionalNodeSelects.Add(new QueryexColumnAccess(pathToTreeEntity, "Node")); // Tell the principal query to include this node

                        // Get all atoms that contain column accesses exclusively starting with A.B
                        var principalSelectsWithMatchingPrefix = _select
                                                                 .Where(exp => exp.ColumnAccesses().All(ca => ca.PathStartsWith(pathToTreeEntity)));

                        // Calculate the target indices
                        var targetIndices = principalSelectsWithMatchingPrefix
                                            .Select(exp => SelectIndexDictionary[exp]);

                        // Remove the prefix from all column accesses
                        var ancestorSelects = principalSelectsWithMatchingPrefix
                                              .Select(exp => exp.Clone(prefixToRemove: pathToTreeEntity));

                        var allPaths = ancestorSelects.SelectMany(e => e.ColumnAccesses()).Select(e => e.Path);
                        var joinTrie = JoinTrie.Make(treeType, allPaths);
                        var joinSql  = joinTrie.GetSql(sources, fromSql: null);

                        // Prepare the Context
                        var ctx = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);

                        // Prepare the SQL components
                        var selectSql         = PrepareAncestorSelectSql(ctx, ancestorSelects);
                        var principalQuerySql = PreparePrincipalQuerySql(nodeColumnName);

                        // Combine the SQL components
                        string sql = QueryTools.CombineSql(
                            selectSql: selectSql,
                            joinSql: joinSql,
                            principalQuerySql: principalQuerySql,
                            whereSql: null,
                            orderbySql: null,
                            offsetFetchSql: null,
                            groupbySql: null,
                            havingSql: null,
                            selectFromTempSql: null);

                        // Get the index of the id select
                        int idIndex = SelectIndexDictionary[idSelect];

                        // Create and add the statement object
                        var statement = new DimensionAncestorsStatement(idIndex, sql, targetIndices);
                        ancestorsStatements.Add(statement);
                    }
                }
            }


            // ------------------------ The SQL Generation Step

            // (1) Prepare the JOIN's clause
            var principalJoinTrie = PreparePrincipalJoin();
            var principalJoinSql  = principalJoinTrie.GetSql(sources, fromSql: null);

            // Compilation context
            var principalCtx = new QxCompilationContext(principalJoinTrie, sources, vars, ps, today, now, userId);

            // (2) Prepare all the SQL clauses
            var(principalSelectSql, principalGroupbySql, principalColumnCount) = PreparePrincipalSelectAndGroupBySql(principalCtx, additionalNodeSelects);
            string principalWhereSql          = PreparePrincipalWhereSql(principalCtx);
            string principalHavingSql         = PreparePrincipalHavingSql(principalCtx);
            string principalOrderbySql        = PreparePrincipalOrderBySql();
            string principalSelectFromTempSql = PrepareSelectFromTempSql();

            // (3) Put together the final SQL statement and return it
            string principalSql = QueryTools.CombineSql(
                selectSql: principalSelectSql,
                joinSql: principalJoinSql,
                principalQuerySql: null,
                whereSql: principalWhereSql,
                orderbySql: principalOrderbySql,
                offsetFetchSql: null,
                groupbySql: principalGroupbySql,
                havingSql: principalHavingSql,
                selectFromTempSql: principalSelectFromTempSql
                );

            // ------------------------ Execute SQL and return Result
            var principalStatement = new SqlDynamicStatement(principalSql, principalColumnCount);

            var(result, trees, _) = await EntityLoader.LoadDynamicStatement(
                principalStatement : principalStatement,
                dimAncestorsStatements : ancestorsStatements,
                includeCount : false,
                vars : vars,
                ps : ps,
                conn : conn,
                logger : logger,
                cancellation : cancellation);

            return(result, trees);
        }
示例#14
0
 /// <summary>
 /// Create a new instance of <see cref="SqlSelectGroupByClause"/> using the supplied column definitions
 /// </summary>
 public SqlSelectGroupByClause(List <QueryexBase> columns, int top, QxCompilationContext ctx)
 {
     _top     = top;
     _ctx     = ctx;
     _columns = columns ?? throw new ArgumentNullException(nameof(columns));
 }
示例#15
0
        public override (string, QxType, QxNullity) CompileNative(QxCompilationContext ctx)
        {
            string sql = Value ? "CAST(1 AS BIT)" : "CAST(0 AS BIT)";

            return(sql, QxType.Bit, QxNullity.NotNull);
        }