Use FetchXML to construct a query

 

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

To execute a FetchXML query in Microsoft Dynamics 365 and Microsoft Dynamics 365 (online), 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 FetchXML schema.

You can save a query by creating a SavedQuery record, as demonstrated in Sample: Validate and execute a saved query. 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 negative 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.

Create the Query String

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 Query

The following code shows how to execute a FetchXML query:

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

Query Results

When you execute a FetchXML query by using the RetrieveMultiple 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.

See Also

Build queries with FetchXML
Use FetchXML aggregation
FetchXML schema

Microsoft Dynamics 365

© 2016 Microsoft. All rights reserved. Copyright