/// <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 PreparePrincipalQuerySql(QxCompilationContext ctx)
{
string principalQuerySql = "";
if (PrincipalQuery != null)
{
// Get the inner sql and append 4 spaces before each line for aesthetics
string innerSql = PrincipalQuery.PrepareStatementAsPrincipal(
ctx.Sources,
ctx.Variables,
ctx.Parameters,
IsAncestorExpand,
PathToCollectionPropertyInPrincipal,
ctx.UserId,
ctx.Today);
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
{
// This works since when there is a principal query, there is no WHERE clause
principalQuerySql = $@"WHERE [P].[{ForeignKeyToPrincipalQuery}] IN (
{innerSql}
)";
}
}
return(principalQuerySql);
}
/// <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);
}
// 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);
}
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);
}