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

}

🙂

Advertisements
  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

    //}

    }
    //}
    }

    }

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: