link-entity element

Joins a table related to the entity or link-entity to return additional columns with the result. Also used with filter elements to apply conditions on column values in related tables.

Learn how to join tables using FetchXml.

Examples

The following examples show using link-entity with different types of relationships.

Many-to-one relationship

This query returns data from the account and contact tables based on the PrimaryContactId lookup column in the account record:

<fetch>
  <entity name='account'>
    <attribute name='name' />
    <link-entity name='contact'
      from='contactid'
      to='primarycontactid'
      link-type='inner'
      alias='contact'>
      <attribute name='fullname' />
    </link-entity>
  </entity>
</fetch>

One-to-many relationship

This query returns data from the contact and account tables based on the Contact account_primary_contact one-to-many relationship.

<fetch>
  <entity name='contact'>
    <attribute name='fullname' />
    <link-entity name='account' 
     from='primarycontactid' 
     to='contactid' 
     alias='account'>
      <attribute name='name' />
    </link-entity>
  </entity>
</fetch>

Many-to-many relationship

This query returns data from the SystemUser and Team tables using the teammembership_association many-to-many relationship.

<fetch>
  <entity name='systemuser'>
    <attribute name='fullname' />
    <link-entity name='teammembership'
      from='systemuserid'
      to='systemuserid' >
      <link-entity name='team'
        from='teamid'
        to='teamid'
        link-type='inner'
        alias='team'>
        <attribute name='name' />
      </link-entity>
    </link-entity>
  </entity>
</fetch>

Attributes

Name	Required?	Description
name	Yes	The logical name of the related table.
to	No	The logical name of the column in the parent element to match with the related table column specified in the from attribute. While not technically required, this attribute is usually used.
from	No	The logical name of the column from the related table that matches the column specified in the to attribute. While not technically required, this attribute is usually used.
alias	No	Represents the name of the related table. If you don't set an alias, one will be generated for you to ensure all columns have unique names, but you will not be able to use that alias to reference the link entity in other parts of the fetch XML. The auto-generated aliases use the pattern {LogicalName}+{N}, where N is the sequential number of the link-entity in the fetch XML starting from 1.
link-type	No	The type of link use. Default behavior is inner. Learn about link-type options
intersect	No	Indicates that the link-entity is used to join tables and not return any columns, typically for a many-to-many relationship. The existence of this attribute doesn't change the query execution. You might add this attribute to your link-entity when you join a table but don't include any attribute elements to show that this is intentional.

Using from and to attributes

It is best to set values for both the from and to attributes. Both of these attributes are usually used to explicitly define the columns to match. However, the from and to attributes are not technically required.

Note

• It is important that the columns specified in the from and to attributes are the same type. Using different column types is not supported. When the columns are not the same type, the Dataverse infrastructure may be able to force a conversion but this practice can result in a significant performance penalty.

• The meaning of the from and to attributes in FetchXml are the opposite of the corresponding LinkEntity.LinkFromAttributeName and LinkEntity.LinkToAttributeName properties used when composing queries using QueryExpression.

If you don't use either of these attributes, and a system many-to-many relationship exists between the two tables, Dataverse will select the appropriate key values using that relationship.

If you specify only one of the from or to attributes, Dataverse will attempt to figure out the correct relationship using the relationship schema definitions between the two tables.

Otherwise you will get an error like the following:

Code: 0x80041102
Message: No system many-to-many relationship exists between <table A> and <table B>. If attempting to link through a custom many-to-many relationship ensure that you provide the from and to attributes.

For example, both of these queries use the teammembership_association many-to-many relationship between systemuser and team tables. In this case, Dataverse can work out the from and to attributes and the link-entity that specifies the intersect table isn't necessary.

Specify all attributes

<fetch top="2">
<entity name="systemuser">
<attribute name="fullname" />
<link-entity
name="teammembership"
from="systemuserid"
to="systemuserid"
intersect="true"
>
<link-entity
name="team"
from="teamid"
to="teamid"
link-type="inner"
alias="team"
>
<attribute name="name" />
</link-entity>
</link-entity>
</entity>
</fetch>

Let Dataverse choose

<fetch top="2">
<entity name="systemuser">
<attribute name="fullname" />
<link-entity name="team" alias="team">
<attribute name="name" />
</link-entity>
</entity>
</fetch>

link-type options

Use link-type to apply filters on the records returned. The following table describes the valid link-type values

Name	Description
inner	Default. Restricts results to rows with matching values in both tables.
outer	Includes results from the parent element that don't have a matching value.
any	Use this within a filter element. Restricts results to parent rows with any matching rows in the linked entity. Learn to use any to filter values on related tables
not any	Use this within a filter element. Restricts results to parent rows with no matching rows in the linked entity. Learn to use not any to filter values on related tables
all	Use this within a filter element. Restricts results to parent rows where rows with matching from column value exist in the link entity but none of those matching rows satisfy the additional filters defined for this link entity. You need to invert the additional filters to find parent rows where every matching link entity row satisfies some additional criteria. Learn to use all to filter values on related tables
not all	Use this within a filter element. Restricts results to parent rows with any matching rows in the linked entity. This link type is equivalent to any despite the name. Learn to use not all to filter values on related tables
exists	A variant of inner that can provide performance benefits. Uses an EXISTS condition in the where clause. Use this when multiple copies of the parent row are not necessary in the results. Learn more about exists and in
in	A variant of inner that can provide performance benefits. Uses an IN condition in the where clause. Use this when multiple copies of the parent row are not necessary in the results. Learn more about exists and in
matchfirstrowusingcrossapply	A variant of inner that can provide performance benefits. Use this type when only a single example of a matching row from the linked entity is sufficient and multiple copies of the parent row in the results aren't necessary. Learn more about matchfirstrowusingcrossapply

Parent elements

Name	Description
entity	Specifies the child element for a fetch element, the 'parent entity' for the query. Only one entity is allowed.
link-entity	Joins a table related to the entity or link-entity to return additional columns with the result.

Child elements

Name	Occurrences	Description
all-attributes	0 or 1	Indicates that all column values for each row are returned. It is the same as not adding any attribute elements. We don't recommend using this element for most cases.
attribute	0 or many	Specifies a column from an entity or link-entity to return with a query.
order	0 or many	Specifies a sort order for the rows of the results.
link-entity	0 or many	Joins a table related to the entity or link-entity to return additional columns with the result.
filter	0 or 1	Specify complex conditions for an entity or link-entity to apply to a query.