FetchXML versus Query Expression

Microsoft Dynamics CRM 4 provides two main mechanisms for querying data from Microsoft Dynamics CRM, FetchXML and QueryExpression.  In general, it is recommended to use QueryExpression over FetchXML because of its better performance and strongly typed results.  But, FetchXML is handy when you need attributes from multiple entities in a single query.  This blog will discuss each type of query and provide an example of each to return the same result.

QueryExpression is class based and is easy to use, although you can only query one entity type at a time.  Therefore, if you need to get data from a parent entity, such as a contact, and then get data from a child entity, such as a custom product entity, you have to perform two queries.  First you must retrieve the contact to get access to its attributes, then you must retrieve the product using the contact id that links the contact to the product.  With QueryExpression, you can specify the attributes you want returned, or indicate you want all attributes returned, you can specify “and” conditions or ”or” conditions, and you can specify a sort order.  To execute a QueryExpression query:

  • Call the Retrieve or RetrieveMultiple methods on the CrmService web service
  • Receive a BusinessEntityCollection containing the results of the query

With FetchXML you can query multiple entity types at the same time as long as there is a relationship between the types.  You can retrieve attributes from multiple entities in one query in this fashion. To execute a FetchXML query:

  • Create a fetch string specifying the query and pass it to the CrmService web service’s fetch method
  • The fetch string contains XML, which specifies the query criteria

Example of QueryExpression:

      QueryExpression query = new QueryExpression();
      query.EntityName = EntityName.contact.ToString();
      ColumnSet columns = new ColumnSet();
      columns.Attributes =
                         new string[] { “contactid”, “lastname”, “firstname” };

      ConditionExpression whereClause = new ConditionExpression();
      whereClause.AttributeName = “lastname”;
      whereClause.Operator = ConditionOperator.Equal;
      whereClause.Values = new string[] { “Jones” };

      FilterExpression filter = new FilterExpression();
      filter.FilterOperator = LogicalOperator.And;
      filter.Conditions = new ConditionExpression[] { whereClause };

      OrderExpression orderBy = new OrderExpression();
      orderBy.OrderType = OrderType.Descending;
      orderBy.AttributeName = “createdon”;

      query.ColumnSet = columns;
      query.Criteria = filter;
      query.Orders = new OrderExpression[] { orderBy };

BusinessEntityCollection retrieved =
crmService.RetrieveMultiple(query);

Example of FetchXML:

string fetch = @”
   <fetch mapping=””logical””>
       <entity name=””contact””>
            <attribute name=””contactid””/>
            <attribute name=””lastname””/>
            <attribute name=””firstname””/>
            <order attribute=””createdon””/>
                 <filter>
                     <condition attribute=””lastname”” operator=””eq””
                        value=””Jones””/>
                 </filter>
         </entity>
   </fetch>”;
   try
  { 
           string result = service.Fetch(fetch);
   }
   catch(System.Web.Services.Protocols.SoapException se)
   {
            // handle exception
   }

One thought on “FetchXML versus Query Expression

  1. Pingback: FetchXML versus Query Expression « brajeevreddymscrm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s