Querying CRM Relationships

Recently I had to create a query that for a given primary entity would return a set of related target entities and support both 1-many and many-many relationships.

I knew how to do this in separate queries, so I could have some branching logic to see which type of query I needed to perform but that wasn’t a particularly elegant solution. I found the RelationshipQueryCollection, this allows you create a RetrieveRequest against your primary entity and then link in any related targets. All you have to do is specify the relationship name and it will support both 1-many and many-many relationships. If your are using self-referencing relationship be sure the populate relationship.PrimaryEntityRole.

IEnumerable<Guid> GetRelatedEntities(IOrganizationService service, string primaryEntity, Guid primaryEntityId, string relationshipName, string targetEntity)
{
//the related entity we are going to retrieve
QueryExpression query = new QueryExpression();
query.EntityName = targetEntity;
query.ColumnSet = new ColumnSet();

//the relationship that links the primary to the target
Relationship relationship = new Relationship(relationshipName);
relationship.PrimaryEntityRole = EntityRole.Referenced; //important if the relationship is self-referencing

//the query collection which forms the request
RelationshipQueryCollection relatedEntity = new RelationshipQueryCollection();
relatedEntity.Add(relationship, query);

//the request to get the primary entity with the related records
RetrieveRequest request = new RetrieveRequest();
request.RelatedEntitiesQuery = relatedEntity;
request.ColumnSet = new ColumnSet();
request.Target = new EntityReference(primaryEntity, primaryEntityId);

RetrieveResponse r = (RetrieveResponse)service.Execute(request);

//query the returned collection for the target entity ids
return r.Entity.RelatedEntities[relationship].Entities.Select(e => e.Id);
}

Edit based on comment from daryllabar:

To better explain this function here is a practical example.

• There is a one to many relationship between contact and account, it is named “account_primary_contact”.
• The foreign key on the account is named “primarycontactid”
• There is one contact, this is the primary contact of three accounts (A, B & C).
• There is another account which does not have an primary contact (D).

In this example we will retrieve all three related account ids using the above function.

//Create the contact
Entity contact = new Entity("contact");
contact["firstname"] = "Relationship";
contact["lastname"] = "Test";
Guid contactId = proxy.Create(contact);

//Create the accounts, linking them to contact
Entity accountA = new Entity("account");
accountA["name"] = "Relationship Test A";
accountA["primarycontactid"] = new EntityReference("contact", contactId);
Guid accountAId = proxy.Create(accountA);

Entity accountB = new Entity("account");
accountB["name"] = "Relationship Test B";
accountB["primarycontactid"] = new EntityReference("contact", contactId);
Guid accountBId = proxy.Create(accountB);

Entity accountC = new Entity("account");
accountC["name"] = "Relationship Test C";
accountC["primarycontactid"] = new EntityReference("contact", contactId);
Guid accountCId = proxy.Create(accountC);

Entity accountD = new Entity("account");
accountD["name"] = "Relationship Test D";
Guid accountDId = proxy.Create(accountD);

//Get the ids of related accounts by performing a query against the relationship with the contact id
List<Guid> accountIds = GetRelatedEntities(proxy, "contact", contactId, "account_primary_contact", "account").ToList();

//Check that we actually recieved the ids we wanted
Console.WriteLine("Account A: " + accountIds.Contains(accountAId));
Console.WriteLine("Account B: " + accountIds.Contains(accountBId));
Console.WriteLine("Account C: " + accountIds.Contains(accountCId));
Console.WriteLine("Account D: " + accountIds.Contains(accountDId));

Output:
Account A: True
Account B: True
Account C: True
Account D: False

So, we received the result we wanted, but in reality its not very often we just want the ids, often we will need some columns as well, and have to peform some filtering of the results. In which case this function doesn’t achieve this goal. So lets take the function as a template and break it out to add some additional filtering and selection of columns (this could eventually be wrapped up into some clever generic function but for now I’ll keep it simple).

So here, we are going to get the account names and filter out any account name ending in C, this means we should only get accounts A & B. Note: I’ve cut out the entity creation bit from this snippet but its the same as above (e.g. proxy.Create(contact), etc, etc).

//the related entity we are going to retrieve
QueryExpression query = new QueryExpression("account");
query.ColumnSet = new ColumnSet("name");
//we dont want the account ending in C
query.Criteria.AddCondition(new ConditionExpression("name", ConditionOperator.DoesNotEndWith, "c"));

//the relationship that links the primary to the target
Relationship relationship = new Relationship("account_primary_contact");

//the query collection which forms the request
RelationshipQueryCollection relatedEntity = new RelationshipQueryCollection();
relatedEntity.Add(relationship, query);

//the request to get the primary entity with the related records
RetrieveRequest request = new RetrieveRequest();
request.RelatedEntitiesQuery = relatedEntity;
request.ColumnSet = new ColumnSet("lastname");
request.Target = new EntityReference("contact", contactId);

RetrieveResponse r = (RetrieveResponse)proxy.Execute(request);

//write the results
Console.WriteLine("Primary entity: {0}, Name: {1}", r.Entity.LogicalName, r.Entity["lastname"].ToString());

//parse the related entities
foreach (Entity e in r.Entity.RelatedEntities[relationship].Entities)
{
Console.WriteLine("Related entity: {0}, Name: {1}", e.LogicalName, e["name"]);
}

Output:

Primary entity: contact, Name: Test
Related entity: account, Name: Relationship Test A
Related entity: account, Name: Relationship Test B