Join tables using QueryExpression

Artikkel
05/25/2024

Use the QueryExpression.LinkEntities property to describe the data from related tables to return with your query. This property contains a collection of LinkEntity instances that describe:

Which related table rows to return
Which column values to base the join on
Which columns of those records to return
Any filters to apply with the join

Note

The LinkEntities property is read-only. You can set LinkEntity instances to this collection using object initialization or using the QueryExpression.AddLink method.

You can also use System.Collections.ObjectModel.Collection<T> methods the LinkEntities property inherits.

LinkEntity properties

The following table provides details about the LinkEntity properties:

Property	Description
LinkFromEntityName	The logical name of the entity that you're linking from. For a `LinkEntity` that isn't nested, use the same value as the QueryExpression.EntityName property. For a `LinkEntity` that is nested in a LinkEntity.LinkEntities collection, use the value of the LinkEntity.LinkToEntityName from the parent link entity.
LinkToEntityName	The logical name of the entity that you're linking to.
LinkFromAttributeName	The logical name of the attribute of the entity that you're linking from.
LinkToAttributeName	The logical name of the attribute of the entity that you're linking to.
JoinOperator	The join operator. Use a value of one of the JoinOperator enum members. The default value is `Inner`, which restricts results to rows with matching values in both tables. Other valid values are: - `LeftOuter` Includes results from the parent row that don't have a matching value. - `Natural` Only one value of the two joined columns is returned if an equal-join operation is performed and the two values are identical. These members considered advanced JoinOperators: - `Exists` - `In` - `MatchFirstRowUsingCrossApply` These members are used to filter on values in related records: - `All` - `Any` - `NotAll` - `NotAny`
EntityAlias	The alias for the table.
Columns	The columns to include for the table. Add these columns to the joined table using a ColumnSet. Learn to select columns using QueryExpression
LinkCriteria	The complex condition and logical filter expressions that filter the results of the query. Learn how to filter rows using QueryExpression
LinkEntities	The collection of links between entities that can include nested links. Up to 15 total links can be included in a query

Note

The meaning of the LinkEntity.LinkFromAttributeName and LinkEntity.LinkToAttributeName properties are the opposite of the corresponding from and to attributes in FetchXml. Learn more about using from and to attributes with FetchXml

LinkEntity example

The following query returns up to five records from the account and contact tables based on the PrimaryContactId lookup column in the account record. This represents a many-to-one relationship:

QueryExpression query = new("account")
{
      TopCount = 5,
      ColumnSet = new ColumnSet("name"),
      LinkEntities = {
            new LinkEntity()
            {
                  LinkFromEntityName = "account",
                  LinkToEntityName = "contact",
                  LinkFromAttributeName = "primarycontactid",
                  LinkToAttributeName = "contactid",
                  JoinOperator = JoinOperator.Inner,
                  EntityAlias = "contact",
                  Columns = new ColumnSet("fullname")
            }
      }
};

The results look like this:

 -----------------------------------------------------------------
 | name                             | contact.fullname           |
 -----------------------------------------------------------------
 | Litware, Inc. (sample)           | Susanna Stubberod (sample) |
 -----------------------------------------------------------------
 | Adventure Works (sample)         | Nancy Anderson (sample)    |
 -----------------------------------------------------------------
 | Fabrikam, Inc. (sample)          | Maria Campbell (sample)    |
 -----------------------------------------------------------------
 | Blue Yonder Airlines (sample)    | Sidney Higa (sample)       |
 -----------------------------------------------------------------
 | City Power & Light (sample)      | Scott Konersmann (sample)  |
 -----------------------------------------------------------------

AddLink methods

You can compose the entire query using object initialization as shown, but we recommend using the QueryExpression.AddLink and LinkEntity.AddLink methods. These methods return a reference to the link created so that you can easily access and modify the query within the collection. For example, if you compose the query this way using the QueryExpression.AddLink method:

var query = new QueryExpression("account")
{
      TopCount = 5,
      ColumnSet = new ColumnSet("name"),
};

// Link to primary contact
LinkEntity linkedPrimaryContact = query.AddLink(
      linkToEntityName: "contact",
      linkFromAttributeName: "primarycontactid",
      linkToAttributeName: "contactid",
      joinOperator: JoinOperator.Inner);

linkedPrimaryContact.EntityAlias = "primarycontact";
linkedPrimaryContact.Columns = new ColumnSet("fullname");

You can extend the query using the LinkEntity.AddLink method to include information about the owning user for the contact linked via the linkedPrimaryContact LinkEntity instance:

// Link to contact owning user
LinkEntity linkedContactOwner = linkedPrimaryContact.AddLink(
      linkToEntityName: "systemuser",
      linkFromAttributeName: "owninguser",
      linkToAttributeName: "systemuserid",
      joinOperator: JoinOperator.Inner);

