Retrieve N:N relationship records by Query Expression in MSCRM

Sometimes we have requirement to get records from N: N relationship. So today I would like share how we can get these relationship records by query expression in code. When we create N: N relationship between 2 entities, it creates an intermediate entity (i.e., Relationship Entity) . Below screen shot showing N:N relation between account and marketing list.

for details attribute you can see the database table with same relationship entity name as here is “contactleads”

Contact Lead N:N Relationship  contactleads(Intersect Entity) fields in DB

I am taking example of existing  2 entities “Contact” and “Lead” with N:N association as above screenshot showing . Because an Contact can be in multiple Leads or Vice-Versa

for e.g we have requirement to get all the ‘Leads’ which the Contact with name “Arpit Shrivastava” is associated . for this query code viz:

string entity1 = “Contact”;

string entity2 = “Lead”;

string relationshipEntityName = “contactleads”;

Private  EntityCollection getM_to_M_Record(string entity1 ,string entity2, string relationshipentityName)

{

QueryExpression query = new QueryExpression(entity1);

query.ColumnSet = new ColumnSet(new string[]{\”firstname\”});

LinkEntity linkEntity1 = new LinkEntity(entity1, relationshipEntityName, “contactid”, “{Entity 1 Primary field}“, JoinOperator.Inner);

LinkEntity linkEntity2 = new LinkEntity(relationshipEntityName, entity2, “lead”, “{Entity 2 Primary field}“, JoinOperator.Inner);

linkEntity1.LinkEntities.Add(linkEntity2);

query.LinkEntities.Add(linkEntity1);

// Add condition to match the Contact Name with “Arpit Shrivastava”

linkEntity2.LinkCriteria = new FilterExpression();

linkEntity2.LinkCriteria.AddCondition(new ConditionExpression(“firstname”,ConditionOperator.Equal, “Arpit Shrivastava”));

EntityCollection recordcollection= service.RetrieveMultiple(query);

return recordcollection ;

}

Advertisement