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;
}
🙂
Rajeev Pentyala - Microsoft Power Platform 
Hey Rajeev,
This is a nice article and it helped me, even though not fully. I wanted to get the related entity for a particular record. For example, I need to get the related accounts for a particular contact in this case. For that how can I add the condition expression in this query expression.
Appreciate your earliest response. Thanks in advance.
Cheers
Hi Renjith,
Add below condition to Query Expression
Guid contactId = Guid.NewGuid(); // Pass GUID of your contact
ConditionExpression condContactsById = new ConditionExpression(“contactid”, ConditionOperator.Equal, contactId);
query.Criteria = new FilterExpression(LogicalOperator.And);
query.Criteria.Conditions.Add(condContactsById);
Hi,
NIc article..!
i have link entity filter like this.
-owning user(user)
-Teams
Is default equals No
-Users
user equals current user
and one more link entity filter,
owning Team(Team)
-Users
-user equals current user
since these 2 are link entities i can not write OR condition in the filter. Is there a way you can achieve this in query expression.
Any help will be greatly be appreciated .
Thanks.
Gary
Hello,
I need your help please.
In this ligne EntityCollection LIGNEACCOUNT = _orgService.RetrieveMultiple(LISTECLIENT); the Variable “LIGNEACCOUNT” is null and mùy code don’t work. Somebody can’t help me please?
public class CExportClient
{
private OrganizationService _orgService;
public void RunExportClient()
{
//CrmConnection connection = CrmConnection.Parse(connectionString);
//using (_orgService = new OrganizationService(connection))
//{
// Obtain information about the logged on user from the web service.
#region Traitement export de client à traiter est vrai
QueryExpression LISTECLIENT = new QueryExpression(“account”);
LISTECLIENT.ColumnSet.AllColumns = true;
LISTECLIENT.LinkEntities.Add(new LinkEntity(“account”, “invoice”, “accountid”, “customerid”, JoinOperator.Inner));
LISTECLIENT.LinkEntities[0].Columns.AddColumns(“name”, “new_tvaintracom”, “new_bic”, “new_iban”, “adress1_line1”, “adress1_line2”, “adress1_line3”, “adress1_postalcode”, “adress1_city”, “adress1_country”);
LISTECLIENT.LinkEntities[0].EntityAlias = “ExClient”;
LISTECLIENT.LinkEntities[0].LinkCriteria.AddCondition(“prompt_factatraiter”, ConditionOperator.Equal, true);
EntityCollection LIGNEACCOUNT = _orgService.RetrieveMultiple(LISTECLIENT);
Log.MonitoringLogger.Info(“Nombre de client à traiter : ” + LIGNEACCOUNT.Entities.Count.ToString());
for (int a = 0; a < LIGNEACCOUNT.Entities.Count; a++)//liste le nombre de ligne de facture
{
Log.MonitoringLogger.Info("Boucle client: " + a.ToString());
Log.MonitoringLogger.Info("Client n°: " + LIGNEACCOUNT.Entities[a].Attributes["accountnumber"].ToString());
Log.MonitoringLogger.Info("Id prompt_exportclient : " + ((AliasedValue)LIGNEACCOUNT.Entities[a].Attributes["ExFact.prompt_exportclienttid"]).Value.ToString());
//initialisation du nom de fichier
string datefichier = DateTime.Now.ToString("YYYYMMDD");
string heurefichier = DateTime.Now.ToString("HHmm");
string NomFichier = "clients_" + datefichier + "_" + heurefichier + ".xml";
#region XML de commande à générer et déposer sur ftpes
#region Création de l'en-tête du fichier XML
XmlDocument xmlDoc = new XmlDocument(); // Création du document xml
XmlNode docNode = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", null);//le doctyp du fichier xml
xmlDoc.AppendChild(docNode);
XmlNode racineNode = xmlDoc.CreateElement("Entities");
racineNode.Value = "ProgName = 'Client'";
xmlDoc.AppendChild(racineNode);
//Invoice
XmlNode accountNode = xmlDoc.CreateElement("Entity");
XmlAttribute attribute = xmlDoc.CreateAttribute("Caller");
accountNode.InnerText = "CRM";
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("Type");
accountNode.InnerText = "GC";
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("ContratType");
accountNode.InnerText = "CLT";
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("ModelCode");
accountNode.InnerText = "CLT_3";
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("RefAgency");
accountNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_iban".Substring(9, 5)]); ;
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("RefAccount");
accountNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_iban".Substring(14, 11)]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("RefTier");
accountNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_siret"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("Reason");
accountNode.InnerText = Convert.ToString((string)LIGNEACCOUNT.Entities[a].Attributes["name"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("IntraConVat");
accountNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_tvaintracom"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("AddressC1");
accountNode.InnerText = Convert.ToString((string)LIGNEACCOUNT.Entities[a].Attributes["billto_line1"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("AddressC4");
accountNode.InnerText = Convert.ToString((string)LIGNEACCOUNT.Entities[a].Attributes["billto_line2"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("PostalCode");
accountNode.InnerText = Convert.ToString((string)LIGNEACCOUNT.Entities[a].Attributes["billto_postalcode"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("City");
accountNode.InnerText = Convert.ToString((string)LIGNEACCOUNT.Entities[a].Attributes["billto_city"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("Country");
accountNode.InnerText = Convert.ToString((string)LIGNEACCOUNT.Entities[a].Attributes["billto_country"]);
racineNode.AppendChild(accountNode);
attribute = xmlDoc.CreateAttribute("CurrCode");
accountNode.InnerText = "EUR";
racineNode.AppendChild(accountNode);
//Banks
XmlNode linesNode = xmlDoc.CreateElement("Banks");
//Bank
XmlNode lineNode = xmlDoc.CreateAttribute("Bank");
XmlAttribute lineAttribute = xmlDoc.CreateAttribute("BankCode");
lineNode.InnerText = "001";
linesNode.AppendChild(lineNode);
lineAttribute = xmlDoc.CreateAttribute("BranchCode");
lineNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_iban".Substring(4, 5)]);
linesNode.AppendChild(lineNode);
lineAttribute = xmlDoc.CreateAttribute("AgencyCode");
lineNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_iban".Substring(9, 5)]);
linesNode.AppendChild(lineNode);
lineAttribute = xmlDoc.CreateAttribute("AccountNumber");
lineNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_iban".Substring(14, 11)]);
linesNode.AppendChild(lineNode);
lineAttribute = xmlDoc.CreateAttribute("RibKey");
lineNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_iban".Substring(25)]);
linesNode.AppendChild(lineNode);
lineAttribute = xmlDoc.CreateAttribute("BicCode");
lineNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_bic"]);
linesNode.AppendChild(lineNode);
lineAttribute = xmlDoc.CreateAttribute("IbanCode");
lineNode.InnerText = Convert.ToString(LIGNEACCOUNT.Entities[a].Attributes["new_iban"]);
linesNode.AppendChild(lineNode);
#endregion
#endregion
#endregion
Log.MonitoringLogger.Info("OuterXML" + xmlDoc.OuterXml);
MemoryStream xmlStream = new MemoryStream();
string PathServeurC = ConfigurationManager.AppSettings["PathServeurC"];
xmlDoc.Save(PathServeurC);
#region MAJ prompt_exportclient
Log.MonitoringLogger.Info(" XML-Final");
#endregion
//}
}
//}
}
}
Hii Rejeev i want wo create a plugin on whenever an record is created in custom entity with email id.I want contact will be created on the basis of that email id in for the same customer in that record
Hi Shaikh,
If I understand your ask correctly,
– You have 2 entities CustomEntity,Contact.
– When a CustomEntity record creates with a ‘EmailId’, you need to create a new ‘Contact’ record by copying the CustomEntity records ‘EmailId’.
If my understanding is correct, you can create a Workflow/Flow on PostCreate of CustomEntity and add a step to Create ‘Contact’ record. However basic details like First and Last name of Contact will be blank.
Ok But Rajeev Sir workflow will not chk that this customer already have contact with that email id.See if email id is same in that customer then return if email id is not there then create new contact with that email.Also if email id is different then it update the new contact with that emal id
I have check workflow will not fullfill my requirement thats why i want to create a plugin
Hii Rajeev Sir plz help me above query