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