Exemplo n.º 1
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);
        }
Exemplo n.º 2
0
        private async Task <(List <DynamicRow>, int count)> ToListAndCountInnerAsync(bool includeCount, int maxCount, 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)
            {
                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, fromSql: null);

            // 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
            if (includeCount)
            {
                string countSelectSql = maxCount > 0 ? $"SELECT TOP {maxCount} [P].*" : "SELECT [P].*";

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

                sql = $@"
{sql}

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

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

            var principalStatement = new SqlDynamicStatement(sql, columnCount);

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

            return(result, count);
        }