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 ;
}
No comments:
Post a Comment