Düzenle

Aracılığıyla paylaş

Join tables using QueryExpression

  • Makale

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)  |
 -----------------------------------------------------------------

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:

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 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);

JoinOperator.In example

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

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);

Use JoinOperator.MatchFirstRowUsingCrossApply

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

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");

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