Dynamics 365 – Retrieve More than 5000 records with FetchXML

When you’re working with Dynamics 365, you may have encountered a limitation when it comes to retrieving records. The maximum number of records that can be retrieved in a single query is 5.000. This can be a problem if you need to retrieve a large number of records, such as for a data migration or integration project.

In one of my previous posts, I have demonstrated to do with QueryExpression. To see that post click here. This post will be about retrieveing more than 5000 records by using FetchXML.

Retrieving more than 5000 records using FetchXML in Dynamics 365 involves using the “paging cookie” and the page option in the FetchXML query, which allows you to retrieve a specific set of records and then move to the next set of records until all records have been retrieved.

Here’s an example of how to use the paging cookie option in a FetchXML query:

private static List<Entity> GetAccounts()
{
string fetchXml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
<entity name='account'>
<attribute name='accountnumber' />
<attribute name='accountid' />
<attribute name='modifiedon' />
<order attribute='modifiedon' descending='true' />
<order attribute='name' descending='false' />
</entity>
</fetch>";
return orgService.RetrieveAll(new FetchExpression(fetchXml));
}
public static List<Entity> RetrieveAll(this IOrganizationService service, FetchExpression query)
{
var conversionRequest = new FetchXmlToQueryExpressionRequest
{
FetchXml = query.Query
};
var conversionResponse =
(FetchXmlToQueryExpressionResponse)service.Execute(conversionRequest);
return RetrieveAll(service, conversionResponse.Query);
}
public static List<Entity> RetrieveAll(this IOrganizationService service, QueryExpression query)
{
var result = new List<Entity>();
var entities = service.RetrieveMultiple(query);
result.AddRange(entities.Entities);
var page = 2;
while (entities.MoreRecords)
{
query.PageInfo = new PagingInfo
{
PagingCookie = entities.PagingCookie,
PageNumber = page
};
entities = service.RetrieveMultiple(query);
result.AddRange(entities.Entities);
page++;
}
return result;
}
view raw FetchXML.cs hosted with ❤ by GitHub

In this example, the count attribute is not used so the default record count per page will be 5.000, which is the maximum number of records that can be retrieved in a single query. The PagingCookie attribute contains a cookie that specifies the page number (in this case, page 2) and the first and last record IDs in the set.

To retrieve the next set of records, you would simply increment the page number in the paging cookie and run the query again. This process can be repeated until all records have been retrieved.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s