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.
One Pic = 1000 words! Analyze data 90% faster with visualization apps!
Get optimum visualization of Dynamics 365 CRM data with –
Kanban Board – Visualize Dynamics 365 CRM data in Kanban view by categorizing entity records in lanes and rows as per their status, priority, etc.
Map My Relationships – Map My Relationships – Visualize connections and relationships between Dynamics 365 CRM entities or related records in a Mind Map view.