Use FetchXML to construct a query

FetchXML is a proprietary query language that is used in Common Data Service (CDS) for Apps. It’s based on a schema that describes the capabilities of the language. The FetchXML language supports similar query capabilities as query expressions. In addition, it’s used as a serialized form of query, used to save a query as a user-owned saved view in the UserQuery Entity and as an organization-owned saved view in the SavedQuery Entity.

A FetchXML query can be executed by using either the Web API or the Organization service.

Create the FetchXML query string

To execute a FetchXML query, you must first build the XML query string. After you create the query string, use the IOrganizationService.RetrieveMultiple method to execute the query string. The privileges of the logged on user affects the set of records returned. Only records for which the logged on user has read access will be returned.

The FetchXML query string must conform to the schema definition for the FetchXML language. For more information, see Fetch XML Schema.

You can save a query by creating a SavedQuery record. Set visible on the link-entity node to false to hide the linked entity in the Advanced Find user interface. It will still participate in the execution of the query and will return the appropriate results.

Warning

Don’t retrieve all attributes in a query because of the adverse effect on performance. This is particularly true if the query is used as a parameter to an update request. In an update, if all attributes are included this sets all field values, even if they are unchanged, and often triggers cascaded updates to child records.

Example FetchXML query strings

In the following example, the FetchXML statement retrieves all accounts:

  
<fetch mapping='logical'>   
   <entity name='account'>  
      <attribute name='accountid'/>   
      <attribute name='name'/>   
</entity>  
</fetch>  
  

In the following example, the FetchXML statement retrieves all accounts where the last name of the owning user is not equal to Cannon:

  
<fetch mapping='logical'>  
   <entity name='account'>   
      <attribute name='accountid'/>   
      <attribute name='name'/>   
      <link-entity name='systemuser' to='owninguser'>   
         <filter type='and'>   
            <condition attribute='lastname' operator='ne' value='Cannon' />   
          </filter>   
      </link-entity>   
   </entity>   
</fetch>  
  

In the following example, the FetchXML statement uses count to set the maximum number of records returned from the query. In this case first 3 accounts are returned from the query,

<fetch mapping='logical' count='3'>  
  <entity name='account'>  
   <attribute name='name' alias='name'/>  
  </entity></fetch>  

This example shows an inner join between EntityMap and AttributeMap where the EntityMapID matches.

<fetch version='1.0' mapping='logical' distinct='false'>  
   <entity name='entitymap'>  
      <attribute name='sourceentityname'/>  
      <attribute name='targetentityname'/>  
      <link-entity name='attributemap' alias='attributemap' to='entitymapid' from='entitymapid' link-type='inner'>  
         <attribute name='sourceattributename'/>  
         <attribute name='targetattributename'/>  
      </link-entity>  
   </entity>  
 </fetch>  

Execute the FetchXML query

You can execute a FetchXML query by using either the Web API or the Organization service.

Using Web API

You can pass a URL encoded FetchXml string to the appropriate entityset using the fetchXml query string parameter. More information: Use custom FetchXML.

Using Organization service

Use the IOrganizationService.RetrieveMultiple method passing an FetchExpression where the Query property contains the FetchXml query.

The following code shows how to execute a FetchXML query using the Organizations Service:

  
// Retrieve all accounts owned by the user with read access rights to the accounts and   
// where the last name of the user is not Cannon.   
string fetch2 = @"  
   <fetch mapping='logical'>  
     <entity name='account'>   
        <attribute name='accountid'/>   
        <attribute name='name'/>   
        <link-entity name='systemuser' to='owninguser'>   
           <filter type='and'>   
              <condition attribute='lastname' operator='ne' value='Cannon' />   
           </filter>   
        </link-entity>   
     </entity>   
   </fetch> ";   
  
EntityCollection result = _serviceProxy.RetrieveMultiple(new FetchExpression(fetch2));
foreach (var c in result.Entities)
{
   System.Console.WriteLine(c.Attributes["name"]);
}  

Note

You can convert a FetchXML query to a query expression with the FetchXmlToQueryExpressionRequest message.

FetchXML query results

When you execute a FetchXML query by using the OrganizationServiceProxy.RetrieveMultiple(QueryBase) method authenticates the user. method, the return value is an EntityCollection that contains the results of the query. You can then iterate through the entity collection. The previous example uses the foreach loop to iterate through the result collection of the FetchXML query.