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

About these ads

5 thoughts on “Querying CRM Relationships

  1. If I’m reading this correctly, you can only return a single item (and whatever columns for that item that you choose) and one to many related entities as long as a relationship exists. So as long as you know the id of the “parent” entity, you can populate as many child entity relationships that you want, using whatever query expression constraints that you’d like?

    If you were using the early bound entities, would the child collections be populated for the entity?

  2. Hi daryllabar,

    I’ve added an example which should hopefully make usage clear. To respond to your points directly:

    “you can only return a single item” – Yes, this is a restriction of the RetrieveRequest, it takes a Target EntityReference and returns information about that record. As a side note RetrieveMultipleRequest doesn’t have a RelatedEntitiesQuery so you can’t do this for multiple entities (at least not using this method).

    “and whatever columns for that item that you choose” – No, you can also return columns of related entities. For the primary entity set the ColumnSet on the RetrieveRequest, for the related entities set the ColumnSet on the QueryExpression.

    “one to many related entities” – No, this method supports many to many as well, just change the relationship name – this is when this method is most useful, e.g. you need something generic that supports one to many and many to many at the same time.

    “as long as a relationship exists”, – I presume so, I’ve never tried it for a relationship that doesn’t exist, but I would expect the call to fail.

    “you can populate as many child entity relationships that you want” – I believe so.

    “using whatever query expression constraints that you’d like?” – Yes

    “If you were using the early bound entities, would the child collections be populated for the entity?” – Don’t know, have to get back to you on this one, I usually use the last bound entities myself.

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