Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
            }
        }
Пример #4
0
        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);
        }
Пример #5
0
        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);
        }
Пример #6
0
        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());
        }
Пример #8
0
        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);
        }
Пример #11
0
        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);
        }