Use FetchXML to construct a query

FetchXML is a proprietary XML based query language of Microsoft Dataverse used to query data using either the Web API or the SDK for .NET. 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 Table and as an organization-owned saved view in the SavedQuery Table.

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 table 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 columns 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 columns 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>  

Important

A FetchXML query has a limit of a maximum of 15 allowed link tables.

Execute the FetchXML query

You can execute a FetchXML query by using either the Web API or the SDK for .NET.

Using Web API

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

Using SDK for .NET

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"]);
}  

Important

When working with code such as the above example, always make sure that your string is a valid encoded XML string. This is especially true regarding the possibility of containing special characters in condition values such as &<> being encoded as &amp;&lt;&gt;.

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, the return value is an EntityCollection that contains the results of the query. You can then iterate through the table collection. The previous example uses the foreach loop to iterate through the result collection of the FetchXML query.

Use Wildcard characters in conditions using string values

You can use wildcard characters when you construct queries using conditions on string values. More information: Use wildcard characters in conditions for string values