Leveraging FetchXML with Dynamics CRM

Overview:

FetchXML is used to query the Dynamics 365 database. A great advantage of using FetchXML is that it can be easily designed by using Dynamics 365 database and it is easy to understand.

FetchXML tester is available in the XRM Toolbox which can let you test and fetch results quickly. FetchXML also provides facilities like aggregation and grouping of data.

Using FetchXML:

FetchXML must conform to the fetch.xsd schema provided with the Dynamics 365 SDK under the directory SDK\Schemas\fetch.xsd

FetchXML must be built into a query string which can be used with IOrganizationService.RetrieveMultiple to retrieve data from Dynamics 365.

FetchXML query can be saved in Dynamics 365 by creating a SavedQuery record.

From the above sample query, a query can be constructed inside <fetch></fetch> tags. In the above example, I’m retrieving Contact records with the attributes Contact ID which is the Primary Key and the Full Name of the Contact.

In the above query, using count=’3’ will retrieve a maximum of 3 records.

Executing the FetchXML query:

You can simply use the above query in the FetchXML tester provided in the XRM Toolbox and get results or you can construct a query string and use it with IOrganizationService.RetrieveMultiple() and get results in the EntityCollection object in your application.

Using the above query, here’s how I can make it into a query string and retrieve the results in my application.

Then, I can use foreach() to loop through the result returned in the EntityCollection.

FetchXML Aggregation:

The following aggregation functions are provided in FetchXML:

• sum
• avg
• min
• max
• count(*)
• count(attribute name)

Example of max used with aggregation:

In the above example, I’ve used the attribute aggregate=”true” in the FetchXML header and used an aggregate aggregate=”max” with the field Employee which is count of Employees in an Account.

The result returned for the same was:

The above result returned a formatted value of 13000 which is the max for an Account.

Also, notice that the tag is <total></total> since it was the alias I used in the query. Likewise, you can use other aggregates and grouping functions.

Understanding Results of FetchXML:

FetchXML results are returned in the <resultset></resultset> tags as shown in the sample below:

Each record is retrieved under an individual <result></result> tags.

Paging:

At maximum, a FetchXML query can return not more than 5000 records.

If results are more than 5000, morerecords attribute in the resultset will return “1” and will be shown like below:

In order to fetch further records, you’ll need to use PagingCookie. A sample code has been provided in the SDK under SampleCode\CS\GeneralProgramming\Queries\FetchPagingWithCookie.cs to leverage the same.

Also, if you want to return results with even smaller numbers, you can use paging in the FetchXML tested by using the following:

In the above example, I’m choosing to show only 2 records per page and I’m retrieving page no. 2 of all the records.

Hope this was helpful!