Retrieve Multiple - FetchXML with Alias

This is not a complex thing, but though of posting the code snippet since I found it important to explain a bit. When we need to retrieve data in related records few levels away from our current context, best method is using the FetchXML and use ReiveMultiple method. This will avoid us using many server calls to reach our entity.

Reason for writing this post is to explain one point. When we do complex FetchXMLs we definitely need to use Alias for linked entities. So reading the data in the code can be little different.

Check my example; I have an entity called job. Job got a lookup field to Project. Project got a lookup to User. Suppose we need to read the Mobile Number of the user (3 levels ahead) while we have only Job Id in hand, we use FetchXML as below;

internal static EntityCollection RetriveProjectAdminMobileByJobId(IOrganizationService service, Guid Id)
{
var fetchXml = string.Format(@"<fetch mapping='logical' output-format='xml-platform' version='1.0' distinct='false'>  
               <entity name='new_job'>
               <filter type='and'>
               <condition attribute='new_jobid' operator='eq' value='{0}' />
               </filter>
                   <link-entity name='new_project' to='new_jobprojectid' from='new_projectid' alias='PROJ' link-type='outer' visible='false'>
                       <link-entity name='systemuser' to='new_projectadmin' from='systemuserid' link-type='outer' alias ='PROJADMIN'>
                            <attribute name='mobilephone' />
                        </link-entity>
                   </link-entity>
               </entity>
               </fetch>", Id);
            return service.RetrieveMultiple(new FetchExpression(fetchXml));

Please notice, how I have used Aliases meaningfully. Now what we need to keep in mind is resulting fields would come with that Alias. In fact, mobile phone field is like PROJADMIN.mobilephone. Now check how I read it in C#;

var entityCollection = RetriveProjectAdminMobileByJobId(service, workOrderRef.Id);

if (entityCollection == null || entityCollection.Entities == null || entityCollection.Entities.Count <= 0)
    return;

if (entityCollection.Entities[0].Attributes.Contains("PROJADMIN.mobilephone"))
    string AdminMobile = (string)((AliasedValue)entityCollection.Entities[0].Attributes["PROJADMIN.mobilephone"]).Value;

I advice always try the FetchXmls before using. Best tool to do so is FetchXM Tester of XrmToolBox.

There is a one pitfall. I have seen some tools which are not returning the correct field names as expected. For example, I have tested same FetxhXML in DataSet creating tool in SSRS report authering extensions in VS2012 that returned like PROJADMIN-mobilephone, which is WRONG.