Export (0) Print
Expand All

Using Intersect Tables

banner art

[Applies to: Microsoft Dynamics CRM 4.0]

When there is a many-to-many relationship between two entities, an intersect table is created. This is true for both system relationships built in to the product and custom many-to-many relationships. The name of the table is specified in the IntersectEntityName property in the relationship metadata.

You can use the intersect tables to refine the result set in the QueryExpression for a RetrieveMultiple query. However, you cannot retrieve the intersect table records directly with the RetrieveMultiple method. To retrieve the records in an intersect table, you must use the Fetch method.

The following table lists the intersect tables that are used in M:M relationships between system entities.

Intersect tableEntityEntity
accountleadsaccountlead
businessunitmapbusinessunitbusinessunit
campaignactivityitemlistcampaignactivity
campaignactivityitemsalesliteraturecampaignactivity
campaignitemcampaigncampaign
campaignitemcampaignproduct
campaignitemlistcampaign
campaignitemsalesliteraturecampaign
competitorproductcompetitorproduct
competitorsalesliteraturecompetitorsalesliterature
contactinvoicescontactinvoice
contactleadscontactlead
contactorderscontactsalesorder
contactquotescontactquote
leadcompetitorsleadcompetitor
leadproductleadproduct
listmembercontactlist
listmemberlistaccount
listmemberlistlead
opportunitycompetitorsopportunitycompetitors
productassociationproductproduct
productsalesliteratureproductsalesliterature
productsubstituteproduct product
roleprivilegesroleprivilege
roletemplateprivilegesroletemplateprivilege
servicecontractcontactsservicecontractcontact
systemuserlicensessystemuserlicense
systemuserrolessystemuserrole
teammembershipteamsystemuser

Example

The following example shows how to create a query expression by using a link table. The query retrieves all the roles for a user. This is a many-to-many relationship between the system user entity and the role entity. The name of the intersect table is systemuserroles.

[C#]
// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; 
token.OrganizationName = "AdventureWorksCycle";
 
CrmService service = new CrmService();
service.Url = ""http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Get the GUID of the current user.
WhoAmIRequest who = new WhoAmIRequest();
WhoAmIResponse whoResp = (WhoAmIResponse)service.Execute(who);

Guid userid = whoResp.UserId;

// Create a query expression.
QueryExpression qe = new QueryExpression();
qe.EntityName = "role";
// Be aware that using AllColumns may adversely affect performance 
// and cause unwanted cascading in subsequent updates.
// A best practice is to retrieve the least amount of data required.
qe.ColumnSet = new AllColumns();

// Create the link entity from role to systemuserroles.
LinkEntity le = new LinkEntity();
le.LinkFromEntityName = "role";
le.LinkFromAttributeName = "roleid";
le.LinkToEntityName = "systemuserroles";
le.LinkToAttributeName = "roleid";

LinkEntity le2 = new LinkEntity();
le2.LinkFromEntityName = "systemuserroles";
le2.LinkFromAttributeName = "systemuserid";
le2.LinkToEntityName = "systemuser";
le2.LinkToAttributeName = "systemuserid";

// Create the condition to test the user ID.
ConditionExpression ce = new ConditionExpression();
ce.AttributeName = "systemuserid";
ce.Operator = ConditionOperator.Equal;
ce.Values = new object[]{userid};

// Add the condition to the link entity.
le2.LinkCriteria = new FilterExpression();
le2.LinkCriteria.Conditions = new ConditionExpression[]{ce};

// Add the from and to links to the query.
le.LinkEntities = new LinkEntity[]{le2};
qe.LinkEntities = new LinkEntity[]{le};

// Retrieve the roles and write each one to the console.
BusinessEntityCollection bec = service.RetrieveMultiple(qe);
foreach (BusinessEntity e in bec.BusinessEntities)
{
   role r = (role)e;
   Console.WriteLine(r.name.ToString());
}

See Also

Concepts

Other Resources


© 2010 Microsoft Corporation. All rights reserved.


Show:
© 2014 Microsoft