Microsoft have recently released a new in-depth set of documentation for FetchXML, and it includes some extra features that haven’t been supported before.
The new documentation contains examples and comparisons to SQL for each of the elements of the language, similar to my previous Inside FetchXML series.
Along with the previously well-understood features, the documentation also includes some information for the first time about previously undocumented features which could help unlock more powerful and efficient queries.
I’m not going to repeat the documentation here, please use the links to refer to all the latest content on Microsoft Learn. Instead I’ll just highlight some of the interesting new features.
Additional Join Types
I covered the in
, exists
and matchfirstrowusingcrossapply
join types in a blog article a few years ago, but now they are officially supported complete with examples and equivalent SQL 🎉
in
and exists
let you filter rows based on another table (something like “show me contacts that have had an email in the past month”) without having to deal with the multiple rows that would be introduced by an inner join.
matchfirstrowusingcrossapply
does a similar job, but allows you to actually get the data from a single row in the related table as well.
More Ordering Flexibility
When you add sort orders to a query that involves multiple tables, the results are sorted on the main table first, then by the linked tables. So if you have a query like:
<fetch> <entity name="account"> <attribute name="name" /> <link-entity name="contact" from="parentcustomerid" to="accountid"> <attribute name="fullname" /> <order attribute="fullname" /> </link-entity> <order attribute="name" /> </entity> </fetch>
the results will always be ordered by the account name first, then by the contact name. To order by the contact name first you’d have to rearrange your query.
With the addition of the newly-documented entityname
attribute on the <order>
element we can now override this:
<fetch> <entity name="account"> <attribute name="name" /> <link-entity name="contact" from="parentcustomerid" to="accountid"> <attribute name="fullname" /> </link-entity> <order entityname="contact" attribute="fullname" /> <order attribute="name" /> </entity> </fetch>
Cross-Table Column Comparisons
When the ability to filter by comparing two columns against each other was released (rather than a column against a fixed value), it was limited to columns within the same table. That restriction is now officially lifted, so we could add a filter to the earlier example query to find accounts & contacts that have the same email address as each other by adding:
<filter> <condition attribute="emailaddress1" operator="eq" valueof="contact.emailaddress1" /> </filter>
Subquery Filters
The in
and exists
join types mentioned earlier are helpful, but you can’t combine them with other filters very flexibly. The new option to include subqueries within a filter means that this is now possible for the following scenarios:
FetchXML link-type | SQL equivalent | Description |
---|---|---|
any | exists | Finds records that have at least one matching related record |
not any | not exists | Finds records that do not have any matching related records |
not all | exists | Same as the any link-type |
all | exists and not exists | Same as the not any link-type but with an extra check that there are some related records that do not match the filters |
The not all
and all
link-types take a bit of getting used to with their back-to-front naming. Erik Donker shared a good walkthrough of these on his blog a couple of months ago as well.
Wrapping Up
I’ve tried to highlight some of the FetchXML features which are now documented officially for the first time, but it’s well worth browsing through the rest of the documentation as well. It’s presented in a nice step-by-step format and there’s also a complete reference guide as well.
I’ve started building some of these features into the next version of SQL 4 CDS as well so it can use them to generate more efficient queries, keep an eye out for that coming soon!
Great stuff, thank you!!
Thank you
Mark, do you have the all and not all mixed up in the table above?
I don’t believe so – the strange naming of these is actually called out in the official docs at https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/filter-rows?tabs=sql&WT.mc_id=DX-MVP-5004203#link-type-not-all and the subsequent examples show that
not all
is equivalent to a SQLexists
predicate and thatall
is equivalent to a combination of SQLexists
andnot exists
predicates.