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