private IDataExecutionPlanNode FoldToStreamAggregate(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
// Use stream aggregate where possible - if there are no grouping fields or the groups can be folded into sorts
var streamAggregate = new StreamAggregateNode {
Source = Source
};
streamAggregate.GroupBy.AddRange(GroupBy);
foreach (var aggregate in Aggregates)
{
streamAggregate.Aggregates[aggregate.Key] = aggregate.Value;
}
if (!IsScalarAggregate)
{
// Use hash grouping if explicitly requested with optimizer hint
if (hints != null && hints.Any(h => h.HintKind == OptimizerHintKind.HashGroup))
{
return(this);
}
var sorts = new SortNode {
Source = Source
};
foreach (var group in GroupBy)
{
sorts.Sorts.Add(new ExpressionWithSortOrder {
Expression = group, SortOrder = SortOrder.Ascending
});
}
streamAggregate.Source = sorts.FoldQuery(dataSources, options, parameterTypes, hints);
// Don't bother using a sort + stream aggregate if none of the sorts can be folded
if (streamAggregate.Source == sorts && sorts.PresortedCount == 0)
{
return(this);
}
}
return(streamAggregate);
}
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
var folded = base.FoldQuery(dataSources, options, parameterTypes, hints);
if (folded != this)
{
return(folded);
}
// Can't fold the join down into the FetchXML, so add a sort and try to fold that in instead
LeftSource = new SortNode
{
Source = LeftSource,
Sorts =
{
new ExpressionWithSortOrder
{
Expression = LeftAttribute,
SortOrder = SortOrder.Ascending
}
}
}.FoldQuery(dataSources, options, parameterTypes, hints);
LeftSource.Parent = this;
RightSource = new SortNode
{
Source = RightSource,
Sorts =
{
new ExpressionWithSortOrder
{
Expression = RightAttribute,
SortOrder = SortOrder.Ascending
}
}
}.FoldQuery(dataSources, options, parameterTypes, hints);
RightSource.Parent = this;
return(this);
}
private IDataExecutionPlanNode FoldSorts(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes)
{
var tryCatch = Source as TryCatchNode;
while (tryCatch?.TrySource is TryCatchNode subTry)
{
tryCatch = subTry;
}
if (tryCatch != null && tryCatch.TrySource is FetchXmlScan tryFetch && tryFetch.FetchXml.aggregate)
{
// We're sorting on the results of an aggregate that will try to go as a single FetchXML request first, then to a separate plan
// if that fails. Try to fold the sorts in to the aggregate FetchXML first
var fetchAggregateSort = new SortNode {
Source = tryFetch
};
fetchAggregateSort.Sorts.AddRange(Sorts);
var sortedFetchResult = fetchAggregateSort.FoldSorts(dataSources, options, parameterTypes);
// If we managed to fold any of the sorts in to the FetchXML, do the same for the non-FetchXML version and remove this node
if (sortedFetchResult == tryFetch || (sortedFetchResult == fetchAggregateSort && fetchAggregateSort.PresortedCount > 0))
{
tryCatch.TrySource = sortedFetchResult;
sortedFetchResult.Parent = tryCatch;
while (tryCatch != null)
{
var nonFetchAggregateSort = new SortNode {
Source = tryCatch.CatchSource
};
nonFetchAggregateSort.Sorts.AddRange(Sorts);
var sortedNonFetchResult = nonFetchAggregateSort.FoldSorts(dataSources, options, parameterTypes);
tryCatch.CatchSource = sortedNonFetchResult;
sortedNonFetchResult.Parent = tryCatch;
tryCatch = tryCatch.Parent as TryCatchNode;
}
return(Source);
}
}
// Allow folding sorts around filters and Compute Scalar (so long as sort is not on a calculated field)
var source = Source;
var fetchXml = Source as FetchXmlScan;
while (source != null && fetchXml == null)
{
if (source is FilterNode filter)
{
source = filter.Source;
}
else if (source is ComputeScalarNode computeScalar)
{
source = computeScalar.Source;
}
else
{
break;
}
fetchXml = source as FetchXmlScan;
}
if (fetchXml != null)
{
if (!dataSources.TryGetValue(fetchXml.DataSource, out var dataSource))
{
throw new QueryExecutionException("Missing datasource " + fetchXml.DataSource);
}
// Remove any existing sorts
if (fetchXml.Entity.Items != null)
{
fetchXml.Entity.Items = fetchXml.Entity.Items.Where(i => !(i is FetchOrderType)).ToArray();
foreach (var linkEntity in fetchXml.Entity.GetLinkEntities().Where(le => le.Items != null))
{
linkEntity.Items = linkEntity.Items.Where(i => !(i is FetchOrderType)).ToArray();
}
}
var fetchSchema = fetchXml.GetSchema(dataSources, parameterTypes);
var entity = fetchXml.Entity;
var items = entity.Items;
foreach (var sortOrder in Sorts)
{
if (!(sortOrder.Expression is ColumnReferenceExpression sortColRef))
{
return(this);
}
if (!fetchSchema.ContainsColumn(sortColRef.GetColumnName(), out var sortCol))
{
return(this);
}
var parts = sortCol.Split('.');
string entityName;
string attrName;
if (parts.Length == 2)
{
entityName = parts[0];
attrName = parts[1];
}
else
{
attrName = parts[0];
entityName = FindEntityWithAttributeAlias(fetchXml, attrName);
}
var fetchSort = new FetchOrderType {
attribute = attrName.ToLowerInvariant(), descending = sortOrder.SortOrder == SortOrder.Descending
};
if (fetchXml.FetchXml.aggregate)
{
fetchSort.alias = fetchSort.attribute;
fetchSort.attribute = null;
}
if (entityName == fetchXml.Alias)
{
if (items != entity.Items)
{
return(this);
}
if (fetchSort.attribute != null)
{
var meta = dataSource.Metadata[entity.name];
var attribute = meta.Attributes.SingleOrDefault(a => a.LogicalName == fetchSort.attribute && a.AttributeOf == null);
// Sorting on a lookup Guid column actually sorts by the associated name field, which isn't what we want
if (attribute is LookupAttributeMetadata || attribute is EnumAttributeMetadata || attribute is BooleanAttributeMetadata)
{
return(this);
}
// Sorting on multi-select picklist fields isn't supported in FetchXML
if (attribute is MultiSelectPicklistAttributeMetadata)
{
return(this);
}
// Sorts on the virtual ___name attribute should be applied to the underlying field
if (attribute == null && fetchSort.attribute.EndsWith("name") == true)
{
attribute = meta.Attributes.SingleOrDefault(a => a.LogicalName == fetchSort.attribute.Substring(0, fetchSort.attribute.Length - 4) && a.AttributeOf == null);
if (attribute != null)
{
fetchSort.attribute = attribute.LogicalName;
}
}
if (attribute == null)
{
return(this);
}
}
entity.AddItem(fetchSort);
items = entity.Items;
}
else
{
var linkEntity = FetchXmlExtensions.FindLinkEntity(items, entityName);
if (linkEntity == null)
{
return(this);
}
if (fetchSort.attribute != null)
{
var meta = dataSource.Metadata[linkEntity.name];
var attribute = meta.Attributes.SingleOrDefault(a => a.LogicalName == fetchSort.attribute && a.AttributeOf == null);
// Sorting on a lookup Guid column actually sorts by the associated name field, which isn't what we want
if (attribute is LookupAttributeMetadata || attribute is EnumAttributeMetadata || attribute is BooleanAttributeMetadata)
{
return(this);
}
// Sorting on multi-select picklist fields isn't supported in FetchXML
if (attribute is MultiSelectPicklistAttributeMetadata)
{
return(this);
}
// Sorts on the virtual ___name attribute should be applied to the underlying field
if (attribute == null && fetchSort.attribute.EndsWith("name") == true)
{
attribute = meta.Attributes.SingleOrDefault(a => a.LogicalName == fetchSort.attribute.Substring(0, fetchSort.attribute.Length - 4) && a.AttributeOf == null);
if (attribute != null)
{
fetchSort.attribute = attribute.LogicalName;
}
}
if (attribute == null)
{
return(this);
}
}
// Adding sorts to link-entity forces legacy paging which has a maximum record limit of 50K.
// Don't add a sort to a link-entity unless there's also a TOP clause of <= 50K
// Doesn't apply to aggregate queries as they can't be paged
if (!fetchXml.FetchXml.aggregate)
{
var top = Parent as TopNode;
var offset = Parent as OffsetFetchNode;
if (top == null && offset == null)
{
return(this);
}
if (top != null)
{
if (!top.Top.IsConstantValueExpression(null, options, out var topLiteral))
{
return(this);
}
if (Int32.Parse(topLiteral.Value, CultureInfo.InvariantCulture) > 50000)
{
return(this);
}
}
else if (offset != null)
{
if (!offset.Offset.IsConstantValueExpression(null, options, out var offsetLiteral) ||
!offset.Fetch.IsConstantValueExpression(null, options, out var fetchLiteral))
{
return(this);
}
if (Int32.Parse(offsetLiteral.Value, CultureInfo.InvariantCulture) + Int32.Parse(fetchLiteral.Value, CultureInfo.InvariantCulture) > 50000)
{
return(this);
}
}
}
linkEntity.AddItem(fetchSort);
items = linkEntity.Items;
}
PresortedCount++;
}
return(Source);
}
// Check if the data is already sorted by any prefix of our sorts
var schema = Source.GetSchema(dataSources, parameterTypes);
for (var i = 0; i < Sorts.Count && i < schema.SortOrder.Count; i++)
{
if (!(Sorts[i].Expression is ColumnReferenceExpression col))
{
return(this);
}
if (!schema.ContainsColumn(col.GetColumnName(), out var colName))
{
return(this);
}
if (!schema.SortOrder[i].Equals(colName, StringComparison.OrdinalIgnoreCase))
{
return(this);
}
PresortedCount++;
}
if (PresortedCount == Sorts.Count)
{
return(Source);
}
return(this);
}