Archive
Reading LinkEntity attributes using Query Expression in CRM 2011
Assume you have to retrieve
- All the “Contact” records with fields “Full Name, Address1_City”
- Along with ‘Account Number’ field of ‘Account’ whom the contacts are associated with.
Relationship between Contact & Account
In CRM, there is out of the box 1:N relationship between Account & Contact.
- Contact has ‘ParentCustomerId’ field as foreignkey of ‘Account’ entity
- ‘Account’ entity’s primary field is ‘AccountId’
Preparing Query Expression
- To get the ‘Contact’ attributes along with LinkEntity (i.e.,Account) attributes (i.e., Account Number), we will write a Query Expression with (Entity as ‘Contact’ and LinkEntity as ‘Account’)
- To read LinkEntity attribute’s we have to read the attribute along with ‘EntityAlias’ of LinkEntity (i.e., EntityAlias.AttributeName)
Below is the query expression
var query = new QueryExpression(“contact”);
var columnNames = new[] { “fullname”,”address1_city” };
query.ColumnSet = new ColumnSet(columnNames);
// ‘Account’ as LinkEntity
var colsAccount = new[] { “accountnumber” };
LinkEntity linkEntityAccount = new LinkEntity() {
LinkFromEntityName = “contact”,
LinkFromAttributeName = “parentcustomerid”,
LinkToEntityName = “account”,
LinkToAttributeName = “accountid”,
JoinOperator = JoinOperator.Inner,
Columns = new ColumnSet(colsAccount),
EntityAlias = “aliasAccount”
};
query.LinkEntities.Add(linkEntityAccount);
// Execute Query using RetrieveMultiple
EntityCollection contacts = this.service.RetrieveMultiple(query);
if (contacts != null) {
foreach (var targetEntity in contacts.Entities) {
// Read “Account Number” along with Alias
var accountNumber = getAttributeValue(targetEntity, “aliasAccount.accountnumber“);
var contactFullname = getAttributeValue(targetEntity, “fullname”);
}
}
/// <summary>
/// Generic function to get value from attribute
/// </summary>
private string getAttributeValue(Entity targetEntity, string attributeName) {
if (string.IsNullOrEmpty(attributeName)) {
return string.Empty;
}
if (targetEntity[attributeName] is AliasedValue) {
return (targetEntity[attributeName] as AliasedValue).Value.ToString();
}
else {
return targetEntity[attributeName].ToString();
}
}
return string.Empty;
}
🙂