Use of Alias in FetchXML

By | March 31, 2014

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 fullname as ContactName from Contact

Our search to get this work with FetchXML led us to the finding that this is indeed possible!!!

Let me first give a background on how alias has been generally used by us.

Use of Alias in Link entities:

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.

Here is sample fetchxml that will read the details of the invoice and the related customer be it account or contact.

string query = @”<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>

<entity name=’invoice’>

<attribute name=’name’/>

<attribute name=’customerid’/>

<attribute name=’statuscode’/>

<attribute name=’totalamount’/>

<attribute name=’invoiceid’/>

<order attribute=’name’ descending=’false’/>

<link-entity name=’account’ from=’accountid’ to=’customerid’ link-type=’outer’ alias=’acc’/>

<attribute  name=’address1_line1’/>

<attribute name=’address1_line2’/>

<attribute name=’address1_city’/>

<attribute name=’address1_country’/>

<attribute name=’address1_postalcode’/>

<attribute name=’address1_stateorprovince’/>

<attribute name=’emailaddress1′>

</link-entity>

<link-entity name=’contact’ from=’contactid’ to=’customerid’ link-type=’outer’ alias=’con’>

<attribute  name=’address1_line1’/>

<attribute name=’address1_line2’/>

<attribute name=’address1_city’/>

<attribute name=’address1_country’/>

<attribute name=’address1_postalcode’/>

<attribute name=’address1_stateorprovince’/>

<attribute name=’emailaddress1’/>

<link-entity

</entity>

</fetch> “;

We can use following code to execute fetchxml to Retrieve the results .

//create FetchExpression object and pass fetchxml

FetchExpression fetchExpression = new FetchExpression(query);

//Execute the fetchxml to retrieve records

EntityCollection entCollection = _service.RetrieveMultiple(fetchExpression);

Now here if you would like to read attribute values of Linked entities then it can be possible using following ways.

Read Account entity attributes as follows.

((AliasedValue)entity.Attributes["acc.emailaddress1"]).Value

Read Contact entity attributes as follows.

((AliasedValue)entity.Attributes["con.emailaddress1"]).Value

Use Alias for an attribute

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.

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.

<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>

<entity name=’invoice’>

<attribute name=’name’ alias=’inv_name’/>

<attribute name=’totalamount’alias=’inv_amount’/>

<attribute name=’customerid’alias=’inv_customer’/>

<order attribute=’name’ descending=’false’/>

</entity>

</fetch>

You would now read the attribute values as shown below with the Alias name instead of the attribute name.

((EntityReference)((AliasedValue)entity.Attributes["inv_customer"]).Value).Name

Use of Alias for Attribute in Link Entity Queries

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.

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.

<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>

<entity name=’invoice’>

<attribute name=’name’/>

<attribute name=’customerid’/>

<attribute name=’statuscode’/>

<attribute name=’totalamount’/>

<attribute name=’invoiceid’/>

<order attribute=’name’ descending=’false’/>

<link-entity name=’account’ from=’accountid’ to=’customerid’ link-type=’outer’ alias=’acc’>

<attribute  name=’address1_line1′ alias = ‘Line1’/>

<attribute name=’address1_line2′ alias = ‘Line2’/>

<attribute name=’address1_city’ alias = ‘city’/>

<attribute name=’address1_country’ alias = ‘country’/>

<attribute name=’address1_postalcode’ alias = ‘code’/>

<attribute name=’address1_stateorprovince’ alias = ‘state’/>

<attribute name=’emailaddress1′ alias = ‘email’/>

</link-entity>

<link-entity name=’contact’ from=’contactid’ to=’customerid’ link-type=’outer’ alias=’con’>

<attribute  name=’address1_line1′ alias = ‘cont_Line1’/>

<attribute name=’address1_line2′ alias = ‘cont_Line2’/>

<attribute name=’address1_city’  />

<attribute name=’address1_country’ alias = ‘cont_country’/>

<attribute name=’address1_postalcode’ alias = ‘cont_code’/>

<attribute name=’address1_stateorprovince’ alias = ‘cont_state’/>

<attribute name=’emailaddress1′ alias = ‘cont_email’/>

</link-entity>

</entity>

</fetch>

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

((AliasedValue)entity.Attributes["cont_city"]).Value

if the Alias had not been provided at the attribute level the value would be read as

((AliasedValue)entity.Attributes["con. address1_city"]).Value

Conclusion:

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

Leave a Reply