/// <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 PreparePrincipalQuery(Func <Type, string> sources, SqlStatementParameters ps, int userId, DateTime?userToday) { string principalQuerySql = ""; if (PrincipalQuery != null) { // Get the inner sql and append 4 spaces before each line for aesthetics string innerSql = PrincipalQuery.PrepareStatementAsPrincipal(sources, ps, IsAncestorExpand, PathToCollectionPropertyInPrincipal, userId, userToday); innerSql = QueryTools.IndentLines(innerSql); if (IsAncestorExpand) { principalQuerySql = $@"INNER JOIN ( {innerSql} ) As [S] ON [S].[Node].IsDescendantOf([P].[Node]) = 1 AND [S].[Node] <> [P].[Node]"; } else { principalQuerySql = $@"INNER JOIN ( {innerSql} ) As [S] ON [S].[Id] = [P].[{ForeignKeyToPrincipalQuery}]"; } } return(principalQuerySql); }
private static string FullSqlForLogger(string sql, SqlStatementParameters ps) { var stringifiedParams = string.Join(" ", ps.Select(e => $"DECLARE @{e.ParameterName} {e.SqlDbType.ToString().ToUpper()} = {e.Value};")); var fullSql = @$ "{stringifiedParams} {sql}"; return(fullSql); }
/// <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> /// 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, SqlStatementVariables vars, SqlStatementParameters ps, bool isAncestorExpand, ArraySegment <string> pathToCollectionProperty, int userId, DateTime?userToday) { // (1) Prepare the JOIN's clause JoinTrie joinTrie = PrepareJoin(pathToCollectionProperty); 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 = 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); }
/// <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, }); }
/// <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> /// This methods loads the results of an <see cref="AggregateQuery{T}"/> into a list of <see cref="DynamicEntity"/> /// </summary> /// <param name="statement">The <see cref="SqlStatement"/> to load</param> /// <param name="preparatorySql">Any SQL to be included at the very beginning the main script (cannot contain a SELECT or return a result set</param> /// <param name="ps">The parameters needed by SQL</param> /// <param name="conn">The SQL Server connection through which to execute the SQL script</param> /// <returns>The list of hydrated <see cref="DynamicEntity"/>s</returns> public static async Task <List <DynamicEntity> > LoadAggregateStatement( SqlStatement statement, string preparatorySql, SqlStatementParameters ps, SqlConnection conn) { var result = new List <DynamicEntity>(); using (var cmd = conn.CreateCommand()) { // Command Text cmd.CommandText = PrepareSql(preparatorySql, statement); // Command Parameters foreach (var parameter in ps) { cmd.Parameters.Add(parameter); } // It will always be open, but we add this nonetheless for robustness bool ownsConnection = conn.State != System.Data.ConnectionState.Open; if (ownsConnection) { conn.Open(); } // Efficiently calculates the dynamic property name var cacheDynamicPropertyName = new Dictionary <(ArraySegment <string>, string, string, string), string>(); string DynamicPropertyName(ArraySegment <string> path, string prop, string aggregation, string function) { if (!cacheDynamicPropertyName.ContainsKey((path, prop, aggregation, function))) { string result = prop; string pathString = string.Join('/', path); if (!string.IsNullOrWhiteSpace(pathString)) { result = $"{pathString}/{result}"; } if (!string.IsNullOrWhiteSpace(function)) { result = $"{result}|{function}"; } if (!string.IsNullOrWhiteSpace(aggregation)) { result = $"{aggregation}({result})"; } cacheDynamicPropertyName[(path, prop, aggregation, function)] = result; } return(cacheDynamicPropertyName[(path, prop, aggregation, function)]);
// Functionality /// <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, SqlStatementParameters ps, int userId, DateTime?userToday) { // (1) Prepare the JOIN's clause var joinTree = PrepareJoin(); var joinSql = joinTree.GetSql(sources, FromSql); // (2) Prepare the SELECT clause SqlSelectClause selectClause = PrepareSelect(joinTree); 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(); // (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(new SqlStatement { Sql = sql, ResultType = ResultType, ColumnMap = selectClause.GetColumnMap(), Query = this, }); }
// Functionality public string PrepareCountSql( Func <Type, string> sources, SqlStatementVariables vars, SqlStatementParameters ps, int userId, DateTime?userToday, int maxCount) { // (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 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 ( {QueryTools.IndentLines(sql)} ) AS [Q]"; return(sql); }
/// <summary> /// Implementation of <see cref="IQueryInternal"/> /// </summary> public SqlStatement PrepareStatement( Func <Type, string> sources, SqlStatementParameters ps, int userId, DateTime?userToday) { // (1) Prepare the JOIN's clause var joinTree = PrepareJoin(); var joinSql = joinTree.GetSql(sources, fromSql: null); // (2) Prepare the SELECT clause SqlSelectGroupByClause selectClause = PrepareSelect(joinTree); var selectSql = selectClause.ToSelectSql(); var groupbySql = selectClause.ToGroupBySql(); // (3) Prepare the WHERE clause string whereSql = PrepareWhere(sources, joinTree, ps, userId, userToday); // (4) Prepare the ORDERBY clause string orderbySql = PrepareOrderBy(joinTree); // (5) Finally put together the final SQL statement and return it string sql = QueryTools.CombineSql( selectSql: selectSql, joinSql: joinSql, principalQuerySql: null, whereSql: whereSql, orderbySql: orderbySql, offsetFetchSql: null, groupbySql: groupbySql ); // (6) Return the result return(new SqlStatement { Sql = sql, ResultType = ResultType, ColumnMap = selectClause.GetColumnMap(), Query = null, // Not used anyways }); }
/// <summary> /// This methods loads the results of an <see cref="AggregateQuery{T}"/> into a list of <see cref="DynamicRow"/> /// </summary> /// <param name="principalStatement">The principal <see cref="SqlStatement"/> to load first.</param> /// <param name="preparatorySql">Any SQL to be included at the very beginning the main script (cannot contain a SELECT or return a result set</param> /// <param name="ps">The parameters needed by SQL</param> /// <param name="conn">The SQL Server connection through which to execute the SQL script</param> /// <returns>The list of hydrated <see cref="DynamicRow"/>s</returns> public static async Task <(List <DynamicRow> result, IEnumerable <TreeDimensionResult> treeDimensions, int count)> LoadDynamicStatement( SqlDynamicStatement principalStatement, IEnumerable <DimensionAncestorsStatement> dimAncestorsStatements, bool includeCount, SqlStatementVariables vars, SqlStatementParameters ps, SqlConnection conn, ILogger logger, CancellationToken cancellation) { dimAncestorsStatements ??= new List <DimensionAncestorsStatement>(); var result = new List <DynamicRow>(); var treeResults = new List <TreeDimensionResult>(); int count = 0; ////////////// Prepare the complete SQL code // Add any variables in the preparatory SQL string variablesSql = vars.ToSql(); var statements = new List <string>(1 + dimAncestorsStatements.Count()) { principalStatement.Sql }; statements.AddRange(dimAncestorsStatements.Select(e => e.Sql)); string sql = PrepareSql(variablesSql, null, null, statements.ToArray()); using (var cmd = conn.CreateCommand()) { // Command Text cmd.CommandText = sql; // Command Parameters foreach (var parameter in ps) { cmd.Parameters.Add(parameter); } // It will always be open, but we add this nonetheless for robustness bool ownsConnection = conn.State != System.Data.ConnectionState.Open; if (ownsConnection) { conn.Open(); } // Results are loaded try { // Load results of the principal query using var reader = await cmd.ExecuteReaderAsync(cancellation); { int columnCount = principalStatement.ColumnCount; while (await reader.ReadAsync(cancellation)) { var row = new DynamicRow(columnCount); for (int index = 0; index < columnCount; index++) { var dbValue = reader[index]; if (dbValue == DBNull.Value) { dbValue = null; } row.Add(dbValue); } result.Add(row); } } // Load the tree dimensions foreach (var treeStatement in dimAncestorsStatements) { int columnCount = treeStatement.TargetIndices.Count(); int index; int minIndex = treeStatement.TargetIndices.Min(); int[] targetIndices = treeStatement.TargetIndices.Select(i => i - minIndex).ToArray(); var treeResult = new TreeDimensionResult(treeStatement.IdIndex, minIndex); await reader.NextResultAsync(cancellation); while (await reader.ReadAsync(cancellation)) { var row = new DynamicRow(columnCount); for (index = 0; index < targetIndices.Length; index++) { int targetIndex = targetIndices[index]; var dbValue = reader[index]; if (dbValue == DBNull.Value) { dbValue = null; } row.AddAt(dbValue, targetIndex); } treeResult.Result.Add(row); } treeResults.Add(treeResult); } // Load the count if any if (includeCount) { await reader.NextResultAsync(cancellation); if (await reader.ReadAsync(cancellation)) { count = reader.GetInt32(0); } } } catch (SqlException ex) when(ex.Number is 8134) // Divide by zero { throw new QueryException(DIVISION_BY_ZERO_MESSAGE); } catch (SqlException ex) { logger.LogError(ex, FullSqlForLogger(sql, ps)); // Log the generated SQL code for debugging throw; } finally { // Otherwise we might get an error when a parameter is reused cmd.Parameters.Clear(); // The connection is never owned, but we add this code anyways for robustness if (ownsConnection) { conn.Close(); conn.Dispose(); } } } return(result, treeResults, count); }
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); }
/// <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]; }
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); }
/// <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); }