public static IEnumerable <FetchLinkEntityType> GetLinkEntities(this FetchEntityType entity)
{
foreach (var linkEntity in GetLinkEntities(entity.Items))
{
yield return(linkEntity);
}
}
private static string GetFilter(FetchEntityType entity, FetchXmlBuilder sender, string expandFilter)
{
var resultList = new StringBuilder();
var filteritems = entity.Items.Where(i => i is filter && ((filter)i).Items != null && ((filter)i).Items.Length > 0).ToList();
if (filteritems.Count > 0)
{
var and = true;
foreach (filter filteritem in filteritems)
{
resultList.Append(GetFilter(entity.name, filteritem, sender));
if (filteritem.type == filterType.or)
{
and = false;
}
}
var result = resultList.ToString();
if (result.StartsWith("(") && result.EndsWith(")"))
{
result = result.Substring(1, result.Length - 2);
}
if (!String.IsNullOrEmpty(expandFilter))
{
if (!and)
{
result = "(" + result + ")";
}
result += " and " + expandFilter;
}
return(result);
}
return(expandFilter);
}
private static string GetApply(FetchEntityType entity, FetchXmlBuilder sender)
{
var groups = entity.Items.OfType <FetchAttributeType>()
.Where(a => a.groupbySpecified)
.Select(a => a.name)
.ToList();
var aggregates = entity.Items.OfType <FetchAttributeType>()
.Where(a => a.aggregateSpecified)
.Select(a => $"aggregate({a.name} with {GetAggregateType(a.aggregate)} as {a.alias})")
.ToList();
if (groups.Count > 0)
{
var result = "groupby((" + String.Join(",", groups) + ")";
if (aggregates.Count > 0)
{
result += "," + String.Join(",", aggregates);
}
result += ")";
return(result);
}
return(String.Join(",", aggregates));
}
private static string GetAggregate(FetchEntityType entity, FetchXmlBuilder sender)
{
var groups = entity.Items.OfType <FetchAttributeType>()
.Where(a => a.groupbySpecified)
.Select(a => a.name)
.ToList();
var aggregates = entity.Items.OfType <FetchAttributeType>()
.Where(a => a.aggregateSpecified && a.aggregate != AggregateType.count)
.Select(a => $"{a.name} with {GetAggregateType(a.aggregate)} as {a.alias}")
.ToList();
aggregates.AddRange(entity.Items.OfType <FetchAttributeType>()
.Where(a => a.aggregateSpecified && a.aggregate == AggregateType.count)
.Select(a => $"$count as {a.alias}"));
var aggregateText = "aggregate(" + String.Join(",", aggregates) + ")";
if (groups.Count > 0)
{
var result = "groupby((" + String.Join(",", groups) + ")";
if (aggregates.Count > 0)
{
result += "," + aggregateText;
}
result += ")";
return(result);
}
return(aggregateText);
}
private static string GetExpand(FetchEntityType entity, FetchXmlBuilder sender, ref string select)
{
var result = "";
var linkitems = entity.Items.Where(i => i is FetchLinkEntityType).ToList();
if (linkitems.Count > 0)
{
foreach (FetchLinkEntityType linkitem in linkitems)
{
if (linkitem.linktype == "outer")
{
throw new Exception("OData queries do not support outer joins");
}
if (linkitem.Items != null)
{
if (!linkitem.intersect && linkitem.Items.Where(i => i is FetchLinkEntityType).ToList().Count > 0)
{
throw new Exception("OData queries only support one level of link entities");
}
if (linkitem.Items.Where(i => i is filter).ToList().Count > 0)
{
throw new Exception("OData queries do not support filter on link entities");
}
if (linkitem.Items.Where(i => i is FetchOrderType).ToList().Count > 0)
{
throw new Exception("OData queries do not support sorting on link entities");
}
}
var relation = LinkItemToRelation(entity.name, linkitem, sender);
result += relation.SchemaName + ",";
select += GetExpandedSelect(linkitem, relation.SchemaName, sender);
}
}
return(result);
}
private static void removeUnnecessaryLinksAttributes(savedqueryLayoutxmlGrid viewFeo, ViewExcelSheetsInfo excelData)
{
FetchEntityType currentEt = excelData.fetchObj.Items.Where(x => x is FetchEntityType).Select(x => (FetchEntityType)x).FirstOrDefault();
ViewsRelationsObj obj = excelData.relationsList.Where(x => x.relationAlias == null).FirstOrDefault();
if (currentEt != null && obj != null)
{
List <object> array = currentEt.Items.ToList();
for (int i = array.Count - 1; i > -1; i--)
{
object currentItem = array[i];
if (currentItem is FetchLinkEntityType)
{
FetchLinkEntityType link = currentItem as FetchLinkEntityType;
removeUnnecessaryAttributesInner(link, viewFeo);
if (link == null || link.visible == false)
{
if (link == null || link.Items.Count() == 0)
{
array.RemoveAt(i);
}
}
}
else if (currentItem is FetchAttributeType)
{
FetchAttributeType attr = currentItem as FetchAttributeType;
if (!(viewFeo.row.cell.Where(x => x.name == attr.name).Count() > 0) && attr.name != obj.entityMetadata.PrimaryIdAttribute)
{
array.RemoveAt(i);
}
}
}
currentEt.Items = array.ToArray();
}
}
private static string GetOrder(FetchEntityType entity, FetchXmlBuilder sender)
{
var orderitems = entity.Items
.OfType <FetchOrderType>()
.Where(i => i.attribute != null);
return(GetOrder(entity.name, sender, orderitems));
}
private static void updateViewRelatedObj(FetchType fetchType, string attributeName, List <ViewsRelationsObj> relationObj)
{
int index = attributeName.IndexOf(".");
if (index == -1)
{
if (relationObj.Where(x => x.relationAlias == null).Count() == 0)
{
foreach (object fetch in fetchType.Items)
{
if (fetch is FetchEntityType)
{
foreach (object obj in ((FetchEntityType)fetch).Items)
{
if (obj is FetchAttributeType && attributeName == ((FetchAttributeType)obj).name)
{
FetchEntityType fetchent = (FetchEntityType)fetch;
relationObj.Add(new ViewsRelationsObj()
{
entity = fetchent.name,
entityMetadata = GlobalOperations.Instance.CRMOpHelper.RetriveEntityAtrribute(fetchent.name)
});
}
}
}
}
}
}
else
{
string alias = attributeName.Substring(0, index);
if (relationObj.Where(x => x.relationAlias != null && attributeName.StartsWith(x.relationAlias)).Count() == 0)
{
foreach (object fetch in fetchType.Items)
{
foreach (object obj in ((FetchEntityType)fetch).Items)
{
if (obj is FetchLinkEntityType)
{
FetchLinkEntityType relation = getFechXmlFromLinks((FetchLinkEntityType)obj, attributeName);
if (relation != null)
{
relationObj.Add(new ViewsRelationsObj()
{
entity = relation.name,
entityMetadata = GlobalOperations.Instance.CRMOpHelper.RetriveEntityAtrribute(relation.name),
relationAlias = relation.alias,
relationFrom = relation.from,
relationTo = relation.to
});
}
}
}
}
}
}
}
private void RemoveAttributesAndOrders(FetchEntityType entity)
{
if (entity.Items != null)
{
entity.Items = entity.Items.Where(o => !(o is FetchAttributeType) && !(o is allattributes) && !(o is FetchOrderType)).ToArray();
foreach (var linkEntity in entity.Items.OfType<FetchLinkEntityType>())
RemoveAttributesAndOrders(linkEntity);
}
}
private static string GetSelect(FetchEntityType entity, FetchXmlBuilder sender)
{
var attributeitems = entity.Items
.OfType <FetchAttributeType>()
.Where(i => i.name != null);
var result = GetAttributeNames(entity.name, attributeitems, sender);
return(string.Join(",", result));
}
public static void AddItem(this FetchEntityType entity, object item)
{
if (entity.Items == null)
{
entity.Items = new[] { item }
}
;
else
{
entity.Items = entity.Items.Concat(new[] { item }).ToArray();
}
}
private static string GetSelect(FetchEntityType entity, FetchXmlBuilder sender)
{
var result = new List <string>();
var attributeitems = entity.Items.Where(i => i is FetchAttributeType && ((FetchAttributeType)i).name != null).ToList();
if (attributeitems.Count > 0)
{
foreach (FetchAttributeType attributeitem in attributeitems)
{
result.Add(LogicalToSchemaName(entity.name, attributeitem.name, sender));
}
}
return(string.Join(",", result));
}
private static string GetFilter(FetchEntityType entity, FetchXmlBuilder sender)
{
var result = "";
var filteritems = entity.Items.Where(i => i is filter && ((filter)i).Items != null && ((filter)i).Items.Length > 0).ToList();
if (filteritems.Count > 0)
{
foreach (filter filteritem in filteritems)
{
result += GetFilter(entity, filteritem, sender);
}
if (result.StartsWith("(") && result.EndsWith(")"))
{
result = result.Substring(1, result.Length - 2);
}
}
return(result);
}
private static List <string> GetSelect(FetchEntityType entity)
{
var result = new List <string>();
var attributeitems = entity.Items.Where(i => i is FetchAttributeType && ((FetchAttributeType)i).name != null).ToList();
if (attributeitems.Count > 0)
{
foreach (FetchAttributeType attributeitem in attributeitems)
{
result.Add(attributeitem.name);
}
}
else
{
result.Add("*");
}
return(result);
}
private static string GetOrder(FetchEntityType entity, FetchXmlBuilder sender)
{
var result = "";
var orderitems = entity.Items.Where(i => i is FetchOrderType && ((FetchOrderType)i).attribute != null).ToList();
if (orderitems.Count > 0)
{
foreach (FetchOrderType orderitem in orderitems)
{
result += LogicalToSchemaName(entity.name, orderitem.attribute, sender);
if (orderitem.descending)
{
result += " desc";
}
result += ",";
}
}
return(result);
}
public static void GenerateNewFechXml(ViewExcelSheetsInfo sheetView, EntityMetadata etMetadata)
{
FetchEntityType et = new FetchEntityType();
et.name = etMetadata.LogicalName;
et.Items = new object[3] {
new FetchOrderType()
{
attribute = etMetadata.PrimaryIdAttribute, descending = false
}, new FetchAttributeType()
{
name = etMetadata.PrimaryIdAttribute
}, new FetchAttributeType()
{
name = etMetadata.PrimaryNameAttribute
}
};
sheetView.fetchObj.Items = new object[] { et };
sheetView.fetchObj.version = "1.0";
sheetView.fetchObj.outputformat = FetchTypeOutputformat.xmlplatform;
sheetView.fetchObj.mapping = FetchTypeMapping.logical;
savedqueryLayoutxmlGridRowCell[] cells = new savedqueryLayoutxmlGridRowCell[1] {
new savedqueryLayoutxmlGridRowCell()
{
name = etMetadata.PrimaryNameAttribute, width = "150"
}
};
sheetView.viewObj.row = new savedqueryLayoutxmlGridRow()
{
cell = cells, name = "result", id = etMetadata.PrimaryIdAttribute
};
sheetView.viewObj.select = "1";
sheetView.viewObj.preview = "1";
sheetView.viewObj.@object = etMetadata.ObjectTypeCode.ToString();
sheetView.viewObj.icon = "1";
sheetView.viewObj.jump = etMetadata.PrimaryNameAttribute;
sheetView.viewObj.name = "resultset";
}
private static string GetFilter(FetchEntityType entity, filter filteritem, FetchXmlBuilder sender)
{
var result = "";
if (filteritem.Items == null || filteritem.Items.Length == 0)
{
return("");
}
var logical = filteritem.type == filterType.or ? " or " : " and ";
if (filteritem.Items.Length > 1)
{
result = "(";
}
foreach (var item in filteritem.Items)
{
if (item is condition)
{
result += GetCondition(entity, item as condition, sender);
}
else if (item is filter)
{
result += GetFilter(entity, item as filter, sender);
}
result += logical;
}
if (result.EndsWith(logical))
{
result = result.Substring(0, result.Length - logical.Length);
}
if (filteritem.Items.Length > 1)
{
result += ")";
}
return(result);
}
private static string GetFilter(FetchEntityType entity, filter filteritem, FetchXmlBuilder sender)
{
var result = "";
if (filteritem.Items == null || filteritem.Items.Length == 0)
{
return "";
}
var logical = filteritem.type == filterType.or ? " or " : " and ";
if (filteritem.Items.Length > 1)
{
result = "(";
}
foreach (var item in filteritem.Items)
{
if (item is condition)
{
result += GetCondition(entity, item as condition, sender);
}
else if (item is filter)
{
result += GetFilter(entity, item as filter, sender);
}
result += logical;
}
if (result.EndsWith(logical))
{
result = result.Substring(0, result.Length - logical.Length);
}
if (filteritem.Items.Length > 1)
{
result += ")";
}
return result;
}
private static string GetCondition(FetchEntityType entity, condition condition, FetchXmlBuilder sender)
{
var result = "";
if (!string.IsNullOrEmpty(condition.attribute))
{
GetEntityMetadata(entity.name, sender);
var attrMeta = FetchXmlBuilder.GetAttribute(entity.name, condition.attribute);
if (attrMeta == null)
{
throw new Exception($"No metadata for attribute: {entity.name}.{condition.attribute}");
}
result = attrMeta.SchemaName;
switch (attrMeta.AttributeType)
{
case AttributeTypeCode.Picklist:
case AttributeTypeCode.Money:
case AttributeTypeCode.State:
case AttributeTypeCode.Status:
result += "/Value";
break;
case AttributeTypeCode.Lookup:
result += "/Id";
break;
}
switch (condition.@operator)
{
case @operator.eq:
case @operator.ne:
case @operator.lt:
case @operator.le:
case @operator.gt:
case @operator.ge:
result += $" {condition.@operator} ";
break;
case @operator.neq:
result += " ne ";
break;
case @operator.@null:
result += " eq null";
break;
case @operator.notnull:
result += " ne null";
break;
case @operator.like:
result = $"substringof('{condition.value}', {attrMeta.SchemaName})";
break;
case @operator.notlike:
result = $"not substringof('{condition.value}', {attrMeta.SchemaName})";
break;
case @operator.@in:
case @operator.notin:
throw new Exception($"Condition operator '{condition.@operator}' is not yet supported by the OData generator");
default:
throw new Exception($"Unsupported OData condition operator '{condition.@operator}'");
}
if (!string.IsNullOrEmpty(condition.value) && condition.@operator != @operator.like && condition.@operator != @operator.notlike)
{
switch (attrMeta.AttributeType)
{
case AttributeTypeCode.Money:
case AttributeTypeCode.BigInt:
case AttributeTypeCode.Boolean:
case AttributeTypeCode.Decimal:
case AttributeTypeCode.Double:
case AttributeTypeCode.Integer:
case AttributeTypeCode.State:
case AttributeTypeCode.Status:
case AttributeTypeCode.Picklist:
result += condition.value;
break;
case AttributeTypeCode.Uniqueidentifier:
case AttributeTypeCode.Lookup:
case AttributeTypeCode.Customer:
case AttributeTypeCode.Owner:
result += $"(guid'{condition.value}')";
break;
case AttributeTypeCode.DateTime:
var date = DateTime.Parse(condition.value);
var datestr = string.Empty;
if (date.Equals(date.Date))
{
datestr = date.ToString("yyyy-MM-dd");
}
else
{
datestr = date.ToString("o");
}
result += $"datetime'{datestr}'";
break;
default:
result += $"'{condition.value}'";
break;
}
}
}
return(result);
}
private static void addNewAttributeToFech(FetchType fecthObj, string attributeName, ViewsRelationsObj currentEntity)
{
if (string.IsNullOrEmpty(currentEntity.relationAlias))
{
FetchEntityType currentEt = fecthObj.Items.Where(x => x is FetchEntityType && ((FetchEntityType)x).name.Equals(currentEntity.entity, StringComparison.InvariantCultureIgnoreCase)).Select(x => (FetchEntityType)x).FirstOrDefault();
if (currentEt != null)
{
List <object> items = currentEt.Items.ToList();
FetchAttributeType attribute = items.Where(x => x is FetchAttributeType && ((FetchAttributeType)x).name.Equals(attributeName)).Select(x => (FetchAttributeType)x).FirstOrDefault();
if (attribute == null)
{
List <object> objectList = currentEt.Items.ToList();
objectList.Add(new FetchAttributeType()
{
name = attributeName
});
currentEt.Items = objectList.ToArray();
}
}
}
else
{
FetchEntityType currentEt = fecthObj.Items.Where(x => x is FetchEntityType).Select(x => (FetchEntityType)x).FirstOrDefault();
if (currentEt != null)
{
FetchLinkEntityType currentLink = currentEt.Items.Where(x => x is FetchLinkEntityType &&
((FetchLinkEntityType)x).alias.Equals(currentEntity.relationAlias, StringComparison.InvariantCultureIgnoreCase))
.Select(x => (FetchLinkEntityType)x).FirstOrDefault();
if (currentLink != null)
{
List <object> items = currentLink.Items.ToList();
FetchAttributeType attribute = items.Where(x => x is FetchAttributeType && ((FetchAttributeType)x).name.Equals(attributeName)).Select(x => (FetchAttributeType)x).FirstOrDefault();
if (attribute == null)
{
List <object> objectList = currentLink.Items.ToList();
objectList.Add(new FetchAttributeType()
{
name = attributeName
});
currentLink.Items = objectList.ToArray();
}
}
else
{
List <object> objectList = currentEt.Items.ToList();
objectList.Add(new FetchLinkEntityType()
{
Items = new object[] { (new FetchAttributeType()
{
name = attributeName
}) },
name = currentEntity.entity,
alias = currentEntity.relationAlias,
linktype = "outer",
from = currentEntity.relationFrom,
to = currentEntity.relationTo,
intersect = false,
intersectSpecified = false,
visible = false,
visibleSpecified = false,
});
currentEt.Items = objectList.ToArray();
}
}
}
}
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);
}
private static string GetExpand(FetchEntityType entity, FetchXmlBuilder sender, ref string select)
{
var result = "";
var linkitems = entity.Items.Where(i => i is FetchLinkEntityType).ToList();
if (linkitems.Count > 0)
{
foreach (FetchLinkEntityType linkitem in linkitems)
{
if (linkitem.linktype== "outer")
{
throw new Exception("OData queries do not support outer joins");
}
if (linkitem.Items != null)
{
if (!linkitem.intersect && linkitem.Items.Where(i => i is FetchLinkEntityType).ToList().Count > 0)
{
throw new Exception("OData queries only support one level of link entities");
}
if (linkitem.Items.Where(i => i is filter).ToList().Count > 0)
{
throw new Exception("OData queries do not support filter on link entities");
}
if (linkitem.Items.Where(i => i is FetchOrderType).ToList().Count > 0)
{
throw new Exception("OData queries do not support sorting on link entities");
}
}
var relation = LinkItemToRelation(entity.name, linkitem, sender);
result += relation.SchemaName + ",";
select += GetExpandedSelect(linkitem, relation.SchemaName, sender);
}
}
return result;
}
private static string GetFilter(FetchEntityType entity, FetchXmlBuilder sender)
{
var result = "";
var filteritems = entity.Items.Where(i => i is filter && ((filter)i).Items != null && ((filter)i).Items.Length > 0).ToList();
if (filteritems.Count > 0)
{
foreach (filter filteritem in filteritems)
{
result += GetFilter(entity, filteritem, sender);
}
if (result.StartsWith("(") && result.EndsWith(")"))
{
result = result.Substring(1, result.Length - 2);
}
}
return result;
}
private static string GetSelect(FetchEntityType entity, FetchXmlBuilder sender)
{
var result = "";
var attributeitems = entity.Items.Where(i => i is FetchAttributeType && ((FetchAttributeType)i).name != null).ToList();
if (attributeitems.Count > 0)
{
foreach (FetchAttributeType attributeitem in attributeitems)
{
result += LogicalToSchemaName(entity.name, attributeitem.name, sender) + ",";
}
}
return result;
}
private static string GetExpand(FetchEntityType entity, FetchXmlBuilder sender, ref string filterString)
{
var resultList = new List <string>();
var linkitems = entity.Items.Where(i => i is FetchLinkEntityType).ToList();
if (linkitems.Count > 0)
{
foreach (FetchLinkEntityType linkitem in linkitems)
{
var navigationProperty = LinkItemToNavigationProperty(entity.name, linkitem, sender, out var child, out var manyToManyNextLink);
if (linkitem.Items != null)
{
if (!child)
{
if (linkitem.Items.Where(i => i is filter).ToList().Count > 0)
{
foreach (var filter in linkitem.Items.OfType <filter>())
{
foreach (var condition in filter.Items.OfType <condition>())
{
var targetLogicalName = linkitem.name;
GetEntityMetadata(targetLogicalName, sender);
if (!String.IsNullOrEmpty(filterString))
{
filterString += $" {filter.type} ";
}
filterString += GetCondition(linkitem.name, condition, sender, navigationProperty + "/");
}
}
}
if (linkitem.Items.Where(i => i is FetchOrderType).ToList().Count > 0)
{
throw new Exception("OData queries do not support sorting on parent link entities");
}
}
}
var currentLinkItem = linkitem;
if (manyToManyNextLink != null)
{
currentLinkItem = manyToManyNextLink; // For Many to Many relationship, get the link item of the 2nd link instead of the join table
}
var expandedSelect = GetExpandedSelect(currentLinkItem, sender);
var childFilter = child ? currentLinkItem.Items?.OfType <filter>().FirstOrDefault() : null;
var expandedFilter = childFilter == null ? null : GetFilter(currentLinkItem.name, childFilter, sender);
var childOrders = child ? currentLinkItem.Items?.OfType <FetchOrderType>().ToList() : null;
var expandedOrder = childOrders == null ? null : GetOrder(currentLinkItem.name, sender, childOrders);
if (String.IsNullOrEmpty(expandedSelect) && String.IsNullOrEmpty(expandedFilter) && String.IsNullOrEmpty(expandedOrder))
{
resultList.Add(navigationProperty);
}
else
{
var options = new List <string>();
if (!String.IsNullOrEmpty(expandedSelect))
{
options.Add(expandedSelect);
}
if (!String.IsNullOrEmpty(expandedFilter))
{
options.Add("$filter=" + expandedFilter);
}
if (!String.IsNullOrEmpty(expandedOrder))
{
options.Add("$orderby=" + expandedOrder);
}
resultList.Add(navigationProperty + "(" + String.Join(";", options) + ")");
}
}
}
return(string.Join(",", resultList));
}
public static FetchAttributeType FindAliasedAttribute(this FetchEntityType entity, string colName, Func <FetchAttributeType, bool> predicate, out FetchLinkEntityType linkEntity)
{
linkEntity = null;
return(FindAliasedAttribute(entity.Items, colName, predicate, ref linkEntity));
}
private static string GetOrder(FetchEntityType entity, FetchXmlBuilder sender)
{
var result = "";
var orderitems = entity.Items.Where(i => i is FetchOrderType && ((FetchOrderType)i).attribute != null).ToList();
if (orderitems.Count > 0)
{
foreach (FetchOrderType orderitem in orderitems)
{
result += LogicalToSchemaName(entity.name, orderitem.attribute, sender);
if (orderitem.descending)
{
result += " desc";
}
result += ",";
}
}
return result;
}
public static FetchLinkEntityType FindLinkEntity(this FetchEntityType entity, string alias)
{
return(FindLinkEntity(entity.Items, alias));
}
private static string GetExpand(FetchEntityType entity, FetchXmlBuilder sender, ref string filterString)
{
var resultList = new List <string>();
var linkitems = entity.Items.Where(i => i is FetchLinkEntityType).ToList();
if (linkitems.Count > 0)
{
foreach (FetchLinkEntityType linkitem in linkitems)
{
var navigationProperty = LinkItemToNavigationProperty(entity.name, linkitem, sender, out var child);
if (linkitem.Items != null)
{
if (!linkitem.intersect && linkitem.Items.Where(i => i is FetchLinkEntityType).ToList().Count > 0)
{
throw new Exception("OData queries only support one level of link entities");
}
if (!child)
{
if (linkitem.Items.Where(i => i is filter).ToList().Count > 0)
{
foreach (var filter in linkitem.Items.OfType <filter>())
{
foreach (var condition in filter.Items.OfType <condition>())
{
var targetLogicalName = linkitem.name;
GetEntityMetadata(targetLogicalName, sender);
if (condition.attribute == sender.entities[targetLogicalName].PrimaryIdAttribute)
{
if (!String.IsNullOrEmpty(filterString))
{
filterString += $" {filter.type} ";
}
filterString += navigationProperty + "/" + GetCondition(linkitem.name, condition, sender);
}
else
{
throw new Exception($"OData queries do not support filter on link entities except by primary key. Filter on {linkitem.name}.{condition.attribute} is not allowed");
}
}
}
}
if (linkitem.Items.Where(i => i is FetchOrderType).ToList().Count > 0)
{
throw new Exception("OData queries do not support sorting on parent link entities");
}
}
}
var expandedSelect = GetExpandedSelect(linkitem, sender);
var childFilter = child ? linkitem.Items?.OfType <filter>().FirstOrDefault() : null;
var expandedFilter = childFilter == null ? null : GetFilter(linkitem.name, childFilter, sender);
var childOrders = child ? linkitem.Items?.OfType <FetchOrderType>().ToList() : null;
var expandedOrder = childOrders == null ? null : GetOrder(linkitem.name, sender, childOrders);
if (String.IsNullOrEmpty(expandedSelect) && String.IsNullOrEmpty(expandedFilter) && String.IsNullOrEmpty(expandedOrder))
{
resultList.Add(navigationProperty);
}
else
{
var options = new List <string>();
if (!String.IsNullOrEmpty(expandedSelect))
{
options.Add("$select=" + expandedSelect);
}
if (!String.IsNullOrEmpty(expandedFilter))
{
options.Add("$filter=" + expandedFilter);
}
if (!String.IsNullOrEmpty(expandedOrder))
{
options.Add("$orderby=" + expandedOrder);
}
resultList.Add(navigationProperty + "(" + String.Join(";", options) + ")");
}
}
}
return(string.Join(",", resultList));
}
private static string GetCondition(FetchEntityType entity, condition condition, FetchXmlBuilder sender)
{
var result = "";
if (!string.IsNullOrEmpty(condition.attribute))
{
GetEntityMetadata(entity.name, sender);
var attrMeta = FetchXmlBuilder.GetAttribute(entity.name, condition.attribute);
if (attrMeta == null)
{
throw new Exception($"No metadata for attribute: {entity.name}.{condition.attribute}");
}
result = attrMeta.SchemaName;
switch (attrMeta.AttributeType)
{
case AttributeTypeCode.Picklist:
case AttributeTypeCode.Money:
case AttributeTypeCode.State:
case AttributeTypeCode.Status:
result += "/Value";
break;
case AttributeTypeCode.Lookup:
result += "/Id";
break;
}
switch (condition.@operator)
{
case @operator.eq:
case @operator.ne:
case @operator.lt:
case @operator.le:
case @operator.gt:
case @operator.ge:
result += $" {condition.@operator} ";
break;
case @operator.neq:
result += " ne ";
break;
case @operator.@null:
result += " eq null";
break;
case @operator.notnull:
result += " ne null";
break;
case @operator.like:
result = $"substringof('{condition.value}', {attrMeta.SchemaName})";
break;
case @operator.notlike:
result = $"not substringof('{condition.value}', {attrMeta.SchemaName})";
break;
case @operator.@in:
case @operator.notin:
throw new Exception($"Condition operator '{condition.@operator}' is not yet supported by the OData generator");
default:
throw new Exception($"Unsupported OData condition operator '{condition.@operator}'");
}
if (!string.IsNullOrEmpty(condition.value) && condition.@operator != @operator.like && condition.@operator != @operator.notlike)
{
switch (attrMeta.AttributeType)
{
case AttributeTypeCode.Money:
case AttributeTypeCode.BigInt:
case AttributeTypeCode.Boolean:
case AttributeTypeCode.Decimal:
case AttributeTypeCode.Double:
case AttributeTypeCode.Integer:
case AttributeTypeCode.State:
case AttributeTypeCode.Status:
case AttributeTypeCode.Picklist:
result += condition.value;
break;
case AttributeTypeCode.Uniqueidentifier:
case AttributeTypeCode.Lookup:
case AttributeTypeCode.Customer:
case AttributeTypeCode.Owner:
result += $"(guid'{condition.value}')";
break;
case AttributeTypeCode.DateTime:
result += $"datetime'{condition.value}'";
break;
default:
result += $"'{condition.value}'";
break;
}
}
}
return result;
}
