Beispiel #1
0
        /// <summary>
        /// Prepares the ORDER BY clause of the SQL query using the <see cref="OrderBy"/> argument: ORDER BY ABC
        /// </summary>
        private string PrepareOrderBy(JoinTree joinTree)
        {
            List <string> orderbys = new List <string>(OrderBy?.Count() ?? 0);

            if (OrderBy != null)
            {
                foreach (var atom in OrderBy)
                {
                    var join = joinTree[atom.Path];
                    if (join == null)
                    {
                        // Developer mistake
                        throw new InvalidOperationException($"The path '{string.Join('/', atom.Path)}' was not found in the joinTree");
                    }
                    var    symbol  = join.Symbol;
                    string orderby = $"[{symbol}].[{atom.Property}] {(atom.Desc ? "DESC" : "ASC")}";
                    orderbys.Add(orderby);
                }
            }

            string orderbySql = ""; // Default order by

            if (orderbys.Count > 0)
            {
                orderbySql = "ORDER BY " + string.Join(", ", orderbys);
            }

            return(orderbySql);
        }
        /// <summary>
        /// Prepares the ORDER BY clause of the SQL query using the <see cref="Select"/> argument: ORDER BY ABC
        /// </summary>
        private string PrepareOrderBy(JoinTree joinTree)
        {
            var orderByAtoms      = Select.Where(e => !string.IsNullOrEmpty(e.OrderDirection));
            var orderByAtomsCount = orderByAtoms.Count();

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

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

            foreach (var atom in orderByAtoms)
            {
                var join = joinTree[atom.Path];
                if (join == null)
                {
                    // Developer mistake
                    throw new InvalidOperationException($"The path '{string.Join('/', atom.Path)}' was not found in the joinTree");
                }
                var    symbol  = join.Symbol;
                string orderby = QueryTools.AtomSql(symbol, atom.Property, atom.Aggregation, atom.Modifier) + $" {atom.OrderDirection.ToUpper()}";
                orderbys.Add(orderby);
            }

            string orderbySql = ""; //  "ORDER BY Id DESC"; // Default order by

            if (orderbys.Count > 0)
            {
                orderbySql = "ORDER BY " + string.Join(", ", orderbys);
            }

            return(orderbySql);
        }
        /// <summary>
        /// Prepares a data structure containing all the information needed to construct the SELECT and GROUP BY clauses of the aggregate query
        /// </summary>
        private SqlSelectGroupByClause PrepareSelect(JoinTree joinTree)
        {
            var selects = new HashSet <(string Symbol, string PropName, string Aggregate, string Modifier)>(); // To ensure uniqueness
            var columns = new List <(string Symbol, ArraySegment <string> Path, string PropName, string Aggregate, string Modifier)>();

            foreach (var select in Select)
            {
                // Add the property
                string[] path        = select.Path;
                var      join        = joinTree[path];
                var      symbol      = join.Symbol;
                var      propName    = select.Property; // Can be null
                var      aggregation = select.Aggregation;
                var      modifier    = select.Modifier;

                // If the select doesn't exist: add it, or if it is not original and it shows up again as original: upgrade it
                if (selects.Add((symbol, propName, aggregation, modifier)))
                {
                    columns.Add((symbol, path, propName, aggregation, modifier));
                }
            }

            // Change the hash set to a list so that the order is well defined
            return(new SqlSelectGroupByClause(columns.ToList(), Top ?? 0));
        }
Beispiel #4
0
 /// <summary>
 /// Creates the SQL WHERE clause of the current query
 /// </summary>
 public string WhereSql(
     Func <Type, string> sources,
     JoinTree joins,
     SqlStatementParameters ps,
     int userId,
     DateTime?userToday)
 {
     return(PrepareWhere(sources, joins, ps, userId, userToday));
 }
        /// <summary>
        /// Prepares the WHERE clause of the SQL query from the <see cref="Filter"/> argument: WHERE ABC
        /// </summary>
        private string PrepareWhere(Func <Type, string> sources, JoinTree joinTree, SqlStatementParameters ps, int userId, DateTime?userToday)
        {
            string whereSql = QueryTools.FilterToSql(Filter, sources, ps, joinTree, userId, userToday) ?? "";

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

            return(whereSql);
        }
