Retrieve associated records (N:N related)
In CRM, If you create N:N relationship between 2 entities, it creates an intermediate entity (i.e., Relationship Entity) with 3 fields
- Primary key field of “Relationship Entity”
- Entity 1 Primary Key field
- Entity 2 Primary Key field
N to N Relationship
In this sample, I have 2 custom entities “Bike” and “Bond” with N:N association. (i.e., A ‘Bike’ can have N no of ‘Bonds’ and a ‘Bond’ can be associate with N ‘Bikes’)
To get all the ‘Bonds’ of ‘Bike’ with name “Honda”, below is the query expression.
string entity1 = “raj_bike”;
string entity2 = “raj_bond”;
string relationshipEntityName = “raj_bike_bond”;
QueryExpression query = new QueryExpression(entity1);
query.ColumnSet = new ColumnSet(true);
LinkEntity linkEntity1 = new LinkEntity(entity1, relationshipEntityName, “raj_bikeid”, “{Entity 1 Primary key field (i.e.,raj_bikeid)}“, JoinOperator.Inner);
LinkEntity linkEntity2 = new LinkEntity(relationshipEntityName, entity2, “raj_bondid”, “{Entity 2 Primary key field (i.e.,raj_bondid)}“, JoinOperator.Inner);
linkEntity1.LinkEntities.Add(linkEntity2);
query.LinkEntities.Add(linkEntity1);
// Add condition to match the Bike name with “Honda”
linkEntity2.LinkCriteria = new FilterExpression();
linkEntity2.LinkCriteria.AddCondition(new ConditionExpression(“raj_bikename”, ConditionOperator.Equal, “Honda”));
EntityCollection collRecords = service.RetrieveMultiple(query);
Note : To retrieve columns from Entity 2, set ‘Alias’ to ‘LinkEntity2’.
🙂
Rajeev Pentyala - Microsoft Power Platform 
Thanks a lot….this was really helpful
-Sunil Raheja
Hi, is there any chance to achieve the same result with javascript?
Just use oData queries, it’s actually easier that way than in C#.
Thanks Rajeev. This was very helpful
Im using CRM 2016 (on-premises) it doesnot work. It returns -1 recordscount as a result.
string entity1 = “mn_apartmentconfiguration”;
string entity2 = “mn_room”;
string relationshipEntityName = “mn_mn_apartmentconfiguration_mn_room”;
QueryExpression query = new QueryExpression(entity1);
query.ColumnSet = new ColumnSet(true);
LinkEntity linkEntity1 = new LinkEntity(entity1, relationshipEntityName,
“mn_apartmentconfigurationid”, “mn_apartmentconfigurationid”, JoinOperator.Inner);
LinkEntity linkEntity2 = new LinkEntity(relationshipEntityName, entity2, “mn_roomid”, “mn_roomid”, JoinOperator.Inner);
linkEntity2.EntityAlias = “alias”;
linkEntity1.LinkEntities.Add(linkEntity2);
query.LinkEntities.Add(linkEntity1);
linkEntity2.LinkCriteria = new FilterExpression();
linkEntity2.LinkCriteria.AddCondition(new ConditionExpression(“mn_room”, ConditionOperator.Equal, “Dining Room”));
EntityCollection collRecords = service.RetrieveMultiple(query);
foreach (var r in collRecords.Entities)
{
counter=collRecords.TotalRecordCount;
records = counter.ToString() ; //returns -1
throw new InvalidPluginExecutionException(records);
}
Thanks Really Helpful.