protected override INodeSchema GetRightSchema(IDictionary <string, DataSource> dataSources, IDictionary <string, Type> parameterTypes)
{
var leftSchema = LeftSource.GetSchema(dataSources, parameterTypes);
var innerParameterTypes = GetInnerParameterTypes(leftSchema, parameterTypes);
return(RightSource.GetSchema(dataSources, innerParameterTypes));
}
public override void AddRequiredColumns(IDictionary <string, DataSource> dataSources, IDictionary <string, Type> parameterTypes, IList <string> requiredColumns)
{
if (JoinCondition != null)
{
foreach (var col in JoinCondition.GetColumns())
{
if (!requiredColumns.Contains(col, StringComparer.OrdinalIgnoreCase))
{
requiredColumns.Add(col);
}
}
}
var leftSchema = LeftSource.GetSchema(dataSources, parameterTypes);
var leftColumns = requiredColumns
.Where(col => leftSchema.ContainsColumn(col, out _))
.Concat((IEnumerable <string>)OuterReferences?.Keys ?? Array.Empty <string>())
.Distinct()
.ToList();
var innerParameterTypes = GetInnerParameterTypes(leftSchema, parameterTypes);
var rightSchema = RightSource.GetSchema(dataSources, innerParameterTypes);
var rightColumns = requiredColumns
.Where(col => rightSchema.ContainsColumn(col, out _))
.Concat(DefinedValues.Values)
.Distinct()
.ToList();
LeftSource.AddRequiredColumns(dataSources, parameterTypes, leftColumns);
RightSource.AddRequiredColumns(dataSources, parameterTypes, rightColumns);
}
public override IDataExecutionPlanNode FoldQuery(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IList <OptimizerHint> hints)
{
var leftSchema = LeftSource.GetSchema(dataSources, parameterTypes);
LeftSource = LeftSource.FoldQuery(dataSources, options, parameterTypes, hints);
LeftSource.Parent = this;
var innerParameterTypes = GetInnerParameterTypes(leftSchema, parameterTypes);
RightSource = RightSource.FoldQuery(dataSources, options, innerParameterTypes, hints);
RightSource.Parent = this;
return(this);
}
protected virtual INodeSchema GetSchema(IDictionary <string, DataSource> dataSources, IDictionary <string, Type> parameterTypes, bool includeSemiJoin)
{
var outerSchema = LeftSource.GetSchema(dataSources, parameterTypes);
var innerSchema = GetRightSchema(dataSources, parameterTypes);
var schema = new NodeSchema();
if (JoinType == QualifiedJoinType.LeftOuter && SemiJoin)
{
schema.PrimaryKey = outerSchema.PrimaryKey;
}
foreach (var subSchema in new[] { outerSchema, innerSchema })
{
// Semi-join does not include data from the right source
if (SemiJoin && subSchema == innerSchema && !includeSemiJoin)
{
continue;
}
foreach (var column in subSchema.Schema)
{
schema.Schema[column.Key] = column.Value;
}
foreach (var alias in subSchema.Aliases)
{
if (!schema.Aliases.TryGetValue(alias.Key, out var aliasDetails))
{
aliasDetails = new List <string>();
schema.Aliases[alias.Key] = aliasDetails;
}
schema.Aliases[alias.Key].AddRange(alias.Value);
}
}
foreach (var definedValue in DefinedValues)
{
schema.Schema[definedValue.Key] = innerSchema.Schema[definedValue.Value];
}
return(schema);
}
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
}
}
protected override IEnumerable <Entity> ExecuteInternal(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues)
{
// https://sqlserverfast.com/epr/merge-join/
// Implemented inner, left outer, right outer and full outer variants
// Not implemented semi joins
// TODO: Handle many-to-many joins
// TODO: Handle union & concatenate
// Left & Right: GetNext, mark as unmatched
var leftSchema = LeftSource.GetSchema(dataSources, parameterTypes);
var rightSchema = RightSource.GetSchema(dataSources, parameterTypes);
var left = LeftSource.Execute(dataSources, options, parameterTypes, parameterValues).GetEnumerator();
var right = RightSource.Execute(dataSources, options, parameterTypes, parameterValues).GetEnumerator();
var mergedSchema = GetSchema(dataSources, parameterTypes, true);
var additionalJoinCriteria = AdditionalJoinCriteria?.Compile(mergedSchema, parameterTypes);
var hasLeft = left.MoveNext();
var hasRight = right.MoveNext();
var leftMatched = false;
var rightMatched = false;
var lt = new BooleanComparisonExpression
{
FirstExpression = LeftAttribute,
ComparisonType = BooleanComparisonType.LessThan,
SecondExpression = RightAttribute
}.Compile(mergedSchema, parameterTypes);
var eq = new BooleanComparisonExpression
{
FirstExpression = LeftAttribute,
ComparisonType = BooleanComparisonType.Equals,
SecondExpression = RightAttribute
}.Compile(mergedSchema, parameterTypes);
var gt = new BooleanComparisonExpression
{
FirstExpression = LeftAttribute,
ComparisonType = BooleanComparisonType.GreaterThan,
SecondExpression = RightAttribute
}.Compile(mergedSchema, parameterTypes);
while (!Done(hasLeft, hasRight))
{
// Compare key values
var merged = Merge(hasLeft ? left.Current : null, leftSchema, hasRight ? right.Current : null, rightSchema);
var isLt = lt(merged, parameterValues, options);
var isEq = eq(merged, parameterValues, options);
var isGt = gt(merged, parameterValues, options);
if (isLt || (hasLeft && !hasRight))
{
if (!leftMatched && (JoinType == QualifiedJoinType.LeftOuter || JoinType == QualifiedJoinType.FullOuter))
{
yield return(Merge(left.Current, leftSchema, null, rightSchema));
}
hasLeft = left.MoveNext();
leftMatched = false;
}
else if (isEq)
{
if ((!leftMatched || !SemiJoin) && (additionalJoinCriteria == null || additionalJoinCriteria(merged, parameterValues, options) == true))
{
yield return(merged);
}
leftMatched = true;
hasRight = right.MoveNext();
rightMatched = false;
}
else if (hasRight)
{
if (!rightMatched && (JoinType == QualifiedJoinType.RightOuter || JoinType == QualifiedJoinType.FullOuter))
{
yield return(Merge(null, leftSchema, right.Current, rightSchema));
}
hasRight = right.MoveNext();
rightMatched = false;
}
}
}
protected override IEnumerable <Entity> ExecuteInternal(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues)
{
var leftSchema = LeftSource.GetSchema(dataSources, parameterTypes);
var innerParameterTypes = GetInnerParameterTypes(leftSchema, parameterTypes);
if (OuterReferences != null)
{
if (parameterTypes == null)
{
innerParameterTypes = new Dictionary <string, Type>();
}
else
{
innerParameterTypes = new Dictionary <string, Type>(parameterTypes);
}
foreach (var kvp in OuterReferences)
{
innerParameterTypes[kvp.Value] = leftSchema.Schema[kvp.Key];
}
}
var rightSchema = RightSource.GetSchema(dataSources, innerParameterTypes);
var mergedSchema = GetSchema(dataSources, parameterTypes, true);
var joinCondition = JoinCondition?.Compile(mergedSchema, parameterTypes);
foreach (var left in LeftSource.Execute(dataSources, options, parameterTypes, parameterValues))
{
var innerParameters = parameterValues;
if (OuterReferences != null)
{
if (parameterValues == null)
{
innerParameters = new Dictionary <string, object>();
}
else
{
innerParameters = new Dictionary <string, object>(parameterValues);
}
foreach (var kvp in OuterReferences)
{
left.Attributes.TryGetValue(kvp.Key, out var outerValue);
innerParameters[kvp.Value] = outerValue;
}
}
var hasRight = false;
foreach (var right in RightSource.Execute(dataSources, options, innerParameterTypes, innerParameters))
{
var merged = Merge(left, leftSchema, right, rightSchema);
if (joinCondition == null || joinCondition(merged, parameterValues, options))
{
yield return(merged);
}
hasRight = true;
if (SemiJoin)
{
break;
}
}
if (!hasRight && JoinType == QualifiedJoinType.LeftOuter)
{
yield return(Merge(left, leftSchema, null, rightSchema));
}
}
}
protected override IEnumerable <Entity> ExecuteInternal(IDictionary <string, DataSource> dataSources, IQueryExecutionOptions options, IDictionary <string, Type> parameterTypes, IDictionary <string, object> parameterValues)
{
_hashTable = new Dictionary <object, List <OuterRecord> >();
var mergedSchema = GetSchema(dataSources, parameterTypes, true);
var additionalJoinCriteria = AdditionalJoinCriteria?.Compile(mergedSchema, parameterTypes);
// Build the hash table
var leftSchema = LeftSource.GetSchema(dataSources, parameterTypes);
leftSchema.ContainsColumn(LeftAttribute.GetColumnName(), out var leftCol);
foreach (var entity in LeftSource.Execute(dataSources, options, parameterTypes, parameterValues))
{
var key = entity[leftCol];
if (!_hashTable.TryGetValue(key, out var list))
{
list = new List <OuterRecord>();
_hashTable[key] = list;
}
list.Add(new OuterRecord {
Entity = entity
});
}
// Probe the hash table using the right source
var rightSchema = RightSource.GetSchema(dataSources, parameterTypes);
rightSchema.ContainsColumn(RightAttribute.GetColumnName(), out var rightCol);
foreach (var entity in RightSource.Execute(dataSources, options, parameterTypes, parameterValues))
{
var key = entity[rightCol];
var matched = false;
if (_hashTable.TryGetValue(key, out var list))
{
foreach (var left in list)
{
if (SemiJoin && left.Used)
{
continue;
}
var merged = Merge(left.Entity, leftSchema, entity, rightSchema);
if (additionalJoinCriteria == null || additionalJoinCriteria(merged, parameterValues, options))
{
yield return(merged);
left.Used = true;
matched = true;
}
}
}
if (!matched && (JoinType == QualifiedJoinType.RightOuter || JoinType == QualifiedJoinType.FullOuter))
{
yield return(Merge(null, leftSchema, entity, rightSchema));
}
}
if (JoinType == QualifiedJoinType.LeftOuter || JoinType == QualifiedJoinType.FullOuter)
{
foreach (var unmatched in _hashTable.SelectMany(kvp => kvp.Value).Where(e => !e.Used))
{
yield return(Merge(unmatched.Entity, leftSchema, null, rightSchema));
}
}
}