linkedContactOwner.EntityAlias = "owner";
linkedContactOwner.Columns = new ColumnSet("fullname");

This way you can more easily access the different parts of the query to make adjustments.

Limitations

You can add up to 15 LinkEntity instances to a query. Each LinkEntity adds a JOIN to the query and increases the time to execute the query. This limit is to protect performance. If you add more than 15 LinkEntity instances to the QueryExpression.LinkEntities, you get this runtime error:

Name: TooManyLinkEntitiesInQuery Code: 0x8004430D
Number: -2147204339
Message: Number of link entities in query exceeded maximum limit.

Many-to-one relationships

The previous example is a many-to-one relationship where many account records can refer to a one contact record. This information is defined in the Account account_primary_contact many-to-one relationship, which has the following values:

Property	Value	Comment
`SchemaName`	`account_primary_contact`	Unique Name of the relationship.
`ReferencedEntity`	`contact`	The referenced table. The one in many-to-one. `LinkToEntityName` in the previous example.
`ReferencedAttribute`	`contactid`	The primary key of the referenced table. `LinkToAttributeName` in the previous example.
`ReferencingEntity`	`account`	The table with a lookup column referencing the other table. The many in many-to-one. `LinkFromEntityName` in the previous example.
`ReferencingAttribute`	`primarycontactid`	The name of the lookup column. `LinkFromAttributeName` in the previous example.
`RelationshipType`	`OneToManyRelationship`	A one-to-many relationship when viewed from the referenced (one) `contact` table. A many-to-one relationship when viewed from the referencing (many) `account` table

Retrieve relationship information

You can use other tools and APIs to look up relationship data for the appropriate LinkToEntityName, LinkToAttributeName, LinkFromEntityName, and LinkFromAttributeName values to use. For more information, see:

Many-to-one relationship example

The following table shows the relationship values to use for a many-to-one relationship:

Property	Relationship Value	Comment
`LinkFromEntityName`	`ReferencingEntity`	The referenced table. The many in many-to-one. `account` in the many-to-one example. There's no parameter for this property in the AddLink methods because it can be derived from the `QueryExpression.EntityName` or `LinkEntity.LinkToEntityName` properties.
`LinkToEntityName`	`ReferencedEntity`	The table with a primary key the other table references. The one in many-to-one. `contact` in the many-to-one example.
`LinkFromAttributeName`	`ReferencingAttribute`	The name of the lookup column. `primarycontactid` in the many-to-one example.
`LinkToAttributeName`	`ReferencedAttribute`	The primary key of the referenced table. `contactid` in the many-to-one example.

One-to-many relationships

Many-to-one and one-to-many relationships are like looking at two sides of a coin. The relationship exists between the tables, so the way you use it depends on which table is the base table for your query.

Note

If your base table contains the lookup column, it is a many-to-one relationship. Otherwise, it is a one-to-many relationship.

You can retrieve the same data as the previous example from the contact table using the same relationship, except from the side of the contact table. Use the data from the same Contact account_primary_contact one-to-many relationship, but adjust the values for the different view of the relationship.

var query = new QueryExpression("contact")
{
      TopCount = 5,
      ColumnSet = new ColumnSet("fullname"),
};

// Link to related account
var linkedPrimaryContact = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.Inner);

linkedPrimaryContact.EntityAlias = "account";
linkedPrimaryContact.Columns = new ColumnSet("name");

For a one-to-many relationship, use these relationship values:

Property	Relationship Value	Comment
`LinkFromEntityName`	`ReferencedEntity`	The referenced table. The one in many-to-one. `contact` in the one-to-many example. There's no parameter for this property in the AddLink methods because it can be derived from the `QueryExpression.EntityName` or `LinkEntity.LinkToEntityName` properties.
`LinkToEntityName`	`ReferencingEntity`	The table with a lookup column referencing the other table. The many in many-to-one. `account` in the one-to-many example.
`LinkFromAttributeName`	`ReferencedAttribute`	The primary key of the referenced table. `contactid` in the one-to-many example.
`LinkToAttributeName`	`ReferencingAttribute`	The name of the lookup column. `primarycontactid` in the one-to-many example.

The results include the same records and data as the previous example using the many-to-one relationship, except now the 'parent entity' is now contact instead of account.

 -----------------------------------------------------------------
 | fullname                   | account.name                     |
 -----------------------------------------------------------------
 | Susanna Stubberod (sample) | Litware, Inc. (sample)           |
 -----------------------------------------------------------------
 | Nancy Anderson (sample)    | Adventure Works (sample)         |
 -----------------------------------------------------------------
 | Maria Campbell (sample)    | Fabrikam, Inc. (sample)          |
 -----------------------------------------------------------------
 | Sidney Higa (sample)       | Blue Yonder Airlines (sample)    |
 -----------------------------------------------------------------
 | Scott Konersmann (sample)  | City Power & Light (sample)      |
 -----------------------------------------------------------------

