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