Page large result sets with QueryExpression

 

Applies To: Dynamics CRM 2013

In Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM Online, you can use the paging cookie feature to make paging in an application faster for large datasets. The feature is available in both FetchXML and QueryExpression queries. When you use the paging cookie feature when querying a set of records, the result contains a value for the paging cookie. To improve system performance, you can then 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.

Using a paging cookie with QueryExpression

The following example shows how to use the paging cookie with a query expression.

//  Query using the paging cookie.
// Define the paging attributes.
// 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;
// Define the condition expression for retrieving records.
ConditionExpression pagecondition = new ConditionExpression();

pagecondition.AttributeName = "address1_stateorprovince";

pagecondition.Operator = ConditionOperator.Equal;
pagecondition.Values.Add("WA");

// Define the order expression to retrieve the records.
OrderExpression order = new OrderExpression();
order.AttributeName = "name";
order.OrderType = OrderType.Ascending;

// Create the query expression and add condition.
QueryExpression pagequery = new QueryExpression();
pagequery.EntityName = "account";
pagequery.Criteria.AddCondition(pagecondition);
pagequery.Orders.Add(order);
pagequery.ColumnSet.AddColumns("name", "address1_stateorprovince", "emailaddress1", "accountid");

// Assign the pageinfo properties to the query expression.
pagequery.PageInfo = new PagingInfo();
pagequery.PageInfo.Count = fetchCount;
pagequery.PageInfo.PageNumber = pageNumber;

// The current paging cookie. When retrieving the first page, 
// pagingCookie should be null.
pagequery.PageInfo.PagingCookie = null;
Console.WriteLine("#\tAccount Name\t\t\tEmail Address");while (true)
{
    // Retrieve the page.
    EntityCollection results = _serviceProxy.RetrieveMultiple(pagequery);
    if (results.Entities != null)
    {
        // Retrieve all records from the result set.
        foreach (Account acct in results.Entities)
        {
            Console.WriteLine("{0}.\t{1}\t\t{2}",
                               ++recordCount,
                               acct.EMailAddress1,
                              acct.Name);
        }
    }
    // Check for more records, if it returns true.
    if (results.MoreRecords)
    {
        // Increment the page number to retrieve the next page.
        pagequery.PageInfo.PageNumber++;
        // Set the paging cookie to the paging cookie returned from current results.
        pagequery.PageInfo.PagingCookie = results.PagingCookie;
    }
    else
    {
        // If no more records are in the result nodes, exit the loop.
        break;
    }
}

See Also

Build queries with QueryExpression
Sample: Retrieve with one-to-many relationship
Use the QueryExpression class