Beispiel #6
0
        /// <summary>
        /// Create a <see cref="SqlStatement"/> 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="QueryInternal"/> must be created
        /// </summary>
        private string PrepareStatementAsPrincipal(
            Func <Type, string> sources,
            SqlStatementParameters ps,
            bool isAncestorExpand,
            ArraySegment <string> pathToCollectionProperty,
            int userId,
            DateTime?userToday)
        {
            // (1) Prepare the JOIN's clause
            JoinTree joinTree = PrepareJoin(pathToCollectionProperty);
            var      joinSql  = joinTree.GetSql(sources, FromSql);

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

            // (3) Prepare the inner join with the principal query (if any)
            string principalQuerySql = PreparePrincipalQuery(sources, ps, userId, userToday);

            // (4) Prepare the WHERE clause
            string whereSql = PrepareWhere(sources, joinTree, ps, userId, userToday);

            // (5) Prepare the ORDERBY clause
            string orderbySql = PrepareOrderBy(joinTree);

            // (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
                );

            // (8) Return the result
            return(sql);
        }
        /// <summary>
        /// Prepares the join tree
        /// </summary>
        private JoinTree PrepareJoin()
        {
            // construct the join tree
            var allPaths = new List <string[]>();

            if (Select != null)
            {
                allPaths.AddRange(Select.Select(e => e.Path));
            }

            if (Filter != null)
            {
                allPaths.AddRange(Filter.Select(e => e.Path));
            }

            // This will represent the mapping from paths to symbols
            var joinTree = JoinTree.Make(ResultType, allPaths);

            return(joinTree);
        }
Beispiel #8
0
        /// <summary>
        /// Create the <see cref="JoinTree"/> from the paths in all the arguments
        /// </summary>
        private JoinTree PrepareJoin(ArraySegment <string>?pathToCollection = null)
        {
            // construct the join tree
            var allPaths = new List <string[]>();

            if (Select != null)
            {
                allPaths.AddRange(Select.Select(e => e.Path));
            }

            if (Expand != null)
            {
                allPaths.AddRange(Expand.Select(e => e.Path));
            }

            if (Filter != null)
            {
                allPaths.AddRange(Filter.Select(e => e.Path));
            }

            if (OrderBy != null)
            {
                allPaths.AddRange(OrderBy.Select(e => e.Path));
            }

            if (pathToCollection != null)
            {
                var pathToCollectionEntity = new ArraySegment <string>(
                    pathToCollection.Value.Array,
                    pathToCollection.Value.Offset,
                    pathToCollection.Value.Count - 1);

                allPaths.Add(pathToCollectionEntity.ToArray());
            }

            // This will represent the mapping from paths to symbols
            return(JoinTree.Make(ResultType, allPaths));
        }
Beispiel #9
0
        /// <summary>
        /// Prepares the SELECT statement and the column map, as it would appear in the INNER JOIN of another query that relies on this as its principal
        /// </summary>
        private SqlSelectClause PrepareSelectAsPrincipal(JoinTree joinTree, ArraySegment <string> pathToCollection, bool isAncestorExpand)
        {
            // Take the segment without the last item
            var pathToCollectionEntity = new ArraySegment <string>(
                pathToCollection.Array,
                pathToCollection.Offset,
                pathToCollection.Count - 1);

            string symbol = joinTree[pathToCollectionEntity]?.Symbol;

            if (string.IsNullOrWhiteSpace(symbol))
            {
                // Developer mistake
                throw new InvalidOperationException($"Could not find the path {string.Join("/", pathToCollectionEntity)} in the joinTree");
            }

            var columns = new List <(string Symbol, ArraySegment <string> Path, string PropName)>
            {
                (symbol, pathToCollectionEntity, isAncestorExpand ? "Node" : "Id")
            };

            return(new SqlSelectClause(columns));
        }
