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); }