private SqlDateTime GetMinMaxKey(FetchXmlScan fetchXmlNode, IDictionary<string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary<string, Type> parameterTypes, IDictionary<string, object> parameterValues, bool max) { // Create a new FetchXmlScan node with a copy of the original query var minMaxNode = new FetchXmlScan { Alias = "minmax", DataSource = fetchXmlNode.DataSource, FetchXml = CloneFetchXml(fetchXmlNode.FetchXml), Parent = this }; // Remove the aggregate settings and all attributes from the query minMaxNode.FetchXml.aggregate = false; RemoveAttributesAndOrders(minMaxNode.Entity); // Add the primary key attribute of the root entity minMaxNode.Entity.AddItem(new FetchAttributeType { name = "createdon" }); // Sort by the primary key minMaxNode.Entity.AddItem(new FetchOrderType { attribute = "createdon", descending = max }); // Only need to retrieve the first item minMaxNode.FetchXml.top = "1"; try { var result = minMaxNode.Execute(dataSources, options, parameterTypes, parameterValues).FirstOrDefault(); if (result == null) return SqlDateTime.Null; return (SqlDateTime)result["minmax.createdon"]; } catch (QueryExecutionException ex) { ex.Node = this; throw; } }
internal static void FoldFetchXmlColumns(IDataExecutionPlanNode source, List <SelectColumn> columnSet, IDictionary <string, DataSource> dataSources, IDictionary <string, Type> parameterTypes) { if (source is FetchXmlScan fetchXml) { if (!dataSources.TryGetValue(fetchXml.DataSource, out var dataSource)) { throw new NotSupportedQueryFragmentException("Missing datasource " + fetchXml.DataSource); } // Check if there are any aliases we can apply to the source FetchXml var schema = fetchXml.GetSchema(dataSources, parameterTypes); var processedSourceColumns = new HashSet <string>(StringComparer.OrdinalIgnoreCase); var hasStar = columnSet.Any(col => col.AllColumns && col.SourceColumn == null); var aliasStars = new HashSet <string>(columnSet.Where(col => col.AllColumns && col.SourceColumn != null).Select(col => col.SourceColumn.Replace(".*", "")).Distinct(StringComparer.OrdinalIgnoreCase), StringComparer.OrdinalIgnoreCase); foreach (var col in columnSet) { if (col.AllColumns) { if (col.SourceColumn == null) { // Add an all-attributes to the main entity and all link-entities fetchXml.Entity.AddItem(new allattributes()); foreach (var link in fetchXml.Entity.GetLinkEntities()) { if (link.SemiJoin) { continue; } link.AddItem(new allattributes()); } } else if (!hasStar) { // Only add an all-attributes to the appropriate entity/link-entity if (col.SourceColumn.Replace(".*", "").Equals(fetchXml.Alias, StringComparison.OrdinalIgnoreCase)) { fetchXml.Entity.AddItem(new allattributes()); } else { var link = fetchXml.Entity.FindLinkEntity(col.SourceColumn.Replace(".*", "")); link.AddItem(new allattributes()); } } } else if (!hasStar) { // Only fold individual columns down to the FetchXML if there is no corresponding all-attributes var parts = col.SourceColumn.Split('.'); if (parts.Length == 1 || !aliasStars.Contains(parts[0])) { var sourceCol = col.SourceColumn; schema.ContainsColumn(sourceCol, out sourceCol); var attr = fetchXml.AddAttribute(sourceCol, null, dataSource.Metadata, out var added, out var linkEntity); // Check if we can fold the alias down to the FetchXML too. Don't do this if the name isn't valid for FetchXML if (sourceCol != col.SourceColumn) { parts = col.SourceColumn.Split('.'); } if (!col.OutputColumn.Equals(parts.Last(), StringComparison.OrdinalIgnoreCase) && FetchXmlScan.IsValidAlias(col.OutputColumn)) { if (added || (!processedSourceColumns.Contains(sourceCol) && !fetchXml.IsAliasReferenced(attr.alias))) { // Don't fold the alias if there's also a sort on the same attribute, as it breaks paging // https://markcarrington.dev/2019/12/10/inside-fetchxml-pt-4-order/#sorting_&_aliases var items = linkEntity?.Items ?? fetchXml.Entity.Items; if (items == null || !items.OfType <FetchOrderType>().Any(order => order.attribute == attr.name) || !fetchXml.AllPages) { attr.alias = col.OutputColumn; } } col.SourceColumn = sourceCol.Split('.')[0] + "." + (attr.alias ?? attr.name); } processedSourceColumns.Add(sourceCol); } } } } }
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints) { LeftSource = LeftSource.FoldQuery(dataSources, options, parameterTypes, hints); LeftSource.Parent = this; RightSource = RightSource.FoldQuery(dataSources, options, parameterTypes, hints); RightSource.Parent = this; if (SemiJoin) { return(this); } var leftSchema = LeftSource.GetSchema(dataSources, parameterTypes); var rightSchema = RightSource.GetSchema(dataSources, parameterTypes); if (LeftSource is FetchXmlScan leftFetch && RightSource is FetchXmlScan rightFetch) { // Can't join data from different sources if (!leftFetch.DataSource.Equals(rightFetch.DataSource, StringComparison.OrdinalIgnoreCase)) { return(this); } // If one source is distinct and the other isn't, joining the two won't produce the expected results if (leftFetch.FetchXml.distinct ^ rightFetch.FetchXml.distinct) { return(this); } // Check that the alias is valid for FetchXML if (!FetchXmlScan.IsValidAlias(rightFetch.Alias)) { return(this); } var leftEntity = leftFetch.Entity; var rightEntity = rightFetch.Entity; // Check that the join is on columns that are available in the FetchXML var leftAttribute = LeftAttribute.GetColumnName(); if (!leftSchema.ContainsColumn(leftAttribute, out leftAttribute)) { return(this); } var rightAttribute = RightAttribute.GetColumnName(); if (!rightSchema.ContainsColumn(rightAttribute, out rightAttribute)) { return(this); } var leftAttributeParts = leftAttribute.Split('.'); var rightAttributeParts = rightAttribute.Split('.'); if (leftAttributeParts.Length != 2) { return(this); } if (rightAttributeParts.Length != 2) { return(this); } // If the entities are from different virtual entity data providers it's probably not going to work if (!dataSources.TryGetValue(leftFetch.DataSource, out var dataSource)) { throw new NotSupportedQueryFragmentException("Missing datasource " + leftFetch.DataSource); } if (dataSource.Metadata[leftFetch.Entity.name].DataProviderId != dataSource.Metadata[rightFetch.Entity.name].DataProviderId) { return(this); } // Check we're not going to have too many link entities var leftLinkCount = leftFetch.Entity.GetLinkEntities().Count(); var rightLinkCount = rightFetch.Entity.GetLinkEntities().Count() + 1; if (leftLinkCount + rightLinkCount > 10) { return(this); } // If we're doing a right outer join, switch everything round to do a left outer join // Also switch join order for inner joins to use N:1 relationships instead of 1:N to avoid problems with paging if (JoinType == QualifiedJoinType.RightOuter || JoinType == QualifiedJoinType.Inner && !rightAttributeParts[0].Equals(rightFetch.Alias, StringComparison.OrdinalIgnoreCase) || JoinType == QualifiedJoinType.Inner && leftAttribute == leftSchema.PrimaryKey && rightAttribute != rightSchema.PrimaryKey) { Swap(ref leftFetch, ref rightFetch); Swap(ref leftEntity, ref rightEntity); Swap(ref leftAttribute, ref rightAttribute); Swap(ref leftAttributeParts, ref rightAttributeParts); Swap(ref leftSchema, ref rightSchema); } // Must be joining to the root entity of the right source, i.e. not a child link-entity if (!rightAttributeParts[0].Equals(rightFetch.Alias, StringComparison.OrdinalIgnoreCase)) { return(this); } // If there are any additional join criteria, either they must be able to be translated to FetchXml criteria // in the new link entity or we must be using an inner join so we can use a post-filter node var additionalCriteria = AdditionalJoinCriteria; var additionalLinkEntities = new Dictionary <object, List <FetchLinkEntityType> >(); if (TranslateFetchXMLCriteria(dataSource.Metadata, options, additionalCriteria, rightSchema, rightFetch.Alias, rightEntity.name, rightFetch.Alias, rightEntity.Items, out var filter, additionalLinkEntities)) { rightEntity.AddItem(filter); foreach (var kvp in additionalLinkEntities) { if (kvp.Key is FetchEntityType e) { foreach (var le in kvp.Value) { rightEntity.AddItem(le); } } else { foreach (var le in kvp.Value) { ((FetchLinkEntityType)kvp.Key).AddItem(le); } } } additionalCriteria = null; } if (additionalCriteria != null && JoinType != QualifiedJoinType.Inner) { return(this); } var rightLinkEntity = new FetchLinkEntityType { alias = rightFetch.Alias, name = rightEntity.name, linktype = JoinType == QualifiedJoinType.Inner ? "inner" : "outer", from = rightAttributeParts[1].ToLowerInvariant(), to = leftAttributeParts[1].ToLowerInvariant(), Items = rightEntity.Items }; // Find where the two FetchXml documents should be merged together and return the merged version if (leftAttributeParts[0].Equals(leftFetch.Alias)) { if (leftEntity.Items == null) { leftEntity.Items = new object[] { rightLinkEntity } } ; else { leftEntity.Items = leftEntity.Items.Concat(new object[] { rightLinkEntity }).ToArray(); } } else { var leftLinkEntity = leftFetch.Entity.FindLinkEntity(leftAttributeParts[0]); if (leftLinkEntity == null) { return(this); } if (leftLinkEntity.Items == null) { leftLinkEntity.Items = new object[] { rightLinkEntity } } ; else { leftLinkEntity.Items = leftLinkEntity.Items.Concat(new object[] { rightLinkEntity }).ToArray(); } } if (additionalCriteria != null) { return new FilterNode { Filter = additionalCriteria, Source = leftFetch } }
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints) { if (_folded) { return(this); } Source = Source.FoldQuery(dataSources, options, parameterTypes, hints); Source.Parent = this; // Special case for using RetrieveTotalRecordCount instead of FetchXML if (options.UseRetrieveTotalRecordCount && Source is FetchXmlScan fetch && (fetch.Entity.Items == null || fetch.Entity.Items.Length == 0) && GroupBy.Count == 0 && Aggregates.Count == 1 && Aggregates.Single().Value.AggregateType == AggregateType.CountStar && dataSources[fetch.DataSource].Metadata[fetch.Entity.name].DataProviderId == null) // RetrieveTotalRecordCountRequest is not valid for virtual entities { var count = new RetrieveTotalRecordCountNode { DataSource = fetch.DataSource, EntityName = fetch.Entity.name }; var countName = count.GetSchema(dataSources, parameterTypes).Schema.Single().Key; if (countName == Aggregates.Single().Key) { return(count); } var rename = new ComputeScalarNode { Source = count, Columns = { [Aggregates.Single().Key] = new ColumnReferenceExpression { MultiPartIdentifier = new MultiPartIdentifier { Identifiers = { new Identifier { Value = countName } } } } } }; count.Parent = rename; return(rename); } if (Source is FetchXmlScan || Source is ComputeScalarNode computeScalar && computeScalar.Source is FetchXmlScan) { // Check if all the aggregates & groupings can be done in FetchXML. Can only convert them if they can ALL // be handled - if any one needs to be calculated manually, we need to calculate them all. var canUseFetchXmlAggregate = true; // Also track if we can partition the query for larger source data sets. We can't partition DISTINCT aggregates, // and need to transform AVG(field) to SUM(field) / COUNT(field) var canPartition = true; foreach (var agg in Aggregates) { if (agg.Value.SqlExpression != null && !(agg.Value.SqlExpression is ColumnReferenceExpression)) { canUseFetchXmlAggregate = false; break; } if (agg.Value.Distinct && agg.Value.AggregateType != AggregateType.Count) { canUseFetchXmlAggregate = false; break; } if (agg.Value.AggregateType == AggregateType.First) { canUseFetchXmlAggregate = false; break; } if (agg.Value.Distinct) { canPartition = false; } } var fetchXml = Source as FetchXmlScan; computeScalar = Source as ComputeScalarNode; var partnames = new Dictionary <string, FetchXml.DateGroupingType>(StringComparer.OrdinalIgnoreCase) { ["year"] = DateGroupingType.year, ["yy"] = DateGroupingType.year, ["yyyy"] = DateGroupingType.year, ["quarter"] = DateGroupingType.quarter, ["qq"] = DateGroupingType.quarter, ["q"] = DateGroupingType.quarter, ["month"] = DateGroupingType.month, ["mm"] = DateGroupingType.month, ["m"] = DateGroupingType.month, ["day"] = DateGroupingType.day, ["dd"] = DateGroupingType.day, ["d"] = DateGroupingType.day, ["week"] = DateGroupingType.week, ["wk"] = DateGroupingType.week, ["ww"] = DateGroupingType.week }; if (computeScalar != null) { fetchXml = (FetchXmlScan)computeScalar.Source; // Groupings may be on DATEPART function, which will have been split into separate Compute Scalar node. Check if all the scalar values // being computed are DATEPART functions that can be converted to FetchXML and are used as groupings foreach (var scalar in computeScalar.Columns) { if (!(scalar.Value is FunctionCall func) || !func.FunctionName.Value.Equals("DATEPART", StringComparison.OrdinalIgnoreCase) || func.Parameters.Count != 2 || !(func.Parameters[0] is ColumnReferenceExpression datePartType) || !(func.Parameters[1] is ColumnReferenceExpression datePartCol)) { canUseFetchXmlAggregate = false; break; } if (!GroupBy.Any(g => g.MultiPartIdentifier.Identifiers.Count == 1 && g.MultiPartIdentifier.Identifiers[0].Value == scalar.Key)) { canUseFetchXmlAggregate = false; break; } if (!partnames.ContainsKey(datePartType.GetColumnName())) { canUseFetchXmlAggregate = false; break; } // FetchXML dategrouping always uses local timezone. If we're using UTC we can't use it if (!options.UseLocalTimeZone) { canUseFetchXmlAggregate = false; break; } } } var metadata = dataSources[fetchXml.DataSource].Metadata; // FetchXML is translated to QueryExpression for virtual entities, which doesn't support aggregates if (metadata[fetchXml.Entity.name].DataProviderId != null) { canUseFetchXmlAggregate = false; } // Check FetchXML supports grouping by each of the requested attributes var fetchSchema = fetchXml.GetSchema(dataSources, parameterTypes); foreach (var group in GroupBy) { if (!fetchSchema.ContainsColumn(group.GetColumnName(), out var groupCol)) { continue; } var parts = groupCol.Split('.'); string entityName; if (parts[0] == fetchXml.Alias) { entityName = fetchXml.Entity.name; } else { entityName = fetchXml.Entity.FindLinkEntity(parts[0]).name; } var attr = metadata[entityName].Attributes.SingleOrDefault(a => a.LogicalName == parts[1]); // Can't group by virtual attributes if (attr == null || attr.AttributeOf != null) { canUseFetchXmlAggregate = false; } // Can't group by multi-select picklist attributes if (attr is MultiSelectPicklistAttributeMetadata) { canUseFetchXmlAggregate = false; } } var serializer = new XmlSerializer(typeof(FetchXml.FetchType)); if (canUseFetchXmlAggregate) { // FetchXML aggregates can trigger an AggregateQueryRecordLimitExceeded error. Clone the non-aggregate FetchXML // so we can try to run the native aggregate version but fall back to in-memory processing where necessary var clonedFetchXml = new FetchXmlScan { DataSource = fetchXml.DataSource, Alias = fetchXml.Alias, AllPages = fetchXml.AllPages, FetchXml = (FetchXml.FetchType)serializer.Deserialize(new StringReader(fetchXml.FetchXmlString)), ReturnFullSchema = fetchXml.ReturnFullSchema }; if (Source == fetchXml) { Source = clonedFetchXml; clonedFetchXml.Parent = this; } else { computeScalar.Source = clonedFetchXml; clonedFetchXml.Parent = computeScalar; } fetchXml.FetchXml.aggregate = true; fetchXml.FetchXml.aggregateSpecified = true; fetchXml.FetchXml = fetchXml.FetchXml; var schema = Source.GetSchema(dataSources, parameterTypes); foreach (var grouping in GroupBy) { var colName = grouping.GetColumnName(); var alias = grouping.MultiPartIdentifier.Identifiers.Last().Value; DateGroupingType?dateGrouping = null; if (computeScalar != null && computeScalar.Columns.TryGetValue(colName, out var datePart)) { dateGrouping = partnames[((ColumnReferenceExpression)((FunctionCall)datePart).Parameters[0]).GetColumnName()]; colName = ((ColumnReferenceExpression)((FunctionCall)datePart).Parameters[1]).GetColumnName(); } schema.ContainsColumn(colName, out colName); var attribute = fetchXml.AddAttribute(colName, a => a.groupbySpecified && a.groupby == FetchBoolType.@true && a.alias == alias, metadata, out _, out var linkEntity); attribute.groupby = FetchBoolType.@true; attribute.groupbySpecified = true; attribute.alias = alias; if (dateGrouping != null) { attribute.dategrouping = dateGrouping.Value; attribute.dategroupingSpecified = true; } else if (grouping.GetType(schema, null, parameterTypes) == typeof(SqlDateTime)) { // Can't group on datetime columns without a DATEPART specification canUseFetchXmlAggregate = false; } // Add a sort order for each grouping to allow consistent paging var items = linkEntity?.Items ?? fetchXml.Entity.Items; var sort = items.OfType <FetchOrderType>().FirstOrDefault(order => order.alias == alias); if (sort == null) { if (linkEntity == null) { fetchXml.Entity.AddItem(new FetchOrderType { alias = alias }); } else { linkEntity.AddItem(new FetchOrderType { alias = alias }); } } } foreach (var agg in Aggregates) { var col = (ColumnReferenceExpression)agg.Value.SqlExpression; var colName = col == null ? (fetchXml.Alias + "." + metadata[fetchXml.Entity.name].PrimaryIdAttribute) : col.GetColumnName(); if (!schema.ContainsColumn(colName, out colName)) { canUseFetchXmlAggregate = false; } var distinct = agg.Value.Distinct ? FetchBoolType.@true : FetchBoolType.@false; FetchXml.AggregateType aggregateType; switch (agg.Value.AggregateType) { case AggregateType.Average: aggregateType = FetchXml.AggregateType.avg; break; case AggregateType.Count: aggregateType = FetchXml.AggregateType.countcolumn; break; case AggregateType.CountStar: aggregateType = FetchXml.AggregateType.count; break; case AggregateType.Max: aggregateType = FetchXml.AggregateType.max; break; case AggregateType.Min: aggregateType = FetchXml.AggregateType.min; break; case AggregateType.Sum: aggregateType = FetchXml.AggregateType.sum; break; default: throw new ArgumentOutOfRangeException(); } // min, max, sum and avg are not supported for optionset attributes var parts = colName.Split('.'); string entityName; if (parts[0] == fetchXml.Alias) { entityName = fetchXml.Entity.name; } else { entityName = fetchXml.Entity.FindLinkEntity(parts[0]).name; } var attr = metadata[entityName].Attributes.SingleOrDefault(a => a.LogicalName == parts[1]); if (attr == null) { canUseFetchXmlAggregate = false; } if (attr is EnumAttributeMetadata && (aggregateType == FetchXml.AggregateType.avg || aggregateType == FetchXml.AggregateType.max || aggregateType == FetchXml.AggregateType.min || aggregateType == FetchXml.AggregateType.sum)) { canUseFetchXmlAggregate = false; } var attribute = fetchXml.AddAttribute(colName, a => a.aggregate == aggregateType && a.alias == agg.Key && a.distinct == distinct, metadata, out _, out _); attribute.aggregate = aggregateType; attribute.aggregateSpecified = true; attribute.alias = agg.Key; if (agg.Value.Distinct) { attribute.distinct = distinct; attribute.distinctSpecified = true; } } } // FoldQuery can be called again in some circumstances. Don't repeat the folding operation and create another try/catch _folded = true; // Check how we should execute this aggregate if the FetchXML aggregate fails or is not available. Use stream aggregate // for scalar aggregates or where all the grouping fields can be folded into sorts. var nonFetchXmlAggregate = FoldToStreamAggregate(dataSources, options, parameterTypes, hints); if (!canUseFetchXmlAggregate) { return(nonFetchXmlAggregate); } IDataExecutionPlanNode firstTry = fetchXml; // If the main aggregate query fails due to having over 50K records, check if we can retry with partitioning. We // need a createdon field to be available for this to work. if (canPartition) { canPartition = metadata[fetchXml.Entity.name].Attributes.Any(a => a.LogicalName == "createdon"); } if (canUseFetchXmlAggregate && canPartition) { // Create a clone of the aggregate FetchXML query var partitionedFetchXml = new FetchXmlScan { DataSource = fetchXml.DataSource, Alias = fetchXml.Alias, AllPages = fetchXml.AllPages, FetchXml = (FetchXml.FetchType)serializer.Deserialize(new StringReader(fetchXml.FetchXmlString)), ReturnFullSchema = fetchXml.ReturnFullSchema }; var partitionedAggregates = new PartitionedAggregateNode { Source = partitionedFetchXml }; partitionedFetchXml.Parent = partitionedAggregates; var partitionedResults = (IDataExecutionPlanNode)partitionedAggregates; partitionedAggregates.GroupBy.AddRange(GroupBy); foreach (var aggregate in Aggregates) { if (aggregate.Value.AggregateType != AggregateType.Average) { partitionedAggregates.Aggregates[aggregate.Key] = aggregate.Value; } else { // Rewrite AVG as SUM / COUNT partitionedAggregates.Aggregates[aggregate.Key + "_sum"] = new Aggregate { AggregateType = AggregateType.Sum, SqlExpression = aggregate.Value.SqlExpression }; partitionedAggregates.Aggregates[aggregate.Key + "_count"] = new Aggregate { AggregateType = AggregateType.Count, SqlExpression = aggregate.Value.SqlExpression }; if (partitionedResults == partitionedAggregates) { partitionedResults = new ComputeScalarNode { Source = partitionedAggregates }; partitionedAggregates.Parent = partitionedResults; } // Handle count = 0 => null ((ComputeScalarNode)partitionedResults).Columns[aggregate.Key] = new SearchedCaseExpression { WhenClauses = { new SearchedWhenClause { WhenExpression = new BooleanComparisonExpression { FirstExpression = (aggregate.Key + "_count").ToColumnReference(), ComparisonType = BooleanComparisonType.Equals, SecondExpression = new IntegerLiteral{ Value = "0" } }, ThenExpression = new NullLiteral() } }, ElseExpression = new BinaryExpression { FirstExpression = (aggregate.Key + "_sum").ToColumnReference(), BinaryExpressionType = BinaryExpressionType.Divide, SecondExpression = (aggregate.Key + "_count").ToColumnReference() } }; // Find the AVG expression in the FetchXML and replace with _sum and _count var avg = partitionedFetchXml.Entity.FindAliasedAttribute(aggregate.Key, null, out var linkEntity); var sumCount = new object[] { new FetchAttributeType { name = avg.name, alias = avg.alias + "_sum", aggregateSpecified = true, aggregate = FetchXml.AggregateType.sum }, new FetchAttributeType { name = avg.name, alias = avg.alias + "_count", aggregateSpecified = true, aggregate = FetchXml.AggregateType.countcolumn } }; if (linkEntity == null) { partitionedFetchXml.Entity.Items = partitionedFetchXml.Entity.Items .Except(new[] { avg }) .Concat(sumCount) .ToArray(); } else { linkEntity.Items = linkEntity.Items .Except(new[] { avg }) .Concat(sumCount) .ToArray(); } } } var tryPartitioned = new TryCatchNode { TrySource = firstTry, CatchSource = partitionedResults, ExceptionFilter = ex => GetOrganizationServiceFault(ex, out var fault) && IsAggregateQueryLimitExceeded(fault) }; partitionedResults.Parent = tryPartitioned; firstTry.Parent = tryPartitioned; firstTry = tryPartitioned; } var tryCatch = new TryCatchNode { TrySource = firstTry, CatchSource = nonFetchXmlAggregate, ExceptionFilter = ex => (ex is QueryExecutionException qee && (qee.InnerException is PartitionedAggregateNode.PartitionOverflowException || qee.InnerException is FetchXmlScan.InvalidPagingException)) || (GetOrganizationServiceFault(ex, out var fault) && IsAggregateQueryRetryable(fault)) }; firstTry.Parent = tryCatch; nonFetchXmlAggregate.Parent = tryCatch; return(tryCatch); } return(FoldToStreamAggregate(dataSources, options, parameterTypes, hints)); }
protected override IEnumerable<Entity> ExecuteInternal(IDictionary<string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary<string, Type> parameterTypes, IDictionary<string, object> parameterValues) { var schema = Source.GetSchema(dataSources, parameterTypes); var groupByCols = GetGroupingColumns(schema); var groups = new ConcurrentDictionary<Entity, Dictionary<string, AggregateFunctionState>>(new DistinctEqualityComparer(groupByCols)); InitializePartitionedAggregates(schema, parameterTypes); var aggregates = CreateAggregateFunctions(parameterValues, options, true); var fetchXmlNode = (FetchXmlScan)Source; var name = fetchXmlNode.Entity.name; var meta = dataSources[fetchXmlNode.DataSource].Metadata[name]; options.Progress(0, $"Partitioning {GetDisplayName(0, meta)}..."); // Get the minimum and maximum primary keys from the source var minKey = GetMinMaxKey(fetchXmlNode, dataSources, options, parameterTypes, parameterValues, false); var maxKey = GetMinMaxKey(fetchXmlNode, dataSources, options, parameterTypes, parameterValues, true); if (minKey.IsNull || maxKey.IsNull || minKey == maxKey) throw new QueryExecutionException("Cannot partition query"); // Add the filter to the FetchXML to partition the results fetchXmlNode.Entity.AddItem(new filter { Items = new object[] { new condition { attribute = "createdon", @operator = @operator.gt, value = "@PartitionStart" }, new condition { attribute = "createdon", @operator = @operator.le, value = "@PartitionEnd" } } }); var partitionParameterTypes = new Dictionary<string, Type> { ["@PartitionStart"] = typeof(SqlDateTime), ["@PartitionEnd"] = typeof(SqlDateTime) }; if (parameterTypes != null) { foreach (var kvp in parameterTypes) partitionParameterTypes[kvp.Key] = kvp.Value; } if (minKey > maxKey) throw new QueryExecutionException("Cannot partition query"); // Split recursively, add up values below & above split value if query returns successfully, or re-split on error // Range is > MinValue AND <= MaxValue, so start from just before first record to ensure the first record is counted var fullRange = new Partition { MinValue = minKey.Value.AddSeconds(-1), MaxValue = maxKey, Percentage = 1 }; _queue = new BlockingCollection<Partition>(); _pendingPartitions = 1; SplitPartition(fullRange); // Multi-thread where possible var org = dataSources[fetchXmlNode.DataSource].Connection; var maxDop = options.MaxDegreeOfParallelism; _lock = new object(); #if NETCOREAPP var svc = org as ServiceClient; if (maxDop <= 1 || svc == null || svc.ActiveAuthenticationType != Microsoft.PowerPlatform.Dataverse.Client.AuthenticationType.OAuth) { maxDop = 1; svc = null; } #else var svc = org as CrmServiceClient; if (maxDop <= 1 || svc == null || svc.ActiveAuthenticationType != Microsoft.Xrm.Tooling.Connector.AuthenticationType.OAuth) { maxDop = 1; svc = null; } #endif try { Parallel.For(0, maxDop, index => { var ds = new Dictionary<string, DataSource> { [fetchXmlNode.DataSource] = new DataSource { Connection = svc?.Clone() ?? org, Metadata = dataSources[fetchXmlNode.DataSource].Metadata, Name = fetchXmlNode.DataSource, TableSizeCache = dataSources[fetchXmlNode.DataSource].TableSizeCache } }; var fetch = new FetchXmlScan { Alias = fetchXmlNode.Alias, DataSource = fetchXmlNode.DataSource, FetchXml = CloneFetchXml(fetchXmlNode.FetchXml), Parent = this }; var partitionParameterValues = new Dictionary<string, object> { ["@PartitionStart"] = minKey, ["@PartitionEnd"] = maxKey }; if (parameterValues != null) { foreach (var kvp in parameterValues) partitionParameterValues[kvp.Key] = kvp.Value; } foreach (var partition in _queue.GetConsumingEnumerable()) { try { // Execute the query for this partition ExecuteAggregate(ds, options, partitionParameterTypes, partitionParameterValues, aggregates, groups, fetch, partition.MinValue, partition.MaxValue); lock (_lock) { _progress += partition.Percentage; options.Progress(0, $"Partitioning {GetDisplayName(0, meta)} ({_progress:P0})..."); } if (Interlocked.Decrement(ref _pendingPartitions) == 0) _queue.CompleteAdding(); } catch (Exception ex) { lock (_queue) { if (!GetOrganizationServiceFault(ex, out var fault)) { _queue.CompleteAdding(); throw; } if (!IsAggregateQueryLimitExceeded(fault)) { _queue.CompleteAdding(); throw; } SplitPartition(partition); } } } // Merge the stats from this clone of the FetchXML node so we can still see total number of executions etc. // in the main query plan. lock (fetchXmlNode) { fetchXmlNode.MergeStatsFrom(fetch); } }); } catch (AggregateException aggEx) { throw aggEx.InnerExceptions[0]; } foreach (var group in groups) { var result = new Entity(); for (var i = 0; i < groupByCols.Count; i++) result[groupByCols[i]] = group.Key[groupByCols[i]]; foreach (var aggregate in GetValues(group.Value)) result[aggregate.Key] = aggregate.Value; yield return result; } }
private void ExecuteAggregate(IDictionary<string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary<string, Type> parameterTypes, IDictionary<string, object> parameterValues, Dictionary<string, AggregateFunction> aggregates, ConcurrentDictionary<Entity, Dictionary<string, AggregateFunctionState>> groups, FetchXmlScan fetchXmlNode, SqlDateTime minValue, SqlDateTime maxValue) { parameterValues["@PartitionStart"] = minValue; parameterValues["@PartitionEnd"] = maxValue; var results = fetchXmlNode.Execute(dataSources, options, parameterTypes, parameterValues); foreach (var entity in results) { // Update aggregates var values = groups.GetOrAdd(entity, _ => ResetAggregates(aggregates)); lock (values) { foreach (var func in values.Values) func.AggregateFunction.NextPartition(entity, func.State); } } }
private string FindEntityWithAttributeAlias(FetchXmlScan fetchXml, string attrName) { return(FindEntityWithAttributeAlias(fetchXml.Alias, fetchXml.Entity.Items, attrName)); }