Wednesday 28 May 2014

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

No comments:

Post a Comment

Blogger Widgets