Handling RetrieveMultiple Paging

When you use the RetrieveMultiple method to query data from D365 CE, it’s easy to take the results and assume that’s everything, especially when you’re working with small test data sets.

However, to stop a rogue query killing the server while it retrieves millions of records, data is retrieved in pages, by default pulling back 5,000 records at a time. I think it’s because this is quite a lot of records for a simple dev system that it’s easy to overlook the requirement to move onto subsequent pages.

Part of each request identifies which page number you want to retrieve, and how many records to retrieve at a time, as in the following example:

var qry = new QueryExpression("account")
{
  PageInfo = new PagingInfo
  {
    PageNumber = 1,
    Count = 500
  }
};

The first page is number 1, not zero. 🤓 It’s also worth noting that the maximum page size is 5,000 records. You can ask for bigger pages and you won’t get an error, but you will only get 5,000 records back regardless.

If you need to go straight to a specific page you can just specify a higher page number, but if you’re moving from one page to the next it’s more efficient if you also use the PagingCookie option:

while (true)
{
  var results = org.RetrieveMultiple(qry);

  // TODO: Do something with the results
  
  if (!results.MoreRecords)
    break;

  qry.PageInfo.PageNumber++;
  qry.PageInfo.PagingCookie = results.PagingCookie;
}

I’ve always just taken it as gospel from the documentation that using the PagingCookie is The Right Way™, but what difference does it actually make?

To check, I ran a quick test application to retrieve every email record in a test system (275,000 in total). With the paging cookie took 1:31, and without took 4:29, so definitely worthwhile doing one extra line of code for a 3x speed improvement!

If you’re displaying data to a user then you probably want to retrieve a single page and give the user the options to move through them using code such as this. Most of the code I write however interacts with data as a background operation and will always need to process all the records matched by a query, so instead of using RetrieveMultiple directly and having an outer loop to move between pages and an inner loop to process the records in a single page, I use a simple extension method to make things easier:

var qry = new QueryExpression("account");

// TODO: Set up the criteria etc. of the query

foreach (var account in org.RetrieveAll(qry))
{
  // TODO: Do something with the results
}

This makes it much clearer what the code is actually doing and hides the details of how the paging is happening in the background.

The RetrieveAll method is implemented as follows:

static class Extensions
{
  public static IEnumerable<Entity> RetrieveAll(this IOrganizationService org, QueryExpression qry)
  {
    // Set up paging
    qry.PageInfo = new PagingInfo
    {
      PageNumber = 1,
      Count = 500
    };

    while (true)
    {
      var results = org.RetrieveMultiple(qry);

      foreach (var entity in results.Entities)
        yield return entity;

      if (!results.MoreRecords)
        break;

      qry.PageInfo.PageNumber++;
      qry.PageInfo.PagingCookie = results.PagingCookie;
    }
  }
}

Hope this helps you query your D365 CE data a little bit easier!