New FetchXML Features and Documentation

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!