MSCRM 4.0: LINQ to FetchXML Queries

When it comes to querying data from Microsoft Dynamics CRM server, I am a much big fan of FetchXML if compared to CRM Query Expression. There are a few reasons for that:

1. FetchXML is more powerful, it can do something that Query Expression may not be able to do the same, such as aggregation, conditional criteria in linked entity, etc.
2. FetchXML is more readable and more maintainable, it's much easier to add one or more extra attributes to be returned, or in a different scenario that you may want to add one or more new query criteria.
3. FetchXML is more intuitive, and it could make your code refactoring effort a lot easier when you need to do so.

But FetchXML does come with a caveat, you need to deal with the XML string that MSCRM server responds back to you. There are a number of CRM SDK documents telling you how to perform a FetchXML query, but usually they all stopped at the point that you get the result XML back from CRM server. You don't actually have any clue about the next step, which is how you should process and manipulate the result in XML format in an efficient and elegant fashion.

In this blog post, I am going to show you a short snippet code that tells how you can properly do this, using the two XElement extension methods that I shared in my previous blog post.

Suppose that you have a requirement to get all CRM accounts in Washington state (WA), so you came up with the following FetchXML query:

<fetch mapping='logical' count='10'>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='numberofemployees' />
      <attribute name='owninguser' />
      <attribute name='customertypecode' />
      <attribute name='donotemail' />
      <attribute name='createdon' />
      <filter>
         <condition attribute='address1_stateorprovince' operator='eq' value='WA' />
      </filter>
   </entity>
</fetch>

And CRM may return a result like the following one:

<resultset morerecords="0" paging-cookie="&lt;cookie page=&quot;1&quot;&gt;&lt;accountid last=&quot;{4BC7A5F8-AD02-DE11-83DE-0003FFE51F61}&quot; first=&quot;{25C7A5F8-AD02-DE11-83DE-0003FFE51F61}&quot; /&gt;&lt;/cookie&gt;">
   <result>
      <name>AB Company</name>
      <numberofemployees formattedvalue="95">95</numberofemployees>
      <customertypecode name="Customer" formattedvalue="3">3</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{25C7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Baldwin Museum of Science</name>
      <numberofemployees formattedvalue="250">250</numberofemployees>
      <customertypecode name="Customer" formattedvalue="3">3</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{3AC7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Blue Yonder Airlines</name>
      <numberofemployees formattedvalue="150">150</numberofemployees>
      <customertypecode name="Other" formattedvalue="12">12</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{45C7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Brown Company</name>
      <numberofemployees formattedvalue="39">39</numberofemployees>
      <customertypecode name="Prospect" formattedvalue="8">8</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{4AC7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
   <result>
      <name>Budget Company</name>
      <numberofemployees formattedvalue="32">32</numberofemployees>
      <customertypecode name="Reseller" formattedvalue="9">9</customertypecode>
      <donotemail name="Allow">0</donotemail>
      <createdon date="2/22/2008" time="4:00 PM">2008-02-22T16:00:00-08:00</createdon>
      <accountid>{4BC7A5F8-AD02-DE11-83DE-0003FFE51F61}</accountid>
   </result>
</resultset>

Your job, as a CRM developer, is to process the result in an Object-Oriented fashion after you have got the result from CRM server. Suppose you are a fan of LINQ query as well, you may want to do it in an elegant way by utilizing the power of LINQ query.

To help you achieve this goal, you may include the two extension methods that I just mentioned in one of your static utility classes, and then start to write your FetchXML query code. The following is a short snippet code that could be used as your start point. You may be able to tell that what I am showing you here is a snippet code from an NUnit test.

string fetchXml = @"
<fetch mapping='logical' count='10'>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='numberofemployees' />
      <attribute name='owninguser' />
      <attribute name='customertypecode' />
      <attribute name='donotemail' />
      <attribute name='createdon' />
      <filter>
         <condition attribute='address1_stateorprovince' operator='eq' value='WA' />
      </filter>
   </entity>
</fetch>
";

string fetchResult = crmService.Fetch(fetchXml);
XDocument xml = XDocument.Parse(fetchResult);

var accounts = from item in xml.Root.Elements()
               select new
               {
                   AccountId = item.ParseValueAs<Guid>("accountid"),
                   Name = item.ParseValueAs<string>("name"),
                   NumberOfEmployees = item.ParseValueAs<int?>("numberofemployees"),
                   DonotEmail = item.ParseValueAs<bool?>("donotemail"),
                   CustomerTypeCode = item.ParseValueAs<int?>("customertypecode"),
                   CustomerType = item.ParseValueAs<string>("customertypecode", "name"),
                   CreatedOn = item.ParseValueAs<DateTime?>("createdon"),
               };

var createdOn = new DateTime(2008, 2, 22, 16, 0, 0);

// Get the first account returned
var firstAccount = accounts.ElementAt(0);

// Assertions for the first account returned
Assert.AreEqual(new Guid("{25C7A5F8-AD02-DE11-83DE-0003FFE51F61}"), firstAccount.AccountId);
Assert.AreEqual("AB Company", firstAccount.Name);
Assert.AreEqual(95, firstAccount.NumberOfEmployees);
Assert.AreEqual(false, firstAccount.DonotEmail);
Assert.AreEqual(3, firstAccount.CustomerTypeCode);
Assert.AreEqual("Customer", firstAccount.CustomerType);
Assert.AreEqual(createdOn, firstAccount.CreatedOn);

As you may see, I have tried to parse customertypecode (a picklist field in account entity) in two different ways to serve different purpose. First I tried to get the integer value of the picklist field, then I tried to get the field's value attribute which is what the integer value represents for. The second value could be more meaningful to the end user of your application.

As I have mentioned in my previous blog post, you should use basic .NET CLR types as the target types when you try to parse FetchXML result.

All I have shown here is not actually something of rocket science, but hopefully it has given you enough idea how to properly parse a CRM FetchXml query result string in an easy way.

Cheers, and Happy New Year to everyone!