Published by

Rajeev Pentyala

June 5, 2013

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 toN Relationship — 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’.

🙂

7 responses to “Retrieve associated records (N:N related)”

sunil

August 31, 2013

Thanks a lot….this was really helpful

-Sunil Raheja

Reply
Alex

September 14, 2015

Hi, is there any chance to achieve the same result with javascript?

Reply
1. Francis
  
  June 13, 2016
  
  Just use oData queries, it’s actually easier that way than in C#.
  
  Reply
Faniel Joseph

April 9, 2016

Thanks Rajeev. This was very helpful

Reply
mehaknarmeen

February 22, 2017

Im using CRM 2016 (on-premises) it doesnot work. It returns -1 recordscount as a result.

Reply
mehaknarmeen

February 22, 2017

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

Reply
mohammedfakhri

January 8, 2019

Thanks Really Helpful.

Reply