Dynamics CRM 2011 - Querying Data using FetchXML

Roshan Mehta, 16 April 2012

I have previously taken you through various querying techniques through the Microsoft Dynamics CRM 2011 SDK such as the QueryExpression and QueryByAttribute classes. Today we will take a look at querying data using fetchXML queries and the FetchExpression class. FetchXML lets you define complex queries with multiple linked entities and support for aggregates and groupings.

Like QueryExpression and QueryByAttribute, fetchXML queries are executed using the RetrieveMultiple method. FetchXML has support for the sum, max, min, and count aggregates as well as other functions to perform calculations inside your query.

If you are not familiar with the FetchXML syntax, you can easily construct FetchXML queries using the Advanced Find functionality within the Microsoft Dynamics CRM 2011 user interface.

Example 1 – A simple query to retrieve all active Accounts based in Auckland city

Firstly, we will build our query using Advanced Find.

Click on Download Fetch XML to view the FetchXML query. We will need to copy this into our C# code to build our query.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="address1_city" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="address1_city" operator="eq" value="Auckland" />
    </filter> 
    <link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias="accountprimarycontactidcontactcontactid">
      <attribute name="emailaddress1" />
    </link-entity> 
  </entity>
</fetch>

Notice how the XML is quite complex; this is because it includes the default columns built into the Advanced Find View of the entity we are working with. For our example, we are only interested in returning the name of the matching Accounts, so we can erase the attributes we don’t need.

Example 2- Find all Contacts with a Job Title of “Sales Manager” where the parent account is based in Auckland. We want to return the Full Name, Email Address, and name of the parent Account.

Again we can build this using Advanced Find. I have used the Edit Columns button to add the required columns into the view.

Download the Fetch XML query and include it in our C# code.

Notice the <filter> tag is used to add a logical “AND” filter to our query. We have also got a <link-entity> tag which joins the Contact to the related parent Account. It allows us to query the “address1_city” attribute on the Account and also return the “Name” field as part of our column set.

In my next post, I will show you how to write queries using the LINQ capabilities available in Microsoft Dynamics CRM 2011.