Home > CRM 2011 > Reading LinkEntity attributes using Query Expression in CRM 2011

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;

}

🙂

  1. Renjith
    January 9, 2014 at 4:29 PM

    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

    • January 9, 2014 at 5:13 PM

      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);

  2. Gary
    January 31, 2014 at 11:27 PM

    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

  3. karl
    October 17, 2016 at 10:10 PM

    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

    //}

    }
    //}
    }

    }

  4. Shaikh Irfan
    October 21, 2020 at 6:49 PM

    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

    • October 23, 2020 at 5:24 PM

      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.

  5. Shaikh Irfan
    October 21, 2020 at 6:51 PM

    Hii Rajeev Sir plz help me above query

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: