Deep dive: Linked entities in FetchXML

The first section explains traditional FetchXML link types, i.e., inner and outer joins. It introduces the concept of reverse lookups (as those representing 1:N relations), emphasizing their crucial role in fetch performance. Additionally, it explores how the system's security model affects fetch performance and addresses specifics of the synchronization sync filters.

The second section explores the applicability of In/Exists links:

• These links can replace inner links when the linked entity’s attributes are neither referenced outside the <link-entity> nor included in the fetch results.
• They are effective when the performance of an SQL sub-query surpasses that of a join.
• This is particularly relevant for highly selective fetches (those examining large datasets to yield a small number of results) and their inner links based on reverse lookups.

The key takeaway is that while In/Exists links may not generally improve fetch performance (and can sometimes degrade it), there are specific contexts — particularly in complex Field Service queries — where they can offer significant performance improvements and help avoid potential server timeouts.

A typical example is msdyn_workorder<-bookableresourcebooking link in the MyBookings-centric Field Service client data model.

Linked entities in FetchXML

Links allow you to join related entities (tables) in a single query. For example, you might want to get information for an account which includes contacts related to that account. (We'll discuss this example later.)

Formally, links combine two entities - BaseEntity with LinkedEntity, whereby the latter is described by XML syntax <link-entity name="LinkedEntity" from="fieldFrom" to="fieldTo" alias="..." link-type="...">

In the example

• LinkedEntity.fieldFrom should match BaseEntity.fieldTo.
  This typically means a lookup BaseEntity.fieldTo pointing to LinkedEntity primary key (fieldFrom), but there are more possibilities. (See below.)
• Alias defines an (optional) new name for the linked entity.
• Link-type specifies various ways of joins - traditionally inner (default) and outer.

Besides this, the linked entity may contain attributes and conditions just like the base entity.

Link examples

This fetch outputs a list of contacts (represented by their full name):

<fetch>
	<entity name="contact">
		<attribute name="fullname"/>
	</entity>
</fetch>

If we want to add information about the parent account, we can make use of the lookup contact.parentcustomerid -> account.

For example, the following fetch returns two columns from two different entities: (contact.fullname, account.name).

<fetch>
	<entity name="contact">
		<attribute name="fullname"/>
		<link-entity name='account' from='accountid' to='parentcustomerid' link-type='inner'>
			<attribute name="name"/>
		</link-entity>
	</entity>
</fetch>

The link is inner, which means that standalone contacts (those without a parent account) are not returned. If we also want those contacts, we can use the outer link:

<fetch>
	<entity name="contact">
		<attribute name="fullname"/>
		<link-entity name='account' from='accountid' to='parentcustomerid' link-type='outer'>
			<attribute name="name"/>
		</link-entity>
	</entity>
</fetch>

This time, we get:

• previous results (contact.fullname, account.name)
• new results (contact.fullname, null) representing standalone (disconnected, unassociated) contacts.

The above cases are lookup-based, i.e., they use existing relation contact->account. (Relational theory uses the term N:1 relation contact:account as 1 account has N contacts.)

However, we may pose a reverse problem: Get accounts + their contacts. We have to use the same relation as above, but in reverse order: account:1 <- contact:N. We refer to this situation as a reverse lookup. We'll subsequently discuss the two possible fetches - inner and outer.

<fetch>
	<entity name="account">
		<attribute name="name"/>
		<link-entity name='contact' from='parentcustomerid' to='accountid' link-type='inner'>
			<attribute name="fullname"/>
		</link-entity>
	</entity>
</fetch>

The results look like this:

	(account1.name, contact11.fullname)
	(account1.name, contact12.fullname)
	(account1.name, contact13.fullname)
	...
	(account2.name, contact21.fullname)
	(account2.name, contact22.fullname)
	...

We see that one account is returned N times - once per each account's contact. Note that accounts not having any contact at all are not returned. If we want those as well, we have to use the outer link:

<fetch>
	<entity name="account">
		<attribute name="name"/>
		<link-entity name='contact' from='parentcustomerid' to='accountid' link-type='outer'>
			<attribute name="name"/>
		</link-entity>
	</entity>
</fetch>

Then we also get "orphaned" accounts (accounts with no contacts):

	(account3.name, null)

Let's show another example demonstrating a bit unorthodox use of outer link ("accounts without contacts"):

<fetch>
   <entity name='account'>
	  <attribute name='name' />
	  <link-entity name='contact' from='parentcustomerid' to='accountid' link-type='outer' alias='C'/>
	  <filter type='and'>
		 <condition entityname='C' attribute='parentcustomerid' operator='null'/>
	  </filter>
   </entity>
</fetch>