{"id":386,"date":"2014-03-31T11:35:37","date_gmt":"2014-03-31T06:05:37","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=386"},"modified":"2021-12-06T15:02:53","modified_gmt":"2021-12-06T09:32:53","slug":"use-of-alias-in-fetchxml","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2014\/03\/use-of-alias-in-fetchxml\/","title":{"rendered":"Use of Alias in FetchXML"},"content":{"rendered":"

Introduction<\/span><\/b>:<\/p>\n

I am sure by now almost anyone developing on Dynamics CRM would have had experience working with FetchXML queries, the Dynamics CRM proprietary format to query data using Dynamics CRM SDK API.<\/p>\n

We recently came across a scenario where we needed to use an alias for an attribute. Similar to the SQL query<\/span><\/p>\n

Select fullname as ContactName from Contact<\/span><\/p>\n

Our search to get this work with FetchXML led us to the finding that this is indeed possible!!!<\/span><\/p>\n

Let me first give a background on how alias has been generally used by us.<\/p>\n

Use of Alias in Link entities:<\/span><\/b><\/p>\n

To design queries that require you to join one or more tables, we make use of Link-Entity. The Link-Entity syntax requires providing of an Alias using which the values of the attributes can be accessed from the resultset. If you do not provide an explicit alias, the platform would auto generate the alias for you.<\/span><\/p>\n

Here is sample fetchxml that will read the details of the invoice and the related customer be it account or contact.<\/p>\n

string query = @”<fetch\u00a0version=’1.0′\u00a0output-format=’xml-platform’\u00a0mapping=’logical’\u00a0distinct=’false’><\/p>\n

<entity\u00a0name=’invoice’><\/p>\n

<attribute\u00a0name=’name’\/><\/p>\n

<attribute\u00a0name=’customerid’\/><\/p>\n

<attribute\u00a0name=’statuscode’\/><\/p>\n

<attribute\u00a0name=’totalamount’\/><\/p>\n

<attribute\u00a0name=’invoiceid’\/><\/p>\n

<order\u00a0attribute=’name’\u00a0descending=’false’\/><\/p>\n

<link-entity\u00a0name=’account’\u00a0from=’accountid’\u00a0to=’customerid’\u00a0link-type=’outer’\u00a0alias=’acc’\/><\/p>\n

<attribute\u00a0\u00a0name=’address1_line1’\/><\/p>\n

<attribute\u00a0name=’address1_line2’\/><\/p>\n

<attribute\u00a0name=’address1_city’\/><\/p>\n

<attribute\u00a0name=’address1_country’\/><\/p>\n

<attribute\u00a0name=’address1_postalcode’\/><\/p>\n

<attribute\u00a0name=’address1_stateorprovince’\/><\/p>\n

<attribute\u00a0name=’emailaddress1′><\/p>\n

<\/link-entity><\/p>\n

<link-entity\u00a0name=’contact’\u00a0from=’contactid’\u00a0to=’customerid’\u00a0link-type=’outer’\u00a0alias=’con’><\/p>\n

<attribute\u00a0\u00a0name=’address1_line1’\/><\/p>\n

<attribute\u00a0name=’address1_line2’\/><\/p>\n

<attribute\u00a0name=’address1_city’\/><\/p>\n

<attribute\u00a0name=’address1_country’\/><\/p>\n

<attribute\u00a0name=’address1_postalcode’\/><\/p>\n

<attribute\u00a0name=’address1_stateorprovince’\/><\/p>\n

<attribute\u00a0name=’emailaddress1’\/><\/p>\n

<link-entity<\/p>\n

<\/entity><\/p>\n

<\/fetch> “;<\/p>\n

We can use following code to execute fetchxml to Retrieve the results .<\/span><\/p>\n

\/\/create FetchExpression object and pass fetchxml<\/span><\/p>\n

FetchExpression fetchExpression = new FetchExpression(query);<\/p>\n

\/\/Execute the fetchxml to retrieve records<\/span><\/p>\n

EntityCollection entCollection = _service.RetrieveMultiple(fetchExpression);<\/p>\n

Now here if you would like to read attribute values of Linked entities then it can be possible using following ways.<\/span><\/p>\n

Read Account entity attributes as follows.<\/span><\/p>\n

((AliasedValue)entity.Attributes[“acc.emailaddress1”]).Value<\/p>\n

Read Contact entity attributes as follows.<\/span><\/p>\n

((AliasedValue)entity.Attributes[“con.emailaddress1”]).Value<\/p>\n

Use Alias for an attribute<\/span><\/b><\/p>\n

The examples in SDK always used Alias for aggregation and so it was my understanding that Alias for attributes can only be used in case of aggregation. But simply trying out the below query actually returned desired results.<\/span><\/p>\n

Here we have to read entity attribute values which can be done using alias as shown below. You can specify alias for each attribute and then read values by referring the Alias provided.<\/span><\/p>\n

<fetch\u00a0version=’1.0′\u00a0output-format=’xml-platform’\u00a0mapping=’logical’\u00a0distinct=’false’><\/span><\/p>\n

<entity\u00a0name=’invoice’><\/p>\n

<attribute\u00a0name=’name’\u00a0alias=’inv_name’\/><\/p>\n

<attribute\u00a0name=’totalamount’alias=’inv_amount’\/><\/p>\n

<attribute\u00a0name=’customerid’alias=’inv_customer’\/><\/p>\n

<order\u00a0attribute=’name’\u00a0descending=’false’\/><\/p>\n

<\/entity><\/p>\n

<\/fetch><\/p>\n

You would now read the attribute values as shown below with the Alias name instead of the attribute name.<\/span><\/p>\n

((EntityReference)((AliasedValue)entity.Attributes[“inv_customer”]).Value).Name<\/span><\/p>\n

Use of Alias for Attribute in Link Entity Queries<\/span><\/b><\/p>\n

To take this one step further, we decided to test by margining both Link Entity alias and also providing an alias to the attribute read from the Link-Entity to check how that works.<\/span><\/p>\n

Here following fetch query will show how to give alias names to the link entity attributes. By using this alias names we are able to retrieve the link entities attributes as well.<\/span><\/p>\n

<fetch\u00a0version=’1.0′\u00a0output-format=’xml-platform’\u00a0mapping=’logical’\u00a0distinct=’false’><\/span><\/p>\n

<entity\u00a0name=’invoice’><\/p>\n

<attribute\u00a0name=’name’\/><\/p>\n

<attribute\u00a0name=’customerid’\/><\/p>\n

<attribute\u00a0name=’statuscode’\/><\/p>\n

<attribute\u00a0name=’totalamount’\/><\/p>\n

<attribute\u00a0name=’invoiceid’\/><\/p>\n

<order\u00a0attribute=’name’\u00a0descending=’false’\/><\/p>\n

<link-entity\u00a0name=’account’\u00a0from=’accountid’\u00a0to=’customerid’\u00a0link-type=’outer’\u00a0alias=’acc’><\/span><\/p>\n

<attribute\u00a0\u00a0name=’address1_line1′\u00a0alias\u00a0=\u00a0‘Line1’\/><\/p>\n

<attribute\u00a0name=’address1_line2’\u00a0alias\u00a0=\u00a0‘Line2’\/><\/p>\n

<attribute\u00a0name=’address1_city’\u00a0alias\u00a0=\u00a0‘city’\/><\/p>\n

<attribute\u00a0name=’address1_country’\u00a0alias\u00a0=\u00a0‘country’\/><\/p>\n

<attribute\u00a0name=’address1_postalcode’\u00a0alias\u00a0=\u00a0‘code’\/><\/p>\n

<attribute\u00a0name=’address1_stateorprovince’\u00a0alias\u00a0=\u00a0‘state’\/><\/p>\n

<attribute\u00a0name=’emailaddress1′\u00a0alias\u00a0=\u00a0’email’\/><\/p>\n

<\/link-entity><\/p>\n

<link-entity\u00a0name=’contact’\u00a0from=’contactid’\u00a0to=’customerid’\u00a0link-type=’outer’\u00a0alias=’con’><\/span><\/p>\n

<attribute\u00a0\u00a0name=’address1_line1′\u00a0alias\u00a0=\u00a0‘cont_Line1’\/><\/p>\n

<attribute\u00a0name=’address1_line2’\u00a0alias\u00a0=\u00a0‘cont_Line2’\/><\/p>\n

<attribute\u00a0name=’address1_city’\u00a0 \/><\/p>\n

<attribute\u00a0name=’address1_country’\u00a0alias\u00a0=\u00a0‘cont_country’\/><\/p>\n

<attribute\u00a0name=’address1_postalcode’\u00a0alias\u00a0=\u00a0‘cont_code’\/><\/p>\n

<attribute\u00a0name=’address1_stateorprovince’\u00a0alias\u00a0=\u00a0‘cont_state’\/><\/p>\n

<attribute\u00a0name=’emailaddress1′\u00a0alias\u00a0=\u00a0‘cont_email’\/><\/p>\n

<\/link-entity><\/p>\n

<\/entity><\/p>\n

<\/fetch><\/p>\n

In the first query for link entity provided above, we needed to prefix the attribute with the entity alias to read the values. But here we can directly read the values as follows<\/span><\/p>\n

((AliasedValue)entity.Attributes[“cont_city”]).Value<\/span><\/p>\n

if the Alias had not been provided at the attribute level the value would be read as<\/span><\/p>\n

((AliasedValue)entity.Attributes[“con. address1_city”]).Value<\/span><\/p>\n

Conclusion:<\/span><\/b><\/p>\n

Alias can be used for the Entities in case of Link-Entity as well as for Attributes even if there is no aggregation provided.<\/p>\n

One Pic = 1000 words! Analyze data 90% faster with visualization apps!<\/div><\/div><\/h2>\n

Get optimum visualization of Dynamics 365 CRM data with –<\/em>
\nKanban Board<\/a> <\/strong>\u2013 Visualize Dynamics 365 CRM data in Kanban view by categorizing entity records in lanes and rows as per their status, priority, etc.<\/em>
\nMap My Relationships<\/a><\/strong> \u2013 Map My Relationships \u2013 Visualize connections and relationships between Dynamics 365 CRM entities or related records in a Mind Map view.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

Introduction: I am sure by now almost anyone developing on Dynamics CRM would have had experience working with FetchXML queries, the Dynamics CRM proprietary format to query data using Dynamics CRM SDK API. We recently came across a scenario where we needed to use an alias for an attribute. Similar to the SQL query Select\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[21,22,24],"tags":[392,795,797,1390],"class_list":["post-386","post","type-post","status-publish","format-standard","hentry","category-dynamics-crm-2013","category-dynamics-crm-2015","category-dynamics-crm-2016","tag-crm-2013","tag-fetchxml","tag-fetchxml-dynamics-crm","tag-queries"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/386","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=386"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/386\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=386"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=386"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}