New FetchXml enhancements and previously undocumented features

[jordimontana82]

This issue is a container for several FetchXml improvements that would probably be too big to address in a single issue, so it's a placeholder for investigating them, and will likely be implemented throughout several releases.

Mark has a great resource summarizing some of them: https://markcarrington.dev/2024/03/08/new-fetchxml-features-and-documentation/

The official FetchXml documentation can be found here: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/overview

Some of the new features that need investigation are:

Extra Llink operators

FXE currenly supports inner and outer (LEFT OUTER) joins. Since the query engine was implemented several other link types have been introduced.

• any
• not any
• all
• not all
• exists
• in
• matchfirstrowusingcrossapply

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/reference/link-entity#link-type-options https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/join-tables?tabs=fetchxml#use-exists-or-in-link-types

Order By

• New entityname attribute introduced allowing to arbitrarily sort by any linked entity column without relying on the order of appearance in the FetchXml

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/order-rows

Cross-Table column comparisons

• Implement ´value-of' in condition expressions where an attribute can be compared against to another column as opposed to a fixed value.

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/filter-rows?tabs=fetchxml#cross-table-comparisons

Other

Some other areas that need investigation to see if they are implemented:

• Column aliases: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/select-columns?tabs=sdk#column-aliases
• Intersect attribute in linked entities by which the selected columns are not returned: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/join-tables?tabs=fetchxml
• Linked-Entity limits: the maximum number of linked entities is 15 (used to be 10).
• Condition limits: the maximum number of conditions is 500, I have never ever seen such a querying needing that many conditions so maybe low priority.