Retrieve primary entities along with related entities with one QueryExpression

Retrieve related records for a parent record

It’s possible to get related records of  a relationship when we retrieve a parent record using RelatedEntitiesQuery property in the RetrieveRequest. That means you have to construct the RetrieveRequest not using the Retrieve method from the OrganizationServiceProxy class.

The RelatedEntitiesQuery property is a RelationshipQueryCollection. It’s a dictionary of multiple QueryExpressions to query related entities with the keys are the relationship names.

So all you have to do is create QueryExpression for each related entities with fields you want to include in the parent record, and add the query to the RelatedEntitiesQuery collection with the relationship name as the key, then call Retrieve method as usual. The related records can be accessed in the response.

For example, in my solution, an Account has many related Events. I want to retrieve a specific account and all of its Events. Below is the demo code.

// Query the related records
QueryExpression query = new QueryExpression();
query.EntityName = "hvn_event";
query.ColumnSet = new ColumnSet("hvn_name");

// Get the relationship between Account and Event
Relationship relationship = new Relationship("hvn_account_events");
relationship.PrimaryEntityRole = EntityRole.Referenced;

// Create list of related entities
RelationshipQueryCollection relatedEntity = new RelationshipQueryCollection();
relatedEntity.Add(relationship, query);

// Request to get a specified account record with the related records
RetrieveRequest request = new RetrieveRequest();
request.RelatedEntitiesQuery = relatedEntity;
request.ColumnSet = new ColumnSet("name");
request.Target = new EntityReference("account", new Guid("ED71041E-D1B3-E711-A825-000D3AA32537"));

RetrieveResponse response = (RetrieveResponse)organizationProxy.Execute(request);

// Print out the results
Console.WriteLine("Account name {0}", response.Entity["name"]);
foreach (var item in response.Entity.RelatedEntities[relationship].Entities)
{
Console.WriteLine(" Related event name {0}", item["hvn_name"]);
}

Here is the result:

Account name A. Datum Corporation (sample)
Related event name test
Related event name No feedback
Related event name teeee
Related event name approval
Related event name Testtt
Related event name test security field

Retrieve related records for multiple parent records

Unfortunately, we can’t do it with the same manner above when using RetrieveMultiple method. Actually, we can retrieve related records with LinkEntities property in QueryExpression to join to the related entity and select fields, but the result looks like a flat dataset instead of a parent-child hierarchy. For example, let’s look at this code

// Query Account entity
QueryExpression query = new QueryExpression();
query.EntityName = "account";
query.ColumnSet = new ColumnSet("name");

// Use link entity to join to related Event entity
LinkEntity linkEnt = query.AddLink("hvn_event", "accountid", "hvn_organizeraccountid", JoinOperator.Inner);
linkEnt.Columns.AddColumn("hvn_name");
linkEnt.EntityAlias = "event";

EntityCollection results = organizationProxy.RetrieveMultiple(query);

If you look in the result entities’ Attribute property, the data in the following demo will look like this

So each related record will be a row, and the parent will appear repeatedly in the result. Also to access to the related record’s fields, you have to use a string with a format to get an AliasedValue then cast it to known type to access its value. Below is the demo to get all accounts with related events.

There is a workaround for the AliasedValue problem. Daryl, an MVP, made some extension methods for the Entity class so that we can access to AliasedValue columns easily.

Simplifying Retrieval of Aliased Values

Using a second RetrieveMultiple

It may be easier if we just ignore the “in one query” requirement. In that case, we can iterate the list of parent records, construct a QueryExpression for related records and call another RetrieveMultiple.

// Query Account entity
QueryExpression query = new QueryExpression();
query.EntityName = "account";
query.ColumnSet = new ColumnSet("name");

EntityCollection results = organizationProxy.RetrieveMultiple(query);

// Query related Events for each Account
foreach (Entity item in results.Entities)
{
QueryExpression childQuery = new QueryExpression();
childQuery.EntityName = "hvn_event";
childQuery.ColumnSet = new ColumnSet("hvn_name");
childQuery.Criteria.AddCondition("hvn_organizeraccountid", ConditionOperator.Equal, item.Attributes["accountid"]);
EntityCollection relatedResults = organizationProxy.RetrieveMultiple(childQuery);
}

In conclusion, there are many ways to meet a requirement, our job is to consider and choose which one is best for the customer as well as for our developer life. Don’t force yourself to do it the hard way, but challenging yourself is good though.

Hope it helps!

PS: I just used late bound and didn’t check if entity/attributes exists or null values for quick demo purpose. Make sure your code is safe!