public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
Source = Source.FoldQuery(dataSources, options, parameterTypes, hints);
// Combine multiple ComputeScalar nodes. Calculations in this node might be dependent on those in the previous node, so rewrite any references
// to the earlier computed columns
if (Source is ComputeScalarNode computeScalar)
{
var rewrites = new Dictionary <ScalarExpression, ScalarExpression>();
foreach (var prevCalc in computeScalar.Columns)
{
rewrites[prevCalc.Key.ToColumnReference()] = prevCalc.Value;
}
var rewrite = new RewriteVisitor(rewrites);
foreach (var calc in Columns)
{
computeScalar.Columns.Add(calc.Key, rewrite.ReplaceExpression(calc.Value));
}
return(computeScalar);
}
Source.Parent = this;
return(this);
}
internal static void ExpandWildcardColumns(IDataExecutionPlanNode source, List <SelectColumn> columnSet, IDictionary <string, DataSource> dataSources, IDictionary <string, Type> parameterTypes)
{
// Expand any AllColumns
if (columnSet.Any(col => col.AllColumns))
{
var schema = source.GetSchema(dataSources, parameterTypes);
var expanded = new List <SelectColumn>();
foreach (var col in columnSet)
{
if (!col.AllColumns)
{
expanded.Add(col);
continue;
}
foreach (var src in schema.Schema.Keys.Where(k => col.SourceColumn == null || k.StartsWith(col.SourceColumn.Replace("*", ""), StringComparison.OrdinalIgnoreCase)).OrderBy(k => k, StringComparer.OrdinalIgnoreCase))
{
expanded.Add(new SelectColumn
{
SourceColumn = src,
OutputColumn = src.Split('.').Last()
});
}
}
columnSet.Clear();
columnSet.AddRange(expanded);
}
}
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
Source = Source.FoldQuery(dataSources, options, parameterTypes, hints);
Source.Parent = this;
if (!Offset.IsConstantValueExpression(null, options, out var offsetLiteral) ||
!Fetch.IsConstantValueExpression(null, options, out var fetchLiteral))
{
return(this);
}
if (Source is FetchXmlScan fetchXml)
{
var offset = SqlTypeConverter.ChangeType <int>(offsetLiteral.Compile(null, null)(null, null, options));
var count = SqlTypeConverter.ChangeType <int>(fetchLiteral.Compile(null, null)(null, null, options));
var page = offset / count;
if (page * count == offset && count <= 5000)
{
fetchXml.FetchXml.count = count.ToString();
fetchXml.FetchXml.page = (page + 1).ToString();
fetchXml.AllPages = false;
return(fetchXml);
}
}
return(this);
}
private IEnumerable <IDataExecutionPlanNode> GetFoldableSources(IDataExecutionPlanNode source)
{
if (source is FetchXmlScan)
{
yield return(source);
yield break;
}
if (source is MetadataQueryNode)
{
yield return(source);
yield break;
}
if (source is BaseJoinNode join)
{
if (join.JoinType == QualifiedJoinType.Inner || join.JoinType == QualifiedJoinType.LeftOuter)
{
foreach (var subSource in GetFoldableSources(join.LeftSource))
{
yield return(subSource);
}
}
if (join.JoinType == QualifiedJoinType.Inner || join.JoinType == QualifiedJoinType.RightOuter)
{
foreach (var subSource in GetFoldableSources(join.RightSource))
{
yield return(subSource);
}
}
yield break;
}
if (source is HashMatchAggregateNode)
{
yield break;
}
if (source is TableSpoolNode)
{
yield break;
}
foreach (var subSource in source.GetSources().OfType <IDataExecutionPlanNode>())
{
foreach (var foldableSubSource in GetFoldableSources(subSource))
{
yield return(foldableSubSource);
}
}
}
private void FoldMetadataColumns(IDataExecutionPlanNode source, List <SelectColumn> columnSet)
{
if (source is MetadataQueryNode metadata)
{
// Check if there are any wildcard columns we can apply to the source metadata query
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);
if (metadata.MetadataSource.HasFlag(MetadataSource.Entity) && (hasStar || aliasStars.Contains(metadata.EntityAlias)))
{
if (metadata.Query.Properties == null)
{
metadata.Query.Properties = new MetadataPropertiesExpression();
}
metadata.Query.Properties.AllProperties = true;
}
if (metadata.MetadataSource.HasFlag(MetadataSource.Attribute) && (hasStar || aliasStars.Contains(metadata.AttributeAlias)))
{
if (metadata.Query.AttributeQuery == null)
{
metadata.Query.AttributeQuery = new AttributeQueryExpression();
}
if (metadata.Query.AttributeQuery.Properties == null)
{
metadata.Query.AttributeQuery.Properties = new MetadataPropertiesExpression();
}
metadata.Query.AttributeQuery.Properties.AllProperties = true;
}
if ((metadata.MetadataSource.HasFlag(MetadataSource.OneToManyRelationship) && (hasStar || aliasStars.Contains(metadata.OneToManyRelationshipAlias))) ||
(metadata.MetadataSource.HasFlag(MetadataSource.ManyToOneRelationship) && (hasStar || aliasStars.Contains(metadata.ManyToOneRelationshipAlias))) ||
(metadata.MetadataSource.HasFlag(MetadataSource.ManyToManyRelationship) && (hasStar || aliasStars.Contains(metadata.ManyToManyRelationshipAlias))))
{
if (metadata.Query.RelationshipQuery == null)
{
metadata.Query.RelationshipQuery = new RelationshipQueryExpression();
}
if (metadata.Query.RelationshipQuery.Properties == null)
{
metadata.Query.RelationshipQuery.Properties = new MetadataPropertiesExpression();
}
metadata.Query.RelationshipQuery.Properties.AllProperties = true;
}
}
}
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
Source = Source.FoldQuery(dataSources, options, parameterTypes, hints);
// These sorts will override any previous sort
if (Source is SortNode prevSort)
{
Source = prevSort.Source;
}
Source.Parent = this;
return(FoldSorts(dataSources, options, parameterTypes));
}
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
Source = Source.FoldQuery(dataSources, options, parameterTypes, hints);
Source.Parent = this;
SelectNode.FoldFetchXmlColumns(Source, ColumnSet, dataSources, parameterTypes);
SelectNode.ExpandWildcardColumns(Source, ColumnSet, dataSources, parameterTypes);
if (Source is FetchXmlScan fetchXml)
{
// Check if all the source and output column names match. If so, just change the alias of the source FetchXML
if (ColumnSet.All(col => col.SourceColumn == $"{fetchXml.Alias}.{col.OutputColumn}"))
{
fetchXml.Alias = Alias;
return(fetchXml);
}
}
return(this);
}
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)
{
Source = Source.FoldQuery(dataSources, options, parameterTypes, hints);
Source.Parent = this;
// Remove any duplicated column names
for (var i = Columns.Count - 1; i >= 0; i--)
{
if (Columns.IndexOf(Columns[i]) < i)
{
Columns.RemoveAt(i);
}
}
// If one of the fields to include in the DISTINCT calculation is the primary key, there is no possibility of duplicate
// rows so we can discard the distinct node
var schema = Source.GetSchema(dataSources, parameterTypes);
if (!String.IsNullOrEmpty(schema.PrimaryKey) && Columns.Contains(schema.PrimaryKey, StringComparer.OrdinalIgnoreCase))
{
return(Source);
}
if (Source is FetchXmlScan fetch)
{
fetch.FetchXml.distinct = true;
fetch.FetchXml.distinctSpecified = true;
// Ensure there is a sort order applied to avoid paging issues
if (fetch.Entity.Items == null || !fetch.Entity.Items.OfType <FetchOrderType>().Any())
{
// Sort by each attribute. Make sure we only add one sort per attribute, taking virtual attributes
// into account (i.e. don't attempt to sort on statecode and statecodename)
var sortedAttributes = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (var column in Columns)
{
if (!schema.ContainsColumn(column, out var normalized))
{
continue;
}
if (!sortedAttributes.Add(normalized))
{
continue;
}
var parts = normalized.Split('.');
if (parts.Length != 2)
{
continue;
}
if (parts[0].Equals(fetch.Alias, StringComparison.OrdinalIgnoreCase))
{
var attr = dataSources[fetch.DataSource].Metadata[fetch.Entity.name].Attributes.SingleOrDefault(a => a.LogicalName.Equals(parts[1], StringComparison.OrdinalIgnoreCase));
if (attr == null)
{
continue;
}
if (attr.AttributeOf != null && !sortedAttributes.Add(parts[0] + "." + attr.AttributeOf))
{
continue;
}
fetch.Entity.AddItem(new FetchOrderType {
attribute = parts[1]
});
}
else
{
var linkEntity = fetch.Entity.FindLinkEntity(parts[0]);
var attr = dataSources[fetch.DataSource].Metadata[linkEntity.name].Attributes.SingleOrDefault(a => a.LogicalName.Equals(parts[1], StringComparison.OrdinalIgnoreCase));
if (attr == null)
{
continue;
}
if (attr.AttributeOf != null && !sortedAttributes.Add(parts[0] + "." + attr.AttributeOf))
{
continue;
}
linkEntity.AddItem(new FetchOrderType {
attribute = parts[1]
});
}
}
}
return(fetch);
}
// If the data is already sorted by all the distinct columns we can use a stream aggregate instead.
// We don't mind what order the columns are sorted in though, so long as the distinct columns form a
// prefix of the sort order.
var requiredSorts = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (var col in Columns)
{
if (!schema.ContainsColumn(col, out var column))
{
return(this);
}
requiredSorts.Add(column);
}
if (!schema.IsSortedBy(requiredSorts))
{
return(this);
}
var aggregate = new StreamAggregateNode {
Source = Source
};
Source.Parent = aggregate;
for (var i = 0; i < requiredSorts.Count; i++)
{
aggregate.GroupBy.Add(schema.SortOrder[i].ToColumnReference());
}
return(aggregate);
}
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));
}
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
Source = Source.FoldQuery(dataSources, options, parameterTypes, hints);
Source.Parent = this;
// Foldable correlated IN queries "lefttable.column IN (SELECT righttable.column FROM righttable WHERE ...) are created as:
// Filter: Expr2 is not null
// -> FoldableJoin (LeftOuter SemiJoin) Expr2 = righttable.column in DefinedValues; righttable.column in RightAttribute
// -> FetchXml
// -> FetchXml (Distinct) orderby righttable.column
// Foldable correlated EXISTS filters "EXISTS (SELECT * FROM righttable WHERE righttable.column = lefttable.column AND ...) are created as:
// Filter - @var2 is not null
// -> NestedLoop(LeftOuter SemiJoin), null join condition. Outer reference(lefttable.column -> @var1), Defined values(@var2 -> rightttable.primarykey)
// -> FetchXml
// -> Top 1
// -> Index spool, SeekValue @var1, KeyColumn rightttable.column
// -> FetchXml
var joins = new List <BaseJoinNode>();
var join = Source as BaseJoinNode;
while (join != null)
{
joins.Add(join);
if (join is MergeJoinNode && join.LeftSource is SortNode sort)
{
join = sort.Source as BaseJoinNode;
}
else
{
join = join.LeftSource as BaseJoinNode;
}
}
var addedLinks = new List <FetchLinkEntityType>();
FetchXmlScan leftFetch;
if (joins.Count == 0)
{
leftFetch = null;
}
else
{
var lastJoin = joins.Last();
if (lastJoin is MergeJoinNode && lastJoin.LeftSource is SortNode sort)
{
leftFetch = sort.Source as FetchXmlScan;
}
else
{
leftFetch = lastJoin.LeftSource as FetchXmlScan;
}
}
while (leftFetch != null && joins.Count > 0)
{
join = joins.Last();
if (join.JoinType != QualifiedJoinType.LeftOuter ||
!join.SemiJoin)
{
break;
}
FetchLinkEntityType linkToAdd;
string leftAlias;
if (join is FoldableJoinNode merge)
{
// Check we meet all the criteria for a foldable correlated IN query
var rightSort = join.RightSource as SortNode;
var rightFetch = (rightSort?.Source ?? join.RightSource) as FetchXmlScan;
if (rightFetch == null)
{
break;
}
if (!leftFetch.DataSource.Equals(rightFetch.DataSource, StringComparison.OrdinalIgnoreCase))
{
break;
}
// Sorts could be folded into FetchXML or could be in separate node
string attribute;
if (rightSort != null)
{
if (rightSort.Sorts.Count != 1)
{
break;
}
if (!(rightSort.Sorts[0].Expression is ColumnReferenceExpression sortCol))
{
break;
}
attribute = sortCol.GetColumnName();
}
else
{
var rightSorts = (rightFetch.Entity.Items ?? Array.Empty <object>()).OfType <FetchOrderType>().ToList();
if (rightSorts.Count != 1)
{
break;
}
if (!String.IsNullOrEmpty(rightSorts[0].alias))
{
break;
}
attribute = $"{rightFetch.Alias}.{rightSorts[0].attribute}";
}
if (!merge.RightAttribute.GetColumnName().Equals(attribute, StringComparison.OrdinalIgnoreCase))
{
break;
}
var rightSchema = rightFetch.GetSchema(dataSources, parameterTypes);
// Right values need to be distinct - still allowed if it's the primary key
if (!rightFetch.FetchXml.distinct && rightSchema.PrimaryKey != attribute)
{
break;
}
var definedValueName = join.DefinedValues.SingleOrDefault(kvp => kvp.Value == attribute).Key;
if (definedValueName == null)
{
break;
}
var notNullFilter = FindNotNullFilter(Filter, definedValueName);
if (notNullFilter == null)
{
break;
}
// We can fold IN to a simple left outer join where the attribute is the primary key
if (!rightFetch.FetchXml.distinct && rightSchema.PrimaryKey == attribute)
{
// Replace the filter on the defined value name with a filter on the primary key column
notNullFilter.Expression = attribute.ToColumnReference();
linkToAdd = new FetchLinkEntityType
{
name = rightFetch.Entity.name,
alias = rightFetch.Alias,
from = merge.RightAttribute.MultiPartIdentifier.Identifiers.Last().Value,
to = merge.LeftAttribute.MultiPartIdentifier.Identifiers.Last().Value,
linktype = "outer",
Items = rightFetch.Entity.Items.Where(i => !(i is FetchOrderType)).ToArray()
};
}
else
{
// We need to use an "in" join type - check that's supported
if (!options.JoinOperatorsAvailable.Contains(JoinOperator.Any))
{
break;
}
// Remove the filter and replace with an "in" link-entity
Filter = Filter.RemoveCondition(notNullFilter);
linkToAdd = new FetchLinkEntityType
{
name = rightFetch.Entity.name,
alias = rightFetch.Alias,
from = merge.RightAttribute.MultiPartIdentifier.Identifiers.Last().Value,
to = merge.LeftAttribute.MultiPartIdentifier.Identifiers.Last().Value,
linktype = "in",
Items = rightFetch.Entity.Items.Where(i => !(i is FetchOrderType)).ToArray()
};
}
leftAlias = merge.LeftAttribute.MultiPartIdentifier.Identifiers.Reverse().Skip(1).First().Value;
// Remove the sort that has been merged into the left side too
if (leftFetch.Entity.Items != null)
{
leftFetch.Entity.Items = leftFetch.Entity
.Items
.Where(i => !(i is FetchOrderType sort) || !sort.attribute.Equals(merge.LeftAttribute.MultiPartIdentifier.Identifiers.Last().Value, StringComparison.OrdinalIgnoreCase))
.ToArray();
}
}
else if (join is NestedLoopNode loop)
{
// Check we meet all the criteria for a foldable correlated IN query
if (!options.JoinOperatorsAvailable.Contains(JoinOperator.Exists))
{
break;
}
if (loop.JoinCondition != null ||
loop.OuterReferences.Count != 1 ||
loop.DefinedValues.Count != 1)
{
break;
}
if (!(join.RightSource is TopNode top))
{
break;
}
if (!(top.Top is IntegerLiteral topLiteral) ||
topLiteral.Value != "1")
{
break;
}
if (!(top.Source is IndexSpoolNode indexSpool))
{
break;
}
if (indexSpool.SeekValue != loop.OuterReferences.Single().Value)
{
break;
}
if (!(indexSpool.Source is FetchXmlScan rightFetch))
{
break;
}
if (indexSpool.KeyColumn.Split('.').Length != 2 ||
!indexSpool.KeyColumn.Split('.')[0].Equals(rightFetch.Alias, StringComparison.OrdinalIgnoreCase))
{
break;
}
var notNullFilter = FindNotNullFilter(Filter, loop.DefinedValues.Single().Key);
if (notNullFilter == null)
{
break;
}
// Remove the filter and replace with an "exists" link-entity
Filter = Filter.RemoveCondition(notNullFilter);
linkToAdd = new FetchLinkEntityType
{
name = rightFetch.Entity.name,
alias = rightFetch.Alias,
from = indexSpool.KeyColumn.Split('.')[1],
to = loop.OuterReferences.Single().Key.Split('.')[1],
linktype = "exists",
Items = rightFetch.Entity.Items
};
leftAlias = loop.OuterReferences.Single().Key.Split('.')[0];
}
else
{
// This isn't a type of join we can fold as a correlated IN/EXISTS join
break;
}
// Remove any attributes from the new linkentity
var tempEntity = new FetchEntityType {
Items = new object[] { linkToAdd }
};
foreach (var link in tempEntity.GetLinkEntities())
{
link.Items = (link.Items ?? Array.Empty <object>()).Where(i => !(i is FetchAttributeType) && !(i is allattributes)).ToArray();
}
if (leftAlias.Equals(leftFetch.Alias, StringComparison.OrdinalIgnoreCase))
{
leftFetch.Entity.AddItem(linkToAdd);
}
else
{
leftFetch.Entity.FindLinkEntity(leftAlias).AddItem(linkToAdd);
}
addedLinks.Add(linkToAdd);
joins.Remove(join);
if (joins.Count == 0)
{
Source = leftFetch;
leftFetch.Parent = this;
}
else
{
join = joins.Last();
if (join is MergeJoinNode && join.LeftSource is SortNode sort)
{
sort.Source = leftFetch;
leftFetch.Parent = sort;
}
else
{
join.LeftSource = leftFetch;
leftFetch.Parent = join;
}
}
}
// If we've got a filter matching a column and a variable (key lookup in a nested loop) from a table spool, replace it with a index spool
if (Source is TableSpoolNode tableSpool)
{
var schema = Source.GetSchema(dataSources, parameterTypes);
if (ExtractKeyLookupFilter(Filter, out var filter, out var indexColumn, out var seekVariable) && schema.ContainsColumn(indexColumn, out indexColumn))
{
var spoolSource = tableSpool.Source;
// Index spool requires non-null key values
if (indexColumn != schema.PrimaryKey)
{
spoolSource = new FilterNode
{
Source = tableSpool.Source,
Filter = new BooleanIsNullExpression
{
Expression = indexColumn.ToColumnReference(),
IsNot = true
}
}.FoldQuery(dataSources, options, parameterTypes, hints);
}
Source = new IndexSpoolNode
{
Source = spoolSource,
KeyColumn = indexColumn,
SeekValue = seekVariable
};
Filter = filter;
}
}
// Find all the data source nodes we could fold this into. Include direct data sources, those from either side of an inner join, or the main side of an outer join
foreach (var source in GetFoldableSources(Source))
{
var schema = source.GetSchema(dataSources, parameterTypes);
if (source is FetchXmlScan fetchXml && !fetchXml.FetchXml.aggregate)
{
if (!dataSources.TryGetValue(fetchXml.DataSource, out var dataSource))
{
throw new NotSupportedQueryFragmentException("Missing datasource " + fetchXml.DataSource);
}
var additionalLinkEntities = new Dictionary <object, List <FetchLinkEntityType> >();
// If the criteria are ANDed, see if any of the individual conditions can be translated to FetchXML
Filter = ExtractFetchXMLFilters(dataSource.Metadata, options, Filter, schema, null, fetchXml.Entity.name, fetchXml.Alias, fetchXml.Entity.Items, out var fetchFilter, additionalLinkEntities);
if (fetchFilter != null)
{
fetchXml.Entity.AddItem(fetchFilter);
foreach (var kvp in additionalLinkEntities)
{
if (kvp.Key is FetchEntityType e)
{
foreach (var le in kvp.Value)
{
fetchXml.Entity.AddItem(le);
}
}
else
{
foreach (var le in kvp.Value)
{
((FetchLinkEntityType)kvp.Key).AddItem(le);
}
}
}
}
}
if (source is MetadataQueryNode meta)
{
// If the criteria are ANDed, see if any of the individual conditions can be translated to the metadata query
Filter = ExtractMetadataFilters(Filter, meta, options, out var entityFilter, out var attributeFilter, out var relationshipFilter);
meta.Query.AddFilter(entityFilter);
if (attributeFilter != null && meta.Query.AttributeQuery == null)
{
meta.Query.AttributeQuery = new AttributeQueryExpression();
}
meta.Query.AttributeQuery.AddFilter(attributeFilter);
if (relationshipFilter != null && meta.Query.RelationshipQuery == null)
{
meta.Query.RelationshipQuery = new RelationshipQueryExpression();
}
meta.Query.RelationshipQuery.AddFilter(relationshipFilter);
}
}
foreach (var addedLink in addedLinks)
{
addedLink.SemiJoin = true;
}
if (Filter == null)
{
return(Source);
}
return(this);
}