Many-to-many relationships

Many-to-many relationships depend on an intersect table. An intersect table typically has just four columns, but only two of them are important. The two important columns match the primary key columns of the participating tables.

For example, the TeamMembership intersect table supports the teammembership_association many-to-many relationship between SystemUser and Team tables. It allows users to join multiple teams, and teams to have multiple users. TeamMembership has these columns: systemuserid, teamid.

If you want to retrieve information about users and the teams they belong to using the teammembership_association many-to-many relationship, you can use this QueryExpression query:

var query = new QueryExpression("systemuser")
{
      TopCount = 2,
      ColumnSet = new ColumnSet("fullname"),
};

LinkEntity linkedTeamMemberShip = query.AddLink(
      linkToEntityName: "teammembership",
      linkFromAttributeName: "systemuserid",
      linkToAttributeName: "systemuserid");

LinkEntity linkedTeam = linkedTeamMemberShip.AddLink(
      linkToEntityName: "team",
      linkFromAttributeName: "teamid",
      linkToAttributeName: "teamid");

linkedTeam.EntityAlias = "team";
linkedTeam.Columns = new ColumnSet("name");

There are two LinkEntity instances.

linkedTeamMemberShip connects systemuser to the teammembership intersect table where systemuserid = systemuserid.
linkedTeam connects teammembership intersect table to team where teamid = teamid.

The results should look something like:

 --------------------------------------
 | fullname             | team.name   |
 --------------------------------------
 | FirstName LastName   | org26ed931d |
 --------------------------------------
 | # PpdfCDSClient      | org26ed931d |
 --------------------------------------

No relationship

It's possible to specify LinkFromAttributeName and LinkToAttributeName properties using columns that aren't part of a defined relationship.

For example, this query finds pairs of records where the Name column of an account record matches the FullName column of a contact record regardless of whether they reference each other in any of the lookup columns.

var query = new QueryExpression("account")
{
      ColumnSet = new ColumnSet("name"),
};

LinkEntity linkedContact = query.AddLink(
      linkToEntityName: "contact", 
      linkFromAttributeName: "name", 
      linkToAttributeName: "fullname");
linkedContact.EntityAlias = "contact";
linkedContact.Columns = new ColumnSet("fullname");

Note

It is important that the columns specified in the LinkFromAttributeName and LinkToAttributeName properties are the same type even if they are not involved in a relationship. Using columns of different types will require a type conversion that might have performance impact and might fail for some column values.

The following column types can't be used in LinkFromAttributeName and LinkToAttributeName properties:

File
Image
MultiSelect Field
PartyList

Some columns can be used in LinkFromAttributeName and LinkToAttributeName properties but might result in poor performance:

Columns of the Multiple Lines of Text type
Columns of the Single Line of Text type with a maximum length larger than 850
Formula columns
Calculated columns
Logical columns

Find records not in a set

You can use QueryExpression to create a query to return records that aren't in a set using a left outer join. A left outer join returns each row that satisfies the join of the first input with the second input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values.

You can perform a left outer join in QueryExpression by using the ConditionExpression.EntityName property. The EntityName property is valid in conditions, filters, and nested filters. Learn more about filters on LinkEntity

For example, the following query returns all account records with no contacts.

var query = new QueryExpression(entityName: "account");
query.ColumnSet.AddColumn("name");
query.AddOrder(
      attributeName: "name", 
      orderType: OrderType.Descending);
query.Criteria.AddCondition(
      entityName: "contact",
      attributeName: "parentcustomerid",
      conditionOperator: ConditionOperator.Null);

LinkEntity linkedContact = query.AddLink(
      linkToEntityName: "contact",
      linkFromAttributeName: "accountid",
      linkToAttributeName: "parentcustomerid",
      joinOperator: JoinOperator.LeftOuter);
linkedContact.EntityAlias = "contact";
linkedContact.Columns.AddColumn("fullname");

Use advanced JoinOperators

The following JoinOperator members don't directly correspond to T-SQL JOIN operator types and use subqueries instead. These types provides more advanced capabilities you can use to improve query performance and define more complex queries.

Name	Description
`Exists`	A variant of `Inner` that can provide performance benefits. Uses an EXISTS condition in the `where` clause. Use `Exists` when multiple copies of the parent row aren't 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 `In` when multiple copies of the parent row aren't 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 using `MatchFirstRowUsingCrossApply`

Use `JoinOperator.Exists` or `JoinOperator.In`

