/// <summary>
/// Prepares the join tree.
/// </summary>
private JoinTrie PrepareJoin()
{
// construct the join tree
var allPaths = new List <string[]>();
if (_select != null)
{
allPaths.AddRange(_select.ColumnAccesses().Select(e => e.Path));
}
if (_orderby != null)
{
allPaths.AddRange(_orderby.ColumnAccesses().Select(e => e.Path));
}
if (_filter != null)
{
allPaths.AddRange(_filter.ColumnAccesses().Select(e => e.Path));
}
// This will represent the mapping from paths to symbols
var joinTree = JoinTrie.Make(TypeDescriptor.Get <T>(), allPaths);
return(joinTree);
}
private static IEnumerable <PropertyDescriptor> AddColumnsFromProperties <T>(DataTable table, IEnumerable <ExtraColumn <T> > extras = null) where T : Entity
{
var props = TypeDescriptor.Get <T>().SimpleProperties;
foreach (var prop in props)
{
// If it's a self referencing FK column, add the index first (by convention the index column immediate precedes the self ref FK column
if (prop.IsSelfReferencing)
{
var indexColumn = new DataColumn(prop.IndexPropertyName, typeof(int));
table.Columns.Add(indexColumn);
}
// Add the column itself
var propType = Nullable.GetUnderlyingType(prop.Type) ?? prop.Type;
var column = new DataColumn(prop.Name, propType);
if (propType == typeof(string))
{
// For string columns, it is more performant to explicitly specify the maximum column size
// According to this article: http://www.dbdelta.com/sql-server-tvp-performance-gotchas/
int maxLength = prop.MaxLength;
if (maxLength > 0)
{
column.MaxLength = maxLength;
}
}
table.Columns.Add(column);
}
if (extras != null)
{
foreach (var extra in extras)
{
var column = new DataColumn(extra.Name, extra.Type);
table.Columns.Add(column);
}
}
return(props);
}
/// <summary>
/// Restricts the <see cref="EntityQuery{T}"/> to loading entities that have a certain property evaluating to one of the supplied values.
/// For example: all entities where Code IN values.
/// </summary>
public EntityQuery <T> FilterByPropertyValues(string propName, IEnumerable <object> values)
{
if (string.IsNullOrWhiteSpace(propName))
{
throw new ArgumentNullException(nameof(propName));
}
var desc = TypeDescriptor.Get <T>();
if (desc.Property(propName) == null)
{
throw new InvalidOperationException($"Property {propName} does not exist on type {desc.Name}");
}
var clone = Clone();
clone._propName = propName;
clone._values = values?.ToList() ?? throw new ArgumentNullException(nameof(values)); // for immutability
return(clone);
}
public static void AddEntity(this RelatedEntities relatedEntities, EntityWithKey entity)
{
if (entity == null)
{
return;
}
var type = entity.GetType();
var desc = TypeDescriptor.Get(type);
var collection = type.Name;
var list = relatedEntities.GetList(collection);
if (list == null)
{
list = desc.CreateList();
relatedEntities.SetList(collection, list);
}
list.Add(entity);
}
public IDictionary<string, object> ToTypedKeyValueOrNull(TypeDescriptor typeDescriptor, string json)
{
if (string.IsNullOrWhiteSpace(json))
return null;
var kvRepresentation = Serializer.Deserialize<IDictionary<string, dynamic>>(json);
if (kvRepresentation == null || kvRepresentation.Count < 1)
return null;
foreach (var key in kvRepresentation.Keys.ToArray())
{
var membername = key;
var member = typeDescriptor.Get(membername);
if (member == null)
continue;
kvRepresentation[membername] = JsonSerializer.DeserializeFromString(kvRepresentation[membername], member.Type);
}
return kvRepresentation;
}
/// <summary>
/// Backbone for <see cref="CountAsync(int, QueryContext, CancellationToken)"/>,
/// <see cref="ToListAsync(QueryContext, CancellationToken)"/> and
/// <see cref="ToListAndCountAsync(int, QueryContext, CancellationToken)"/>.
/// </summary>
private async Task <EntityOutput <T> > ToListAndCountInnerAsync(bool includeResult, bool includeCount, int maxCount, QueryContext ctx, CancellationToken cancellation)
{
var queryArgs = await _factory(cancellation);
var connString = queryArgs.ConnectionString;
var sources = queryArgs.Sources;
var loader = queryArgs.Loader;
var userId = ctx.UserId;
var userToday = ctx.UserToday;
var resultDesc = TypeDescriptor.Get <T>();
_orderby ??= (IsEntityWithKey() ? ExpressionOrderBy.Parse("Id desc") :
throw new InvalidOperationException($"Query<{resultDesc.Type.Name}> was executed without an orderby clause"));
// Prepare all the query parameters
ExpressionSelect selectExp = _select;
ExpressionExpand expandExp = _expand;
ExpressionOrderBy orderbyExp = _orderby;
ExpressionFilter filterExp = _filter;
// To prevent SQL injection
ValidatePathsAndProperties(selectExp, expandExp, filterExp, orderbyExp, resultDesc);
// ------------------------ Step #1
// Segment the paths of select and expand along the one-to-many relationships, each one-to-many relationship will
// result in a new internal query for the child collection with the original query as its principal query
var segments = new Dictionary <ArraySegment <string>, EntityQueryInternal>(new PathEqualityComparer());
// Helper method for creating a an internal query, will be used later in both the select and the expand loops
EntityQueryInternal MakeQueryInternal(ArraySegment <string> previousFullPath, ArraySegment <string> subPath, TypeDescriptor desc)
{
EntityQueryInternal principalQuery = previousFullPath == null ? null : segments[previousFullPath];
ArraySegment <string> pathToCollectionPropertyInPrincipal = previousFullPath == null ? null : subPath;
if (principalQuery != null && desc.KeyType == KeyType.None)
{
// Programmer mistake
throw new InvalidOperationException($"[Bug] Type {desc.Name} has no Id property, yet it is used as a navigation collection on another entity");
}
string foreignKeyToPrincipalQuery = null;
bool isAncestorExpand = false;
if (principalQuery != null)
{
// This loop retrieves the entity descriptor that has the collection property
TypeDescriptor collectionPropertyEntity = principalQuery.ResultDescriptor;
int i = 0;
for (; i < pathToCollectionPropertyInPrincipal.Count - 1; i++)
{
var step = pathToCollectionPropertyInPrincipal[i];
collectionPropertyEntity = collectionPropertyEntity.NavigationProperty(step).TypeDescriptor;
}
// Get the collection/Parent property
string propertyName = pathToCollectionPropertyInPrincipal[i];
var property = collectionPropertyEntity.Property(propertyName);
if (property is NavigationPropertyDescriptor navProperty && navProperty.IsParent)
{
foreignKeyToPrincipalQuery = "ParentId";
isAncestorExpand = true;
}
else if (property is CollectionPropertyDescriptor collProperty)
{
// Must be a collection then
foreignKeyToPrincipalQuery = collProperty.ForeignKeyName;
}
else
{
throw new InvalidOperationException($"Bug: Segment along a property {property.Name} on type {collectionPropertyEntity.Name} That is neither a collection nor a parent");
}
}
if (isAncestorExpand)
{
// the path to parent entity is the path above minus the "Parent"
var pathToParentEntity = new ArraySegment <string>(
array: pathToCollectionPropertyInPrincipal.Array,
offset: 0,
count: pathToCollectionPropertyInPrincipal.Count - 1);
// Adding this causes the principal query to always include ParentId in the select clause
principalQuery.PathsToParentEntitiesWithExpandedAncestors.Add(pathToParentEntity);
}
// This is the orderby of related queries, and the default orderby of the root query
var defaultOrderBy = ExpressionOrderBy.Parse(
desc.HasProperty("Index") ? "Index" :
desc.HasProperty("SortKey") ? "SortKey" : "Id");
// Prepare the flat query and return it
var flatQuery = new EntityQueryInternal
{
PrincipalQuery = principalQuery,
IsAncestorExpand = isAncestorExpand,
PathToCollectionPropertyInPrincipal = pathToCollectionPropertyInPrincipal,
ForeignKeyToPrincipalQuery = foreignKeyToPrincipalQuery,
ResultDescriptor = desc,
OrderBy = defaultOrderBy
};
return(flatQuery);
}
/// <summary>
/// Takes a list of <see cref="Entity"/>'s, and for every entity it inspects the navigation properties, if a navigation property
/// contains an <see cref="Entity"/> with a strong type, it sets that property to null, and moves the strong entity into a separate
/// "relatedEntities" hash set, this has several advantages: <br/>
/// 1 - The JSON serializer does not have to deal with circular references <br/>
/// 2 - Every strong entity is present once in the JSON response (smaller response size) <br/>
/// 3 - It makes it easier for clients to store and track entities in a central workspace <br/>
/// </summary>
/// <returns>A dictionary mapping every type name to an <see cref="IEnumerable"/> of related entities of that type (excluding the result entities).</returns>
public static RelatedEntities Flatten <TEntity>(IEnumerable <TEntity> resultEntities, CancellationToken cancellation)
where TEntity : Entity
{
// If the result is empty, nothing to do
var relatedEntities = new RelatedEntities();
if (resultEntities == null || !resultEntities.Any())
{
return(relatedEntities);
}
var resultHash = resultEntities.Cast <Entity>().ToHashSet();
var addedAleady = new HashSet <EntityWithKey>();
void FlattenInner(Entity entity, TypeDescriptor typeDesc)
{
if (entity.EntityMetadata.Flattened)
{
// This has already been flattened before
return;
}
// Mark the entity as flattened
entity.EntityMetadata.Flattened = true;
// Recursively go over the nav properties
foreach (var prop in typeDesc.NavigationProperties)
{
if (prop.GetValue(entity) is EntityWithKey relatedEntity)
{
prop.SetValue(entity, null);
if (!resultHash.Contains(relatedEntity) && addedAleady.Add(relatedEntity))
{
// Unless it is part of the main result, add it to relatedEntities (only once)
relatedEntities.AddEntity(relatedEntity);
}
FlattenInner(relatedEntity, prop.TypeDescriptor);
}
}
// Recursively go over every entity in the nav collection properties
foreach (var prop in typeDesc.CollectionProperties)
{
var collectionType = prop.CollectionTypeDescriptor;
if (prop.GetValue(entity) is IList collection)
{
foreach (var obj in collection)
{
if (obj is Entity relatedEntity)
{
FlattenInner(relatedEntity, collectionType);
}
}
}
}
}
// Flatten every entity in the main list
var typeDesc = TypeDescriptor.Get <TEntity>();
foreach (var entity in resultEntities)
{
if (entity != null)
{
FlattenInner(entity, typeDesc);
cancellation.ThrowIfCancellationRequested();
}
}
// Return the result
return(relatedEntities);
}
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);
}
