Rajeev Pentyala – Microsoft Power Platform

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;

}

🙂