Exists and In are variants of Inner that use different conditions (EXISTS and IN respectively) in the where clause so that multiple copies of the parent row aren't returned in the results. Both Exists and In don't return the column values of the related entity rows.

Using JoinOperator.Exists or JoinOperator.In can reduce the size of intermediate or final query results, especially when many matching linked rows exist for the same parent rows, or when multiple link entities are used with the same parent. Using JoinOperator.Exists or JoinOperator.In can improve performance of the query compared to JoinOperator.Inner because it doesn't require returning a Cartesian product containing all possible permutations of rows from different linked entities for each parent row.

These JoinOperator members might also allow Dataverse to only find the first matching linked entity row for each parent row that is more efficient than finding all matching rows in the linked entity with JoinOperator.Inner.

`JoinOperator.Exists` example

These QueryExpression and SQL examples show the patterns applied with JoinOperator.Exists.

QueryExpression
SQL

QueryExpression query = new("contact");
query.ColumnSet.AddColumn("fullname");

LinkEntity linkedAccount = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.Exists);

linkedAccount.EntityAlias = "account";

linkedAccount.LinkCriteria.AddCondition(
      entityName:"account", 
      attributeName: "statecode", 
      conditionOperator: ConditionOperator.Equal,
      values: 1);

select 
    "contact0".fullname as "fullname" 
from Contact as "contact0" 
where exists (
    select "account1".primarycontactid
    from Account as "account1"
    where "account1".statecode = 1
        and "contact0".contactid = "account1".primarycontactid)

`JoinOperator.In` example

These QueryExpression and SQL examples show the patterns applied with JoinOperator.In.

QueryExpression
SQL

QueryExpression query = new("contact");
query.ColumnSet.AddColumn("fullname");

LinkEntity linkedAccount = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.In);

linkedAccount.EntityAlias = "account";

linkedAccount.LinkCriteria.AddCondition(
      entityName: "account",
      attributeName: "statecode",
      conditionOperator: ConditionOperator.Equal,
      values: 1);

select 
    "contact0".fullname as "fullname" 
from Contact as "contact0" 
where "contact0".contactid in (
    select "account1".primarycontactid
    from Account as "account1"
    where "account1".statecode = 1)

Use `JoinOperator.MatchFirstRowUsingCrossApply`

JoinOperator.MatchFirstRowUsingCrossApply produces a CROSS APPLY operator with a subquery using top 1 following this pattern:

QueryExpression
SQL

QueryExpression query = new("contact");
query.ColumnSet.AddColumn("fullname");

LinkEntity linkedAccount = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.MatchFirstRowUsingCrossApply);

linkedAccount.EntityAlias = "account";
linkedAccount.Columns = new ColumnSet("accountid", "name");

select 
    "contact0".fullname as "fullname",
    "account1".accountid as "accountid",
    "account1".name as "name" 
from Contact as "contact0"
cross apply (
    select top 1
        "account1".accountid as "accountid",
        "account1".name as "name"
    from Account as "account1"
    where "contact0".contactid = "account1".primarycontactid
  ) "account1"

This is equivalent to JoinOperator.LeftOuter except it only returns the parent row at most once. Unlike JoinOperator.In and JoinOperator.Exists, it returns column values from one of the matching rows in the related table when matching rows exist, but the parent row is returned even if there are no matching rows in the related table. Use this when only a single example of a matching row from the related table is sufficient and multiple copies of the parent row in the results aren't necessary.

Next steps

Learn how to order rows.

Order rows

Del via

Join tables using QueryExpression

LinkEntity properties

LinkEntity example

AddLink methods

Limitations

Many-to-one relationships

Retrieve relationship information

Many-to-one relationship example

One-to-many relationships

Many-to-many relationships

No relationship

Find records not in a set

Use advanced JoinOperators

Use `JoinOperator.Exists` or `JoinOperator.In`

`JoinOperator.Exists` example

`JoinOperator.In` example

Use `JoinOperator.MatchFirstRowUsingCrossApply`

Next steps

Tilbakemeldinger

Flere ressurser

Del via

Join tables using QueryExpression

LinkEntity properties

LinkEntity example

AddLink methods

Limitations

Many-to-one relationships

Retrieve relationship information

Many-to-one relationship example

One-to-many relationships

Many-to-many relationships

No relationship

Find records not in a set

Use advanced JoinOperators

Use JoinOperator.Exists or JoinOperator.In

JoinOperator.Exists example

JoinOperator.In example

Use JoinOperator.MatchFirstRowUsingCrossApply

Next steps

Tilbakemeldinger

Flere ressurser

Use `JoinOperator.Exists` or `JoinOperator.In`

`JoinOperator.Exists` example

`JoinOperator.In` example

Use `JoinOperator.MatchFirstRowUsingCrossApply`