Beispiel #10
0
        /// <summary>
        /// Turns a filter expression into an SQL WHERE clause (without the WHERE keyword), adds all required parameters into the <see cref="SqlStatementParameters"/>
        /// </summary>
        public static string FilterToSql(FilterExpression e, Func <Type, string> sources, SqlStatementParameters ps, JoinTree joinTree, int userId, DateTime?userToday)
        {
            if (e == null)
            {
                return(null);
            }

            // This inner function just relieves us of having to pass all the above parameters each time, they just become part of its closure
            string FilterToSqlInner(FilterExpression exp)
            {
                if (exp is FilterConjunction conExp)
                {
                    return($"({FilterToSqlInner(conExp.Left)}) AND ({FilterToSqlInner(conExp.Right)})");
                }

                if (exp is FilterDisjunction disExp)
                {
                    return($"({FilterToSqlInner(disExp.Left)}) OR ({FilterToSqlInner(disExp.Right)})");
                }

                if (exp is FilterNegation notExp)
                {
                    return($"NOT ({FilterToSqlInner(notExp.Inner)})");
                }

                if (exp is FilterAtom atom)
                {
                    // (A) Prepare the symbol corresponding to the path, e.g. P1
                    var join = joinTree[atom.Path];
                    if (join == null)
                    {
                        // Developer mistake
                        throw new InvalidOperationException($"The path '{string.Join('/', atom.Path)}' was not found in the joinTree");
                    }
                    var symbol = join.Symbol;

                    // (B) Determine the type of the property and its value
                    var propName = atom.Property;
                    var prop     = join.Type.GetProperty(propName);
                    if (prop == null)
                    {
                        // Developer mistake
                        throw new InvalidOperationException($"Could not find property {propName} on type {join.Type}");
                    }

                    // The type of the first operand
                    var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                    if (!string.IsNullOrWhiteSpace(atom.Modifier))
                    {
                        // So far all modifiers are only applicable for date properties
                        if (propType != typeof(DateTime) && propType != typeof(DateTimeOffset))
                        {
                            // Developer mistake
                            throw new InvalidOperationException($"The modifier {atom.Modifier} is not valid for property {propName} since it is not of type DateTime or DateTimeOffset");
                        }

                        // So far all modifiers are date modifiers that return INT
                        propType = typeof(int);
                    }

                    // The expected type of the second operand (different in the case of hierarchyId)
                    var expectedValueType = propType;
                    if (expectedValueType == typeof(HierarchyId))
                    {
                        var idType = join.Type.GetProperty("Id")?.PropertyType;
                        if (idType == null)
                        {
                            // Programmer mistake
                            throw new InvalidOperationException($"Type {join.Type} is a tree structure but has no Id property");
                        }

                        expectedValueType = Nullable.GetUnderlyingType(idType) ?? idType;
                    }

                    // (C) Prepare the value (e.g. "'Huntington Rd.'")
                    var    valueString = atom.Value;
                    object value;
                    bool   isNull = false;
                    switch (valueString?.ToLower())
                    {
                    // This checks all built-in values
                    case "null":
                        value  = null;
                        isNull = true;
                        break;

                    case "me":
                        value = userId;
                        break;

                    // Relative DateTime values
                    case "startofyear":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = StartOfYear(userToday);
                        break;

                    case "endofyear":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = StartOfYear(userToday).AddYears(1);
                        break;

                    case "startofquarter":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = StartOfQuarter(userToday);
                        break;

                    case "endofquarter":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = StartOfQuarter(userToday).AddMonths(3);
                        break;

                    case "startofmonth":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = StartOfMonth(userToday);
                        break;

                    case "endofmonth":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = StartOfMonth(userToday).AddMonths(1);
                        break;

                    case "today":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = Today(userToday);
                        break;

                    case "endofday":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTime(atom, propName, propType);

                        value = Today(userToday).AddDays(1);
                        break;

                    case "now":
                        EnsureNullFunction(atom);
                        EnsureTypeDateTimeOffset(atom, propName, propType);

                        var now = DateTimeOffset.Now;
                        value = now;
                        break;

                    default:
                        if (expectedValueType == typeof(string) || expectedValueType == typeof(char))
                        {
                            if (!valueString.StartsWith("'") || !valueString.EndsWith("'"))
                            {
                                // Developer mistake
                                throw new InvalidOperationException($"Property {propName} is of type String, therefore the value it is compared to must be enclosed in single quotation marks");
                            }

                            valueString = valueString[1..^ 1];
                        }
Beispiel #11
0
        /// <summary>
        /// Prepares the WHERE clause of the SQL query from the <see cref="Filter"/> argument: WHERE ABC
        /// </summary>
        private string PrepareWhere(Func <Type, string> sources, JoinTree joinTree, SqlStatementParameters ps, int userId, DateTime?userToday)
        {
            // WHERE is cached
            if (_cachedWhere == null)
            {
                string whereFilter      = null;
                string whereInIds       = null;
                string whereInParentIds = null;

                if (Filter != null)
                {
                    whereFilter = QueryTools.FilterToSql(Filter, sources, ps, joinTree, userId, userToday);
                }

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

                        // Prepare the ids table
                        DataTable idsTable = isIntKey ? RepositoryUtilities.DataTable(Ids.Select(id => new { Id = (int)id }))
                            : isStringKey?RepositoryUtilities.DataTable(Ids.Select(id => new { 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    = (Nullable.GetUnderlyingType(KeyType) ?? KeyType) == typeof(int);
                        var isStringKey = KeyType == typeof(string);

                        // Prepare the data table
                        DataTable parentIdsTable = new DataTable();
                        string    propName       = "Id";
                        var       column         = new DataColumn(propName, KeyType);
                        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[propName] = id;
                            parentIdsTable.Rows.Add(row);
                        }

                        // Prepare the TVP
                        var parentIdsTvp = new SqlParameter("@ParentIds", parentIdsTable)
                        {
                            TypeName = isIntKey ? "[dbo].[IdList]" : isStringKey ? "[dbo].[StringList]" : throw new InvalidOperationException("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";
                        }
                    }
                }

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

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

                _cachedWhere = whereSql;
            }

            return(_cachedWhere);
        }
Beispiel #12
0
        /// <summary>
        /// Prepares the SELECT statement and the column map, using the <see cref="Select"/> argument
        /// </summary>
        private SqlSelectClause PrepareSelect(JoinTree joinTree)
        {
            var selects = new HashSet <(string Symbol, string PropName)>();
            var columns = new List <(string Symbol, ArraySegment <string> Path, string PropName)>();

            void AddSelect(string symbol, ArraySegment <string> path, string propName)
            {
                // NULL happens when there is a select that has been segmented from the middle
                // and the first section of the segment no longer terminates with a simple property
                // propName = propName ?? "Id";
                if (propName == null)
                {
                    return;
                }

                if (selects.Add((symbol, propName)))
                {
                    columns.Add((symbol, path, propName));
                }
            }

            // Any path step that is touched by a select (which has a property) ignores the expand, the joinTree below
            // allows us to efficiently check if any particular step is touched by a select
            JoinTree overridingSelectTree = Select == null ? null : JoinTree.Make(ResultType, Select.Select(e => e.Path)); // Overriding select paths

            // Optimization: remember the joins that have been selected and don't select them again
            HashSet <JoinTree> selectedJoins = new HashSet <JoinTree>();

            // For every expanded entity that has not been tainted by a select argument, we add all its properties to the list of selects
            Expand ??= ExpandExpression.Empty;
            foreach (var expand in Expand.Union(ExpandExpression.RootSingleton))
            {
                string[] path = expand.Path;
                for (int i = 0; i <= path.Length; i++)
                {
                    var subpath     = new ArraySegment <string>(path, 0, i);
                    var selectMatch = overridingSelectTree?[subpath];
                    if (selectMatch == null) // This expand is not overridden by a select
                    {
                        var join = joinTree[subpath];
                        if (join == null)
                        {
                            // Developer mistake
                            throw new InvalidOperationException($"The path '{string.Join('/', subpath)}' was not found in the joinTree");
                        }
                        else if (selectedJoins.Contains(join))
                        {
                            continue;
                        }
                        else
                        {
                            selectedJoins.Add(join);
                        }

                        foreach (var prop in join.Type.GetMappedProperties())
                        {
                            AddSelect(join.Symbol, subpath, prop.Name);
                        }
                    }
                }
            }

            if (Select != null)
            {
                foreach (var select in Select)
                {
                    // Add the property
                    string[] path = select.Path;
                    {
                        var join     = joinTree[path];
                        var propName = select.Property; // Can be null
                        AddSelect(join.Symbol, path, propName);
                    }

                    // In this loop we ensure all levels to the selected properties
                    // have their Ids and Foreign Keys added to the select collection
                    for (int i = 0; i <= path.Length; i++)
                    {
                        var subpath = new ArraySegment <string>(path, 0, i);
                        var join    = joinTree[subpath];
                        if (join == null)
                        {
                            // Developer mistake
                            throw new InvalidOperationException($"The path '{string.Join('/', subpath)}' was not found in the joinTree");
                        }
                        else if (selectedJoins.Contains(join))
                        {
                            // All properties were added earlier in an expand
                            continue;
                        }
                        else
                        {
                            selectedJoins.Add(join);
                        }

                        // The Id is ALWAYS required in every EntityWithKey
                        if (join.Type.IsSubclassOf(typeof(EntityWithKey)))
                        {
                            AddSelect(join.Symbol, subpath, "Id");
                        }

                        // Add all the foreign keys to the next level down
                        foreach (var nextJoin in join.Values)
                        {
                            AddSelect(join.Symbol, subpath, nextJoin.ForeignKeyName);
                        }
                    }
                }
            }

            // If the foreign key to the principal query is specified, then always include that
            // otherwise there will be no way to link the collection to the principal query once we load the data
            if (!string.IsNullOrWhiteSpace(ForeignKeyToPrincipalQuery))
            {
                var path = new string[0];
                AddSelect(joinTree.Symbol, path, ForeignKeyToPrincipalQuery);
            }

            // Deals with trees
            foreach (var path in PathsToParentEntitiesWithExpandedAncestors)
            {
                var join = joinTree[path];
                AddSelect(join.Symbol, path, "ParentId");
            }

            if (IsAncestorExpand)
            {
                var path = new string[0];
                AddSelect(joinTree.Symbol, path, "ParentId");
            }

            // Change the hash set to a list so that the order is well defined
            return(new SqlSelectClause(columns));
        }