FetchXML Query In Microsoft Dynamics 365

FetchXML

To execute a FetchXML query in Microsoft Dynamics 365 and Microsoft Dynamics 365 (online), it is necessary to first build the XML query string. After creating the query string, use the IOrganizationService.RetrieveMultiple method to execute the query string. The privileges of the logged-in user affects the set of records returned. Only records for which the logged-in user has read access will be returned.

The FetchXML query string must conform to the schema definition for the FetchXML language.

Accessing records from entities in CRM (Online)

Prerequisites

1. Download XRMToolBOx setup.
2. Extract the contents and run XrmToolBox.exe.
   
   
    
3. Connect to your organization by selecting a new connection and using login credentials of your organization.
   
   
   
   
   
   
    
4. After successfully establishing the connection, the below console screen appears. Here you can enter your XML query and get a response.
   
   

Sample XML request query

Sample response

It is also possible to set conditional filtering on the response. Below is an example of fetch in which 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> 

Aggregate functions can also be used on the records. In the below 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>

Association of two distinct tables is also possible using fetch query. Below 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 in C# code,

// 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 user executes a FetchXML query by using the RetrieveMultiple method, the return value is an EntityCollection that contains the results of the query. It is possible to iterate through the entity collection.