Page large result sets with FetchXML

Applies to Dynamics 365 for Customer Engagement apps version 9.x

You can page the results of a FetchXML query by using the paging cookie. The paging cookie is a performance feature that makes paging in the application faster for very large datasets. When you query for a set of records, the result will contain a value for the paging cookie. For better performance, you can pass that value when you retrieve the next set of records.

FetchXML and QueryExpression use different formats for their paging cookies. If you convert from one query format to the other by using the FetchXmlToQueryExpressionRequest message or the QueryExpressionToFetchXmlRequest message, the paging cookie value is ignored. In addition, if you request nonconsecutive pages, the paging cookie value is ignored.

When you use the paging cookie with FetchXML, make sure that you use the correct encoding. The following example shows the correct encoding when using the paging cookie with FetchXML:

strQueryXML = @"  
<fetch mapping='logical' paging-cookie='&lt;cookie page=&quot;1&quot;&gt;&lt;accountid last=&quot;{E062B974-7F8D-DC11-9048-0003FF27AC3B}&quot; first=&quot;{60B934EF-798D-DC11-9048-0003FF27AC3B}&quot;/&gt;&lt;/cookie&gt;' page='2' count='2'>  
 <entity name='account'>  
  <all-attributes/>  
 </entity>  
</fetch>";  

The following example shows how to use the paging cookie with a FetchXML query. For the complete sample code, see Sample: Use FetchXML with a Paging Cookie.



// Define the fetch attributes.
// Set the number of records per page to retrieve.
int fetchCount = 3;
// Initialize the page number.
int pageNumber = 1;
// Initialize the number of records.
int recordCount = 0;
// Specify the current paging cookie. For retrieving the first page, 
// pagingCookie should be null.
string pagingCookie = null;

// Create the FetchXml string for retrieving all child accounts to a parent account.
// This fetch query is using 1 placeholder to specify the parent account id 
// for filtering out required accounts. Filter query is optional.
// Fetch query also includes optional order criteria that, in this case, is used 
// to order the results in ascending order on the name data column.
string fetchXml = string.Format(@"<fetch version='1.0' 
                                mapping='logical' 
                                output-format='xml-platform'>
                                <entity name='account'>
                                    <attribute name='name' />
                                    <attribute name='emailaddress1' />
                                    <order attribute='name' descending='false'/>
                                    <filter type='and'>
                            <condition attribute='parentaccountid' 
                                            operator='eq' value='{0}' uiname='' uitype='' />
                                    </filter>
                                </entity>
                            </fetch>",
                                _parentAccountId);

Console.WriteLine("Retrieving data in pages\n"); 
Console.WriteLine("#\tAccount Name\t\t\tEmail Address");

while (true)
{
    // Build fetchXml string with the placeholders.
    string xml = CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount);

    // Excute the fetch query and get the xml result.
    RetrieveMultipleRequest fetchRequest1 = new RetrieveMultipleRequest
    {
        Query = new FetchExpression(xml)
    };

    EntityCollection returnCollection = ((RetrieveMultipleResponse)_service.Execute(fetchRequest1)).EntityCollection;
    
    foreach (var c in returnCollection.Entities)
    {
        System.Console.WriteLine("{0}.\t{1}\t\t{2}", ++recordCount, c.Attributes["name"], c.Attributes["emailaddress1"] );
    }                        
    
    // Check for morerecords, if it returns 1.
    if (returnCollection.MoreRecords)
    {
        Console.WriteLine("\n****************\nPage number {0}\n****************", pageNumber);
        Console.WriteLine("#\tAccount Name\t\t\tEmail Address");
        
        // Increment the page number to retrieve the next page.
        pageNumber++;

        // Set the paging cookie to the paging cookie returned from current results.                            
        pagingCookie = returnCollection.PagingCookie;
    }
    else
    {
        // If no more records in the result nodes, exit the loop.
        break;
    }
}

When not to use paging cookies

Paging cookies depend on the common case where each row returned represents a unique entity record. There are some queries you can construct using link-entity that will provide rows that combine data from the primary entity with related entities. This will result in multiple primary entity rows that refer to the same primary key value. If you depend on paging cookies in this situation you will get inconsistent results.

In this case, here are several strategies you can apply.

Write your FetchXml Query so that the primary entity ids are unique

If your query includes only one link-entity and doesn't include many-to-many relationships, you can usually make the related entity the primary entity in your query to resolve this.

Rather than include the paging cookie in your FetchXml, simply update the page value. This will work but there will be some performance impact.

Split your query into multiple queries

You can compose multiple queries which you then join the results together in your code.

See also

Sample: Use FetchXML with a Paging Cookie
Building Queries with FetchXML
Fiscal date query operators in FetchXML
Using FetchXML
Page large result sets with QueryExpression