Fetch records from Entity having N: N relationship

In Microsoft Dynamics CRM, when there is a many-to-many (N:N) relationship between two entities, an intersect entity is automatically created. This is true for both system relationships built into the product as well as custom many-to-many relationships. The name of the entity is specified in the IntersectEntityName property in the relationship metadata. The name of the relationship is specified in the SchemaNameproperty in the relationship metadata.

Here are few out of the box intersect entities listed in below table for better understanding-

Entity1	Entity2	Intersect Entity Name	Schema Name
account	lead	accountleads	accountleads_association
contact	lead	contactleads	contactleads_association
product	entitlement	entitlementproducts	product_entitlement_association
systemuser	role	systemuserroles	systemuserroles_association
list	account	listmember	listaccount_association

These are just few examples, you can refer the complete list of out of the box intersect entities with below link-

https://msdn.microsoft.com/en-us/library/gg309538.aspx

Because it creates an intersect entity for the relationship automatically to fetch the records from the Entity which having N: N relationship is quite different, we would discuss the method in this article.

Let’s take an example of the last records from the above table for out of the box N:N relationship between list and account entity.

The requirement is to fetch all the ‘Marketing List’ which the account with name ‘Bhausaheb Patil’ is associated, below is the code for the same-

        private EntityCollection GetRealtedRecords()

        {

            string entity1 = "account";

            string entity2 = "list";

            string relationshipEntityName = "listmember"; // Provide intersect entity name here

            QueryExpression query = new QueryExpression(entity1);

            query.ColumnSet = new ColumnSet(true);

            LinkEntity linkEntity1 =

  new LinkEntity(entity1, relationshipEntityName, "accountid", "{Entity 1 Primary field}", JoinOperator.Inner);

            LinkEntity linkEntity2 =

  new LinkEntity(relationshipEntityName, entity2, "list", "{Entity 2 Primary field}", JoinOperator.Inner);

            linkEntity1.LinkEntities.Add(linkEntity2);

            query.LinkEntities.Add(linkEntity1);

            linkEntity2.LinkCriteria = new FilterExpression();

            linkEntity2.LinkCriteria.AddCondition(new ConditionExpression("name", ConditionOperator.Equal, "Bhausaheb Patil"));

            EntityCollection result = service.RetrieveMultiple(query);

            return result;

        }        

However, you cannot retrieve the intersect entity records directly by using the QueryExpression class. To retrieve the records in an intersect entity; you must use the FetchExpression class.