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