Advertisements
Home > CRM 2011 > Retrieve associated records (N:N related)

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 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’.

🙂

Advertisements
  1. sunil
    August 31, 2013 at 1:10 AM

    Thanks a lot….this was really helpful

    -Sunil Raheja

  2. Alex
    September 14, 2015 at 4:11 PM

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

    • Francis
      June 13, 2016 at 11:26 PM

      Just use oData queries, it’s actually easier that way than in C#.

  3. Faniel Joseph
    April 9, 2016 at 1:06 AM

    Thanks Rajeev. This was very helpful

  4. February 22, 2017 at 4:14 PM

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

  5. February 22, 2017 at 4:17 PM

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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: