/// <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)); }
/// <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); }
/// <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); }
/// <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)); }
/// <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)); }
/// <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]; }
/// <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); }
/// <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)); }