/// <summary>
/// Connects to the DB and loads the results of a single dynamic statement described in <paramref name="args"/>.
/// </summary>
/// <param name="connString">The connection string of the SQL database from which to load the data.</param>
/// <param name="args">All the information needed to connect to the database and execute the statement.</param>
/// <param name="cancellation">The cancellation instruction.</param>
/// <returns>The requested data packaged in a <see cref="DynamicOutput"/>.</returns>
public async Task <DynamicOutput> LoadDynamic(string connString, DynamicLoaderArguments args, CancellationToken cancellation = default)
{
// Destructure the args
var countSql = args.CountSql;
var principalStatement = args.PrincipalStatement;
var dimAncestorsStatements = args.DimensionAncestorsStatements ?? new List <SqlDimensionAncestorsStatement>();
var ps = args.Parameters;
var vars = args.Variables;
////////////// 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: variablesSql,
countSql: countSql,
statements.ToArray());
// The result
DynamicOutput result = null;
try
{
using var trx = TransactionFactory.ReadCommitted();
await ExponentialBackoff(async() =>
{
var rows = new List <DynamicRow>();
var trees = new List <DimensionAncestorsOutput>();
var count = 0;
// Connection
using var conn = new SqlConnection(connString);
// Command Text
using var cmd = conn.CreateCommand();
cmd.CommandTimeout = TimeoutInSeconds;
cmd.CommandText = sql;
// Parameters
foreach (var parameter in ps)
{
cmd.Parameters.Add(parameter);
}
// Execute
try // To capture
{
await conn.OpenAsync(cancellation);
using var reader = await cmd.ExecuteReaderAsync(cancellation);
// (1) Load the count if any
if (!string.IsNullOrWhiteSpace(countSql))
{
if (await reader.ReadAsync(cancellation))
{
count = reader.GetInt32(0);
}
// Go over to the next result set
await reader.NextResultAsync(cancellation);
}
// (2) Load results of the principal query
{
int columnCount = principalStatement.ColumnCount;
while (await reader.ReadAsync(cancellation))
{
var row = new DynamicRow(columnCount);
for (int index = 0; index < columnCount; index++)
{
var dbValue = reader.Value(index);
row.Add(dbValue);
}
rows.Add(row);
}
}
// (3) 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 DimensionAncestorsOutput
(
idIndex: treeStatement.IdIndex,
minIndex: minIndex,
result: new List <DynamicRow>()
);
await reader.NextResultAsync(cancellation);
while (await reader.ReadAsync(cancellation))
{
var row = new DynamicRow(columnCount);
for (index = 0; index < targetIndices.Length; index++)
{
var dbValue = reader.Value(index);
int targetIndex = targetIndices[index];
row.AddAt(dbValue, targetIndex);
}
treeResult.Result.Add(row);
}
trees.Add(treeResult);
}
}
catch (SqlException ex) when(ex.Number is 8134) // Divide by zero
{
throw new QueryException(DivisionByZeroMessage);
}
finally
{
// Otherwise it will fail on retry
cmd.Parameters.Clear();
}
trx.Complete();
result = new DynamicOutput(rows, trees, count);
}, cancellation);
}
catch (Exception ex) when(ex is not OperationCanceledException && ex is not ReportableException)
{
// Include the SQL and the parameters
throw new StatementLoaderException(sql, ps, ex);
}
return(result);
}
private async Task <(List <DynamicRow>, int count)> ToListAndCountInnerAsync(bool includeCount, int maxCount, QueryContext ctx2, CancellationToken cancellation)
{
var queryArgs = await _factory(cancellation);
var connString = queryArgs.ConnectionString;
var sources = queryArgs.Sources;
var loader = queryArgs.Loader;
var userId = ctx2.UserId;
var userToday = ctx2.UserToday;
// ------------------------ 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);
// 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
string countSql = null;
if (includeCount)
{
string countSelectSql = maxCount > 0 ? $"SELECT TOP {maxCount} [P].*" : "SELECT [P].*";
countSql = QueryTools.CombineSql(
selectSql: countSelectSql,
joinSql: joinSql,
principalQuerySql: null,
whereSql: whereSql,
orderbySql: null,
offsetFetchSql: null,
groupbySql: null,
havingSql: null,
selectFromTempSql: null
);
countSql = $@"SELECT COUNT(*) As [Count] FROM (
{countSql.IndentLines()}
) AS [Q]";
}
// ------------------------ Execute SQL and return Result
var principalStatement = new SqlDynamicStatement(sql, columnCount);
var args = new DynamicLoaderArguments
{
CountSql = countSql, // No counting in aggregate functions
PrincipalStatement = principalStatement,
DimensionAncestorsStatements = null, // No ancestors
Variables = vars,
Parameters = ps,
};
var result = await loader.LoadDynamic(connString, args, cancellation);
return(result.Rows, result.Count);
}
public async Task <DynamicOutput> ToListAsync(QueryContext ctx, CancellationToken cancellation)
{
var queryArgs = await _factory(cancellation);
var sources = queryArgs.Sources;
var connString = queryArgs.ConnectionString;
var loader = queryArgs.Loader;
var userId = ctx.UserId;
var userToday = ctx.UserToday;
// ------------------------ 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 <SqlDimensionAncestorsStatement>();
{
// 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);
// Prepare the Context
var complicationCtx = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);
// Prepare the SQL components
var selectSql = PrepareAncestorSelectSql(complicationCtx, 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 SqlDimensionAncestorsStatement(idIndex, sql, targetIndices);
ancestorsStatements.Add(statement);
}
}
}
// ------------------------ The SQL Generation Step
// (1) Prepare the JOIN's clause
var principalJoinTrie = PreparePrincipalJoin();
var principalJoinSql = principalJoinTrie.GetSql(sources);
// 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
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 Output
var principalStatement = new SqlDynamicStatement(principalSql, principalColumnCount);
var args = new DynamicLoaderArguments
{
CountSql = null, // No counting in aggregate functions
PrincipalStatement = principalStatement,
DimensionAncestorsStatements = ancestorsStatements,
Variables = vars,
Parameters = ps,
};
var output = await loader.LoadDynamic(connString, args, cancellation);
return(output);
}
