For example, if we want to retrieve the active contacts related to an account then, we can design the query as shown in the below code,
using (OrganizationServiceProxy _orgserviceproxy = new OrganizationServiceProxy(new Uri(OrganizationUri), null, Credentials, null))
{
//create the query expression object
QueryExpression query = new QueryExpression();
//Query on reated entity records
query.EntityName = "contact";
//Retrieve the all attributes of the related record
query.ColumnSet = new ColumnSet(true);
//create the relationship object
Relationship relationship = new Relationship();
//add the condition where you can retrieve only the account related active contacts
query.Criteria = new FilterExpression();
query.Criteria.AddCondition(new ConditionExpression("statecode", ConditionOperator.Equal, "Active"));
// name of relationship between account & contact
relationship.SchemaName = "contact_customer_accounts";
//create relationshipQueryCollection Object
RelationshipQueryCollection relatedEntity = new RelationshipQueryCollection();
//Add the your relation and query to the RelationshipQueryCollection
relatedEntity.Add(relationship, query);
//create the retrieve request object
RetrieveRequest request = new RetrieveRequest();
//add the relatedentities query
request.RelatedEntitiesQuery = relatedEntity;
//set column to and the condition for the account
request.ColumnSet = new ColumnSet("accountid");
request.Target = new EntityReference { Id = accountID, LogicalName = "account" };
//execute the request
RetrieveResponse response = (RetrieveResponse)_orgserviceproxy.Execute(request);
// here you can check collection count
if (((DataCollection
return true;
else
return false;
}
One limitation though, is that it is only available with the Retrieve Request and not the Retrieve Multiple request. Wonder why this has not be added for Retrieve Multiple request as this can be of great help to return all the data required in one request.
Thank you this was really helpful for me, I was breaking my head for quite sometime trying to figure this out.
ReplyDeleteIt isn't on RetrieveMultiple because LinkEntities in the QueryExpression can include associated columns - so you can include as many related entities and fields as deep as you want to go with nested LinkEntities.
ReplyDeletewhat is accountID here?? are we supposed to set it???
ReplyDeleteaccountID is the id of primary entity in this (1:N) relationship. Here in this example, primary entity is “account” and related entity is “contact”. So, accountID is the id of the account lookup on contact.
ReplyDeleteHere, it is the variable and value we are getting from somewhere else. You need to set this accountID.
Hope this helps!