private static ControlValidationResult ValidateAttribute(TreeNode node, FetchXmlBuilder fxb)
{
var name = node.Value("name");
if (string.IsNullOrWhiteSpace(name))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Attribute Name must be included."));
}
var parententity = node.LocalEntityName();
if (fxb.entities != null)
{
if (fxb.GetAttribute(parententity, name) is AttributeMetadata metaatt)
{
if (metaatt.IsValidForGrid.HasValue && metaatt.IsValidForGrid.Value == false && metaatt.IsPrimaryId.Value != true)
{
return(new ControlValidationResult(ControlValidationLevel.Info, $"Attribute '{name}' has 'IsValidForGrid=false'."));
}
}
else
{
return(new ControlValidationResult(ControlValidationLevel.Warning, $"Attribute '{name}' is not in the table '{parententity}'."));
}
}
var alias = node.Value("alias");
if (node.IsFetchAggregate())
{
if (string.IsNullOrWhiteSpace(alias))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Aggregate should always have an Alias.", "https://docs.microsoft.com/en-us/powerapps/developer/data-platform/use-fetchxml-aggregation#about-aggregation"));
}
if (node.Value("groupby") == "true")
{
if (!HasSortOnAttribute(node))
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Aggregate queries should be sorted by all grouped attributes for correct paging.", "https://markcarrington.dev/2022/01/13/fetchxml-aggregate-queries-lookup-fields-and-paging/"));
}
if (fxb.GetAttribute(parententity, name) is LookupAttributeMetadata)
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Grouping by lookup columns can give inconsistent results across multiple pages.", "https://markcarrington.dev/2022/01/13/fetchxml-aggregate-queries-lookup-fields-and-paging/"));
}
}
}
else
{
if (!string.IsNullOrWhiteSpace(alias))
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Alias is not recommended for not Aggregate queries."));
}
if (node.IsFetchDistinct() && !HasSortOnAttribute(node) && !HasPrimaryIdAttribute(node.Parent, fxb))
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Distinct queries should be sorted by all attributes for correct paging.", "https://markcarrington.dev/2020/12/08/dataverse-paging-with-distinct/"));
}
}
return(null);
}
private static ControlValidationResult ValidateOrder(TreeNode node, FetchXmlBuilder fxb)
{
var attribute = node.Value("attribute");
var alias = node.Value("alias");
if (node.IsFetchAggregate())
{
if (string.IsNullOrWhiteSpace(alias))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Order Alias must be included in aggregate query.", "https://docs.microsoft.com/en-us/power-apps/developer/data-platform/use-fetchxml-aggregation#order-by"));
}
if (!string.IsNullOrWhiteSpace(attribute))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Order Name must NOT be included in aggregate query.", "https://docs.microsoft.com/en-us/power-apps/developer/data-platform/use-fetchxml-aggregation#order-by"));
}
}
else
{
if (string.IsNullOrWhiteSpace(attribute))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Order Name must be included."));
}
}
if (node.Parent.Name == "link-entity")
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Sorting on a link-entity triggers legacy paging.", "https://docs.microsoft.com/en-us/powerapps/developer/data-platform/org-service/paging-behaviors-and-ordering#ordering-and-multiple-table-queries"));
}
var parententity = node.LocalEntityName();
if (fxb.entities != null && !string.IsNullOrWhiteSpace(attribute))
{
if (fxb.GetAttribute(parententity, attribute) is AttributeMetadata metaatt)
{
}
else
{
return(new ControlValidationResult(ControlValidationLevel.Warning, $"Order Attribute '{attribute}' is not in the table '{parententity}'."));
}
}
if (node.IsFetchAggregate() && !string.IsNullOrWhiteSpace(alias))
{
var attr = node.Parent.Nodes.OfType <TreeNode>()
.Where(n => n.Name == "attribute" && n.Value("alias") == alias)
.FirstOrDefault();
if (attr != null &&
attr.Value("groupby") == "true" &&
fxb.GetAttribute(parententity, attr.Value("name")) is LookupAttributeMetadata)
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Sorting on a grouped lookup column may cause paging problems.", "https://markcarrington.dev/2022/01/13/fetchxml-aggregate-queries-lookup-fields-and-paging/"));
}
}
return(null);
}
private void SetupColumns()
{
columns = new Dictionary <string, AttributeItem>();
foreach (var entity in entities.Entities)
{
foreach (var attribute in entity.Attributes.Keys)
{
if (entity[attribute] is Guid && (Guid)entity[attribute] == entity.Id)
{
continue;
}
if (columns.ContainsKey(attribute))
{
continue;
}
var meta = FetchXmlBuilder.GetAttribute(entities.EntityName, attribute);
columns.Add(attribute, new AttributeItem(meta));
}
}
lvGrid.Columns.Clear();
var nohdr = lvGrid.Columns.Add("#", 20, HorizontalAlignment.Right);
lvGrid.Columns.Add("Id");
foreach (var col in columns)
{
lvGrid.Columns.Add(
form.currentSettings.gridFriendly &&
col.Value.Metadata != null &&
col.Value.Metadata.DisplayName != null &&
col.Value.Metadata.DisplayName.UserLocalizedLabel != null &&
col.Value.Metadata.DisplayName.UserLocalizedLabel.Label != null ?
col.Value.Metadata.DisplayName.UserLocalizedLabel.Label : col.Key);
}
}
private static string LogicalToSchemaName(string entity, string attribute, FetchXmlBuilder sender)
{
GetEntityMetadata(entity, sender);
var attrMeta = FetchXmlBuilder.GetAttribute(entity, attribute);
if (attrMeta == null)
{
throw new Exception($"No metadata for attribute: {entity}.{attribute}");
}
return(attrMeta.SchemaName);
}
private static ControlValidationResult ValidateLinkEntity(TreeNode node, FetchXmlBuilder fxb)
{
var name = node.Value("name");
if (string.IsNullOrWhiteSpace(name) ||
string.IsNullOrWhiteSpace(node.Value("to")) ||
string.IsNullOrWhiteSpace(node.Value("from")))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Link-Entity must include Name, To, From."));
}
if (node.Value("intersect") != "true" &&
fxb.GetAttribute(name, node.Value("from")) is AttributeMetadata fromAttr && fromAttr.IsPrimaryId == false)
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Links to records that aren't parents may cause paging issues.", "https://markcarrington.dev/2021/02/23/msdyn365-internals-paging-gotchas/#multiple_linked_entities"));
}
return(null);
}
private static ControlValidationResult ValidateCondition(TreeNode node, FetchXmlBuilder fxb)
{
var attribute = node.Value("attribute");
if (string.IsNullOrWhiteSpace(attribute))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Attribute must be included."));
}
var oper = node.Value("operator");
if (oper == "contains" || oper == "does-not-contain")
{
return(new ControlValidationResult(ControlValidationLevel.Error, $"Condition operator '{oper}' is not supported by FetchXml.", "https://docs.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml-schema"));
}
var entityname = node.Value("entityname");
if (!string.IsNullOrWhiteSpace(entityname) && !node.LocalEntityIsRoot())
{
return(new ControlValidationResult(ControlValidationLevel.Error, "Cannot enter Entity for Link-Entity condition."));
}
if (string.IsNullOrWhiteSpace(entityname) && fxb.entities != null)
{
var parententity = node.LocalEntityName();
if (fxb.GetAttribute(parententity, attribute) is AttributeMetadata metaatt)
{
if (metaatt.IsValidForGrid.HasValue && metaatt.IsValidForGrid.Value == false)
{
// return new ControlValidationResult(ControlValidationLevel.Error, $"Attribute '{attribute}' has 'IsValidForGrid=false'.");
}
}
else
{
return(new ControlValidationResult(ControlValidationLevel.Warning, $"Attribute '{attribute}' is not in the table '{parententity}'."));
}
}
return(null);
}
private static string GetCondition(string entityname, string entityalias, condition condition)
{
var result = new StringBuilder();
if (!string.IsNullOrEmpty(entityalias))
{
result.Append(entityalias);
result.Append(".");
}
if (!string.IsNullOrEmpty(condition.attribute))
{
if (!string.IsNullOrEmpty(condition.entityname))
{
result.Append($"{condition.entityname}.");
if (aliasmap.ContainsKey(condition.entityname))
{
entityname = aliasmap[condition.entityname];
}
else
{
entityname = condition.entityname;
}
}
result.Append(condition.attribute);
var attrMeta = FetchXmlBuilder.GetAttribute(entityname, condition.attribute);
if (attrMeta == null)
{
throw new Exception($"No metadata for attribute: {entityname}.{condition.attribute}");
}
switch (condition.@operator)
{
case @operator.eq:
case @operator.on:
result.Append(" = ");
break;
case @operator.ne:
case @operator.neq:
result.Append(" != ");
break;
case @operator.lt:
result.Append(" < ");
break;
case @operator.le:
case @operator.onorbefore:
result.Append(" <= ");
break;
case @operator.gt:
result.Append(" > ");
break;
case @operator.ge:
case @operator.onorafter:
result.Append(" >= ");
break;
case @operator.@null:
result.Append(" IS NULL");
break;
case @operator.notnull:
result.Append(" IS NOT NULL");
break;
case @operator.like:
result.Append(" LIKE ");
break;
case @operator.notlike:
result.Append(" NOT LIKE ");
break;
//case @operator.beginswith:
// result.Append(" LIKE \"{0}%\"");
// break;
//case @operator.@in:
// result.Append(" IN ");
// break;
//case @operator.notin:
// result.Append(" NOT IN ");
// break;
default:
throw new Exception($"Unsupported SQL condition operator '{condition.@operator}'");
}
if (!string.IsNullOrEmpty(condition.value))
{
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.Append(condition.value);
break;
default:
result.Append($"'{condition.value}'");
break;
}
}
}
return(result.ToString());
}
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 string GetCondition(string entityName, condition condition, FetchXmlBuilder fxb, object[] rootEntityItems, string navigationProperty = "")
{
var result = "";
if (!string.IsNullOrEmpty(condition.attribute))
{
if (!String.IsNullOrEmpty(condition.entityname))
{
var linkEntity = FindLinkEntity(entityName, rootEntityItems, fxb, condition.entityname, "", out navigationProperty, out var child);
if (linkEntity == null)
{
throw new Exception($"Cannot find filter entity " + condition.entityname);
}
if (child)
{
// Filtering a child collection separately has different semantics in OData vs. FetchXML, e.g.:
//
// <fetch top="50" >
// <entity name="account" >
// <attribute name="name" />
// <filter type="or" >
// <condition attribute="name" operator="eq" value="fxb" />
// <condition entityname="contact" attribute="firstname" operator="eq" value="jonas" />
// </filter>
// <link-entity name="contact" from="parentcustomerid" to="accountid" link-type="inner" >
// <attribute name="fullname" />
// <filter>
// <condition attribute="lastname" operator="eq" value="rapp" />
// </filter>
// </link-entity>
// </entity>
// </fetch>
//
// gives a result only where a contact matches both the firstname and lastname filters, unless the account name is "fxb"
// in which case only the lastname filter needs to match. By comparison, the similar OData query
//
// /accounts?$select=name&$expand=contact_customer_accounts($select=fullname;$filter=lastname eq 'rapp')&$filter=(name eq 'fxb' or contact_customer_accounts/any(o1:(o1/firstname eq 'jonas'))) and (contact_customer_accounts/any(o1:(o1/lastname eq 'rapp')))&$top=50
//
// applies the firstname and lastname filters separately on the full list of contacts, so as long as one contact matches the firstname
// filter it doesn't matter if it is the same record that matches the lastname filter.
throw new Exception("Cannot apply filter to child collection " + navigationProperty);
}
entityName = linkEntity.name;
}
GetEntityMetadata(entityName, fxb);
var attrMeta = fxb.GetAttribute(entityName, condition.attribute);
if (attrMeta == null)
{
throw new Exception($"No metadata for attribute: {entityName}.{condition.attribute}");
}
result = navigationProperty + GetPropertyName(attrMeta);
string function = null;
var functionParameters = 1;
var functionParameterType = typeof(string);
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:
case @operator.notlike:
var value = condition.value;
var func = "contains";
if (value.IndexOf('%') == value.Length - 1)
{
value = value.Substring(0, value.Length - 1);
func = "startswith";
}
else if (value.LastIndexOf('%') == 0)
{
value = value.Substring(1);
func = "endswith";
}
result = $"{func}({HttpUtility.UrlEncode(navigationProperty + attrMeta.LogicalName)}, {FormatValue(typeof(string), value)})";
if (condition.@operator == @operator.notlike)
{
result = "not " + result;
}
break;
case @operator.beginswith:
case @operator.notbeginwith:
result = $"startswith({HttpUtility.UrlEncode(navigationProperty + attrMeta.LogicalName)}, {FormatValue(typeof(string), condition.value)})";
if (condition.@operator == @operator.notbeginwith)
{
result = "not " + result;
}
break;
case @operator.endswith:
case @operator.notendwith:
result = $"endswith({HttpUtility.UrlEncode(navigationProperty + attrMeta.LogicalName)}, {FormatValue(typeof(string), condition.value)})";
if (condition.@operator == @operator.notendwith)
{
result = "not " + result;
}
break;
case @operator.above:
function = "Above";
break;
case @operator.eqorabove:
function = "AboveOrEqual";
break;
case @operator.between:
function = "Between";
functionParameters = Int32.MaxValue;
break;
case @operator.containvalues:
function = "ContainValues";
functionParameters = Int32.MaxValue;
break;
case @operator.notcontainvalues:
function = "DoesNotContainValues";
functionParameters = Int32.MaxValue;
break;
case @operator.eqbusinessid:
function = "EqualBusinessId";
functionParameters = 0;
break;
case @operator.equserid:
function = "EqualUserId";
functionParameters = 0;
break;
case @operator.equserlanguage:
function = "EqualUserLanguage";
functionParameters = 0;
break;
case @operator.equseroruserhierarchy:
function = "EqualUserOrUserHierarchy";
functionParameters = 0;
break;
case @operator.equseroruserhierarchyandteams:
function = "EqualUserOrUserHierarchyAndTeams";
functionParameters = 0;
break;
case @operator.equseroruserteams:
function = "EqualUserOrUserTeams";
functionParameters = 0;
break;
case @operator.equserteams:
function = "EqualUserTeams";
functionParameters = 0;
break;
case @operator.@in:
function = "In";
functionParameters = Int32.MaxValue;
break;
case @operator.infiscalperiod:
function = "InFiscalPeriod";
functionParameterType = typeof(long);
break;
case @operator.infiscalperiodandyear:
function = "InFiscalPeriodAndYear";
functionParameters = 2;
functionParameterType = typeof(long);
break;
case @operator.infiscalyear:
function = "InFiscalYear";
functionParameterType = typeof(long);
break;
case @operator.inorafterfiscalperiodandyear:
function = "InOrAfterFiscalPeriodAndYear";
functionParameters = 2;
functionParameterType = typeof(long);
break;
case @operator.inorbeforefiscalperiodandyear:
function = "InOrBeforeFiscalPeriodAndYear";
functionParameters = 2;
functionParameterType = typeof(long);
break;
case @operator.lastsevendays:
function = "Last7Days";
functionParameters = 0;
break;
case @operator.lastfiscalperiod:
function = "LastFiscalPeriod";
functionParameters = 0;
break;
case @operator.lastfiscalyear:
function = "LastFiscalYear";
functionParameters = 0;
break;
case @operator.lastmonth:
function = "LastMonth";
functionParameters = 0;
break;
case @operator.lastweek:
function = "LastWeek";
functionParameters = 0;
break;
case @operator.lastxdays:
function = "LastXDays";
functionParameterType = typeof(long);
break;
case @operator.lastxfiscalperiods:
function = "LastXFiscalPeriods";
functionParameterType = typeof(long);
break;
case @operator.lastxfiscalyears:
function = "LastXFiscalYears";
functionParameterType = typeof(long);
break;
case @operator.lastxhours:
function = "LastXHours";
functionParameterType = typeof(long);
break;
case @operator.lastxmonths:
function = "LastXMonths";
functionParameterType = typeof(long);
break;
case @operator.lastxweeks:
function = "LastXWeeks";
functionParameterType = typeof(long);
break;
case @operator.lastxyears:
function = "LastXYears";
functionParameterType = typeof(long);
break;
case @operator.lastyear:
function = "LastYear";
functionParameters = 0;
break;
case @operator.nextsevendays:
function = "Next7Days";
functionParameters = 0;
break;
case @operator.nextfiscalperiod:
function = "NextFiscalPeriod";
functionParameters = 0;
break;
case @operator.nextfiscalyear:
function = "NextFiscalYear";
functionParameters = 0;
break;
case @operator.nextmonth:
function = "NextMonth";
functionParameters = 0;
break;
case @operator.nextweek:
function = "NextWeek";
functionParameters = 0;
break;
case @operator.nextxdays:
function = "NextXDays";
functionParameterType = typeof(long);
break;
case @operator.nextxfiscalperiods:
function = "NextXFiscalPeriods";
functionParameterType = typeof(long);
break;
case @operator.nextxfiscalyears:
function = "NextXFiscalYears";
functionParameterType = typeof(long);
break;
case @operator.nextxhours:
function = "NextXHours";
functionParameterType = typeof(long);
break;
case @operator.nextxmonths:
function = "NextXMonths";
functionParameterType = typeof(long);
break;
case @operator.nextxweeks:
function = "NextXWeeks";
functionParameterType = typeof(long);
break;
case @operator.nextxyears:
function = "NextXYears";
functionParameterType = typeof(long);
break;
case @operator.nextyear:
function = "NextYear";
functionParameters = 0;
break;
case @operator.notbetween:
function = "NotBetween";
functionParameters = Int32.MaxValue;
break;
case @operator.nebusinessid:
function = "NotEqualBusinessId";
functionParameters = 0;
break;
case @operator.neuserid:
function = "NotEqualUserId";
functionParameters = 0;
break;
case @operator.notin:
function = "NotIn";
functionParameters = Int32.MaxValue;
break;
case @operator.notunder:
function = "NotUnder";
break;
case @operator.olderthanxdays:
function = "OlderThanXDays";
functionParameterType = typeof(long);
break;
case @operator.olderthanxhours:
function = "OlderThanXHours";
functionParameterType = typeof(long);
break;
case @operator.olderthanxminutes:
function = "OlderThanXMinutes";
functionParameterType = typeof(long);
break;
case @operator.olderthanxmonths:
function = "OlderThanXMonths";
functionParameterType = typeof(long);
break;
case @operator.olderthanxweeks:
function = "OlderThanXWeeks";
functionParameterType = typeof(long);
break;
case @operator.olderthanxyears:
function = "OlderThanXYears";
functionParameterType = typeof(long);
break;
case @operator.on:
function = "On";
break;
case @operator.onorafter:
function = "OnOrAfter";
break;
case @operator.onorbefore:
function = "OnOrBefore";
break;
case @operator.thisfiscalperiod:
function = "ThisFiscalPeriod";
functionParameters = 0;
break;
case @operator.thisfiscalyear:
function = "ThisFiscalYear";
functionParameters = 0;
break;
case @operator.thismonth:
function = "ThisMonth";
functionParameters = 0;
break;
case @operator.thisweek:
function = "ThisWeek";
functionParameters = 0;
break;
case @operator.thisyear:
function = "ThisYear";
functionParameters = 0;
break;
case @operator.today:
function = "Today";
functionParameters = 0;
break;
case @operator.tomorrow:
function = "Tomorrow";
functionParameters = 0;
break;
case @operator.under:
function = "Under";
break;
case @operator.eqorunder:
function = "UnderOrEqual";
break;
case @operator.yesterday:
function = "Yesterday";
functionParameters = 0;
break;
default:
throw new Exception($"Unsupported OData condition operator '{condition.@operator}'");
}
if (!String.IsNullOrEmpty(function))
{
if (functionParameters == Int32.MaxValue)
{
return($"Microsoft.Dynamics.CRM.{HttpUtility.UrlEncode(function)}(PropertyName='{HttpUtility.UrlEncode(navigationProperty + attrMeta.LogicalName)}',PropertyValues=[{String.Join(",", condition.Items.Select(i => FormatValue(functionParameterType, i.Value)))}])");
}
else if (functionParameters == 0)
{
return($"Microsoft.Dynamics.CRM.{HttpUtility.UrlEncode(function)}(PropertyName='{HttpUtility.UrlEncode(navigationProperty + attrMeta.LogicalName)}')");
}
else if (functionParameters == 1)
{
return($"Microsoft.Dynamics.CRM.{HttpUtility.UrlEncode(function)}(PropertyName='{HttpUtility.UrlEncode(navigationProperty + attrMeta.LogicalName)}',PropertyValue={FormatValue(functionParameterType, condition.value)})");
}
else
{
return($"Microsoft.Dynamics.CRM.{HttpUtility.UrlEncode(function)}(PropertyName='{HttpUtility.UrlEncode(navigationProperty + attrMeta.LogicalName)}',{String.Join(",", condition.Items.Select((i, idx) => $"Property{idx + 1}={FormatValue(functionParameterType, i.Value)}"))})");
}
}
if (!string.IsNullOrEmpty(condition.value) && !result.Contains("("))
{
var valueType = typeof(string);
switch (attrMeta.AttributeType)
{
case AttributeTypeCode.Money:
case AttributeTypeCode.Decimal:
valueType = typeof(decimal);
break;
case AttributeTypeCode.BigInt:
valueType = typeof(long);
break;
case AttributeTypeCode.Boolean:
valueType = typeof(bool);
break;
case AttributeTypeCode.Double:
valueType = typeof(double);
break;
case AttributeTypeCode.Integer:
case AttributeTypeCode.State:
case AttributeTypeCode.Status:
case AttributeTypeCode.Picklist:
valueType = typeof(int);
break;
case AttributeTypeCode.Uniqueidentifier:
case AttributeTypeCode.Lookup:
case AttributeTypeCode.Customer:
case AttributeTypeCode.Owner:
valueType = typeof(Guid);
break;
case AttributeTypeCode.DateTime:
valueType = typeof(DateTime);
break;
}
result += FormatValue(valueType, condition.value);
}
else if (!string.IsNullOrEmpty(condition.valueof))
{
result += condition.valueof;
}
}
return(result);
}
private static string GetCondition(string entityName, condition condition, FetchXmlBuilder sender)
{
var result = "";
if (!string.IsNullOrEmpty(condition.attribute))
{
if (!String.IsNullOrEmpty(condition.entityname))
{
throw new ApplicationException($"OData queries do not support filtering on link entities. If filtering on the primary key of an N:1 related entity, please add the filter to the link entity itself");
}
GetEntityMetadata(entityName, sender);
var attrMeta = sender.GetAttribute(entityName, condition.attribute);
if (attrMeta == null)
{
throw new Exception($"No metadata for attribute: {entityName}.{condition.attribute}");
}
result = GetPropertyName(attrMeta);
string function = null;
var functionParameters = 1;
var functionParameterType = typeof(string);
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 = $"contains({attrMeta.LogicalName}, '{condition.value}')";
break;
case @operator.notlike:
result = $"not contains({attrMeta.LogicalName}, '{condition.value}')";
break;
case @operator.beginswith:
result = $"startswith({attrMeta.LogicalName}, '{condition.value}')";
break;
case @operator.endswith:
result = $"endswith({attrMeta.LogicalName}, '{condition.value}')";
break;
case @operator.above:
function = "Above";
break;
case @operator.eqorabove:
function = "AboveOrEqual";
break;
case @operator.between:
function = "Between";
functionParameters = Int32.MaxValue;
break;
case @operator.containvalues:
function = "ContainsValues";
functionParameters = Int32.MaxValue;
break;
case @operator.notcontainvalues:
function = "DoesNotContainValues";
functionParameters = Int32.MaxValue;
break;
case @operator.eqbusinessid:
function = "EqualBusinessId";
functionParameters = 0;
break;
case @operator.equserid:
function = "EqualUserId";
functionParameters = 0;
break;
case @operator.equserlanguage:
function = "EqualUserLanguage";
functionParameters = 0;
break;
case @operator.equseroruserhierarchy:
function = "EqualUserOrUserHierarchy";
functionParameters = 0;
break;
case @operator.equseroruserhierarchyandteams:
function = "EqualUserOrUserHierarchyAndTeams";
functionParameters = 0;
break;
case @operator.equseroruserteams:
function = "EqualUserOrUserTeams";
functionParameters = 0;
break;
case @operator.equserteams:
function = "EqualUserTeams";
functionParameters = 0;
break;
case @operator.@in:
function = "In";
functionParameters = Int32.MaxValue;
break;
case @operator.infiscalperiod:
function = "InFiscalPeriod";
functionParameterType = typeof(long);
break;
case @operator.infiscalperiodandyear:
function = "InFiscalPeriodAndYear";
functionParameters = 2;
functionParameterType = typeof(long);
break;
case @operator.infiscalyear:
function = "InFiscalYear";
functionParameterType = typeof(long);
break;
case @operator.inorafterfiscalperiodandyear:
function = "InOrAfterFiscalPeriodAndYear";
functionParameters = 2;
functionParameterType = typeof(long);
break;
case @operator.inorbeforefiscalperiodandyear:
function = "InOrBeforeFiscalPeriodAndYear";
functionParameters = 2;
functionParameterType = typeof(long);
break;
case @operator.lastsevendays:
function = "Last7Days";
functionParameters = 0;
break;
case @operator.lastfiscalperiod:
function = "LastFiscalPeriod";
functionParameters = 0;
break;
case @operator.lastfiscalyear:
function = "LastFiscalYear";
functionParameters = 0;
break;
case @operator.lastmonth:
function = "LastMonth";
functionParameters = 0;
break;
case @operator.lastweek:
function = "LastWeek";
functionParameters = 0;
break;
case @operator.lastxdays:
function = "LastXDays";
functionParameterType = typeof(long);
break;
case @operator.lastxfiscalperiods:
function = "LastXFiscalPeriods";
functionParameterType = typeof(long);
break;
case @operator.lastxfiscalyears:
function = "LastXFiscalYears";
functionParameterType = typeof(long);
break;
case @operator.lastxhours:
function = "LastXHours";
functionParameterType = typeof(long);
break;
case @operator.lastxmonths:
function = "LastXMonths";
functionParameterType = typeof(long);
break;
case @operator.lastxweeks:
function = "LastXWeeks";
functionParameterType = typeof(long);
break;
case @operator.lastxyears:
function = "LastXYears";
functionParameterType = typeof(long);
break;
case @operator.lastyear:
function = "LastYear";
functionParameters = 0;
break;
case @operator.nextsevendays:
function = "Next7Days";
functionParameters = 0;
break;
case @operator.nextfiscalperiod:
function = "NextFiscalPeriod";
functionParameters = 0;
break;
case @operator.nextfiscalyear:
function = "NextFiscalYear";
functionParameters = 0;
break;
case @operator.nextmonth:
function = "NextMonth";
functionParameters = 0;
break;
case @operator.nextweek:
function = "NextWeek";
functionParameters = 0;
break;
case @operator.nextxdays:
function = "NextXDays";
functionParameterType = typeof(long);
break;
case @operator.nextxfiscalperiods:
function = "NextXFiscalPeriods";
functionParameterType = typeof(long);
break;
case @operator.nextxfiscalyears:
function = "NextXFiscalYears";
functionParameterType = typeof(long);
break;
case @operator.nextxhours:
function = "NextXHours";
functionParameterType = typeof(long);
break;
case @operator.nextxmonths:
function = "NextXMonths";
functionParameterType = typeof(long);
break;
case @operator.nextxweeks:
function = "NextXWeeks";
functionParameterType = typeof(long);
break;
case @operator.nextxyears:
function = "NextXYears";
functionParameterType = typeof(long);
break;
case @operator.nextyear:
function = "NextYear";
functionParameters = 0;
break;
case @operator.notbetween:
function = "NotBetween";
functionParameters = Int32.MaxValue;
break;
case @operator.nebusinessid:
function = "NotEqualBusinessId";
functionParameters = 0;
break;
case @operator.neuserid:
function = "NotEqualUserId";
functionParameters = 0;
break;
case @operator.notin:
function = "NotIn";
functionParameters = Int32.MaxValue;
break;
case @operator.notunder:
function = "NotUnder";
break;
case @operator.olderthanxdays:
function = "OlderThanXDays";
functionParameterType = typeof(long);
break;
case @operator.olderthanxhours:
function = "OlderThanXHours";
functionParameterType = typeof(long);
break;
case @operator.olderthanxminutes:
function = "OlderThanXMinutes";
functionParameterType = typeof(long);
break;
case @operator.olderthanxmonths:
function = "OlderThanXMonths";
functionParameterType = typeof(long);
break;
case @operator.olderthanxweeks:
function = "OlderThanXWeeks";
functionParameterType = typeof(long);
break;
case @operator.olderthanxyears:
function = "OlderThanXYears";
functionParameterType = typeof(long);
break;
case @operator.on:
function = "On";
break;
case @operator.onorafter:
function = "OnOrAfter";
break;
case @operator.onorbefore:
function = "OnOrBefore";
break;
case @operator.thisfiscalperiod:
function = "ThisFiscalPeriod";
functionParameters = 0;
break;
case @operator.thisfiscalyear:
function = "ThisFiscalYear";
functionParameters = 0;
break;
case @operator.thismonth:
function = "ThisMonth";
functionParameters = 0;
break;
case @operator.thisweek:
function = "ThisWeek";
functionParameters = 0;
break;
case @operator.thisyear:
function = "ThisYear";
functionParameters = 0;
break;
case @operator.today:
function = "Today";
functionParameters = 0;
break;
case @operator.tomorrow:
function = "Tomorrow";
functionParameters = 0;
break;
case @operator.under:
function = "Under";
break;
case @operator.eqorunder:
function = "UnderOrEqual";
break;
case @operator.yesterday:
function = "Yesterday";
functionParameters = 0;
break;
default:
throw new Exception($"Unsupported OData condition operator '{condition.@operator}'");
}
if (!String.IsNullOrEmpty(function))
{
if (functionParameters == Int32.MaxValue)
{
return($"Microsoft.Dynamics.CRM.{function}(PropertyName='{attrMeta.LogicalName}',PropertyValues=[{String.Join(",", condition.Items.Select(i => FormatValue(functionParameterType, i.Value)))}])");
}
else if (functionParameters == 0)
{
return($"Microsoft.Dynamics.CRM.{function}(PropertyName='{attrMeta.LogicalName}')");
}
else if (functionParameters == 1)
{
return($"Microsoft.Dynamics.CRM.{function}(PropertyName='{attrMeta.LogicalName}',PropertyValue={FormatValue(functionParameterType, condition.value)})");
}
else
{
return($"Microsoft.Dynamics.CRM.{function}(PropertyName='{attrMeta.LogicalName}',{String.Join(",", condition.Items.Select((i, idx) => $"Property{idx + 1}={FormatValue(functionParameterType, i.Value)}"))})");
}
}
if (!string.IsNullOrEmpty(condition.value) && !result.Contains("("))
{
var valueType = typeof(string);
switch (attrMeta.AttributeType)
{
case AttributeTypeCode.Money:
case AttributeTypeCode.Decimal:
valueType = typeof(decimal);
break;
case AttributeTypeCode.BigInt:
valueType = typeof(long);
break;
case AttributeTypeCode.Boolean:
valueType = typeof(bool);
break;
case AttributeTypeCode.Double:
valueType = typeof(double);
break;
case AttributeTypeCode.Integer:
case AttributeTypeCode.State:
case AttributeTypeCode.Status:
case AttributeTypeCode.Picklist:
valueType = typeof(int);
break;
case AttributeTypeCode.Uniqueidentifier:
case AttributeTypeCode.Lookup:
case AttributeTypeCode.Customer:
case AttributeTypeCode.Owner:
valueType = typeof(Guid);
break;
case AttributeTypeCode.DateTime:
valueType = typeof(DateTime);
break;
}
result += FormatValue(valueType, condition.value);
}
else if (!string.IsNullOrEmpty(condition.valueof))
{
result += condition.valueof;
}
}
return(result);
}
internal static ControlValidationResult GetWarning(TreeNode node, FetchXmlBuilder fxb)
{
if (!fxb.settings.ShowValidation)
{
return(null);
}
var name = node.Value("name");
var attribute = node.Value("attribute");
var alias = node.Value("alias");
var parententity = node.LocalEntityName();
switch (node.Name)
{
case "fetch":
break;
case "entity":
if (string.IsNullOrWhiteSpace(name))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Entity Name must be included."));
}
break;
case "link-entity":
if (string.IsNullOrWhiteSpace(name) ||
string.IsNullOrWhiteSpace(node.Value("to")) ||
string.IsNullOrWhiteSpace(node.Value("from")))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Link-Entity must include Name, To, From."));
}
if (node.Value("intersect") != "true" &&
fxb.GetAttribute(name, node.Value("from")) is AttributeMetadata fromAttr && fromAttr.IsPrimaryId == false)
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Links to records that aren't parents may cause paging issues.", "https://markcarrington.dev/2021/02/23/msdyn365-internals-paging-gotchas/#multiple_linked_entities"));
}
break;
case "attribute":
if (string.IsNullOrWhiteSpace(name))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Attribute Name must be included."));
}
if (fxb.entities != null)
{
if (fxb.GetAttribute(parententity, name) is AttributeMetadata metaatt)
{
if (metaatt.IsValidForGrid.Value == false && metaatt.IsPrimaryId.Value != true)
{
return(new ControlValidationResult(ControlValidationLevel.Warning, $"Attribute '{name}' has 'IsValidForGrid=false'."));
}
}
else
{
return(new ControlValidationResult(ControlValidationLevel.Warning, $"Attribute '{name}' is not in the table '{parententity}'."));
}
}
if (node.IsFetchAggregate())
{
if (string.IsNullOrWhiteSpace(alias))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Aggregate should always have an Alias.", "https://docs.microsoft.com/en-us/powerapps/developer/data-platform/use-fetchxml-aggregation#about-aggregation"));
}
if (node.Value("groupby") == "true")
{
if (!HasSortOnAttribute(node))
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Aggregate queries should be sorted by all grouped attributes for correct paging.", "https://markcarrington.dev/2022/01/13/fetchxml-aggregate-queries-lookup-fields-and-paging/"));
}
if (fxb.GetAttribute(parententity, name) is LookupAttributeMetadata)
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Grouping by lookup columns can give inconsistent results across multiple pages.", "https://markcarrington.dev/2022/01/13/fetchxml-aggregate-queries-lookup-fields-and-paging/"));
}
}
}
else
{
if (!string.IsNullOrWhiteSpace(alias))
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Alias is not recommended for not Aggregate queries."));
}
if (node.IsFetchDistinct() && !HasSortOnAttribute(node))
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Distinct queries should be sorted by all attributes for correct paging.", "https://markcarrington.dev/2020/12/08/dataverse-paging-with-distinct/"));
}
}
break;
case "filter":
if (node.Nodes.Count == 0)
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Filter shound have at least one Condition."));
}
break;
case "condition":
if (string.IsNullOrWhiteSpace(attribute))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Attribute must be included."));
}
var entityname = node.Value("entityname");
if (!string.IsNullOrWhiteSpace(entityname) && !node.LocalEntityIsRoot())
{
return(new ControlValidationResult(ControlValidationLevel.Error, "Cannot enter Entity for Link-Entity condition."));
}
if (string.IsNullOrWhiteSpace(entityname) && fxb.entities != null)
{
if (fxb.GetAttribute(parententity, attribute) is AttributeMetadata metaatt)
{
if (metaatt.IsValidForGrid.Value == false)
{
// return new ControlValidationResult(ControlValidationLevel.Error, $"Attribute '{attribute}' has 'IsValidForGrid=false'.");
}
}
else
{
return(new ControlValidationResult(ControlValidationLevel.Warning, $"Attribute '{attribute}' is not in the table '{parententity}'."));
}
}
break;
case "value":
if (string.IsNullOrWhiteSpace(node.Value("#text")))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Value should be added."));
}
break;
case "order":
if (string.IsNullOrWhiteSpace(attribute) && string.IsNullOrWhiteSpace(alias))
{
return(new ControlValidationResult(ControlValidationLevel.Warning, "Order Name must be included."));
}
if (node.Parent.Name == "link-entity")
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Sorting on a link-entity triggers legacy paging.", "https://docs.microsoft.com/en-us/powerapps/developer/data-platform/org-service/paging-behaviors-and-ordering#ordering-and-multiple-table-queries"));
}
if (fxb.entities != null)
{
if (fxb.GetAttribute(parententity, attribute) is AttributeMetadata metaatt)
{
}
else
{
return(new ControlValidationResult(ControlValidationLevel.Warning, $"Order Attribute '{attribute}' is not in the table '{parententity}'."));
}
}
if (node.IsFetchAggregate() && !string.IsNullOrWhiteSpace(alias))
{
var attr = node.Parent.Nodes.OfType <TreeNode>()
.Where(n => n.Name == "attribute" && n.Value("alias") == alias)
.FirstOrDefault();
if (attr != null &&
attr.Value("groupby") == "true" &&
fxb.GetAttribute(parententity, attr.Value("name")) is LookupAttributeMetadata)
{
return(new ControlValidationResult(ControlValidationLevel.Info, "Sorting on a grouped lookup column may cause paging problems.", "https://markcarrington.dev/2022/01/13/fetchxml-aggregate-queries-lookup-fields-and-paging/"));
}
}
break;
}
return(null);
}