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!

From LINQ to XML to LINQ to Objects: Two Generic XElement Extension Methods

LINQ is one of the most amazing programming language innovations in the few recent years. Coming with .NET framework 3.5, there are a whole new stack of XML classes that help you manipulate XML document in your .NET program using LINQ approach.

In real-world programming practice, we often have the need to read from an XML file and convert to an enumerable list of strong-typed objects which you start to work with in your .NET application. But I have never come across a complete sample code that shows me how to do this properly in a simple way, so I came up with the following two extension methods that might help you if you need to do the same thing in your life.

/// <summary>
/// Convert the value of an XElement's child element to the specified generic type.
/// </summary>
/// <typeparam name="T">The target type to be used to convert the XML element value</typeparam>
/// <param name="xe">The XML element that contains child elements</param>
/// <param name="childNode">The child element that you want to parse its element value</param>
/// <returns>Converted value of type T</returns>
public static T ParseValueAs<T>(this XElement xe, XName childNode)
{
    return ParseValueAs<T>(xe, childNode, null);
}

/// <summary>
/// Convert the value of an XElement's child element (or one of its attributes) to the specified generic type.
/// </summary>
/// <typeparam name="T">The target type to be used to convert the XML element or attribute value</typeparam>
/// <param name="xe">The XML element that contains child elements</param>
/// <param name="childNode">The child element that you want to parse its element or attribute value</param>
/// <param name="attribute">If provided, the attribute value will be parsed. Otherwise, the element value will be parsed</param>
/// <returns>Converted value of type T</returns>
public static T ParseValueAs<T>(this XElement xe, XName childNode, XName attribute)
{
    if (xe == null)
        return default(T);

    XElement childElement = xe.Element(childNode);
    if (childElement == null)
        return default(T);

    bool valueIsEmpty = attribute == null
                            ? string.IsNullOrEmpty(childElement.Value)
                            : childElement.Attribute(attribute) == null || string.IsNullOrEmpty(childElement.Attribute(attribute).Value);

    if (valueIsEmpty)
        return default(T);

    string value = (attribute == null) ? childElement.Value : childElement.Attribute(attribute).Value;

    Type type = typeof(T);

    if (type == typeof(bool) || type == typeof(bool?))
    {
        switch (value.ToLower())
        {
            // You may tweak the following options a bit based on your needs
            case "1":
            case "true":
            case "yes":
                value = "true";
                break;

            case "0":
            case "false":
            case "no":
                value = "false";
                break;

            default:
                return default(T);
        };
    }

    TypeConverter converter = TypeDescriptor.GetConverter(type);
    return (T)converter.ConvertFromString(value);
}

Given that you have an XML file, suppose it's called contacts.xml, like this:

<Contacts>
  <Contact>
    <Name>Patrick Hines</Name>
    <Phone>408-555-1234</Phone>
    <BirthDate>1990-01-01</BirthDate>
    <Address>123 Main St, San Jose, CA, 94500</Address>
  </Contact>
  <Contact>
    <Name>Patrick Hines</Name>
    <Phone>510-444-0123</Phone>
    <Address>345 Center Ave, Milpitas, CA, 94439</Address>
  </Contact>
</Contacts>

You can use the following C# snippet to convert the XML to your LINQ objects.

XDocument xml = XDocument.Load("contacts.xml");

var contacts = from item in xml.Root.Elements()
               select new
               {
                   Name = item.ParseValueAs<string>("Name"),
                   Phone = item.ParseValueAs<string>("Phone"),
                   BirthDate = item.ParseValueAs<DateTime?>("BirthDate"),
                   Address = item.ParseValueAs<string>("Address"),
               };

I used an anonymous type for simplicity, but you can always use any defined class that you may already have in your application, so you are working with the objects that you are familiar with.

As usual, here are a few notes before we conclude this blog post:

• You should always try to use the basic CLR types as your target type, such as int, bool, string, DateTime, etc.
• As I have mentioned, I have offered a pair of overloaded methods, they are serving for different purpose. One of them is parsing the XML element's value, another one is parsing an XML element's attribute value. With the overloaded signatures, you have the flexibility to either read the XML element or an XML element's attribute.
• You may want to tweak a little bit about how you want to parse into boolean value depending on your XML data and business requirement.
• I didn't include any exception handling in the code, which you may want to add, if your XML data is unpredictable.
• It's advisable to return nullable type, so when the XML element or attribute doesn't exist, it return null as the value, which makes more sense in most cases.

[Update - Jan 6, 2010] I updated the code so that the extension methods now support XML namespace.

If you are coming Microsoft Dynamics CRM world, I will show you in my next blog post about how to use the above extension methods to properly parse your FetchXML query result. Stay tuned.

Hope this helps, cheers!