How can I retrieve all records (more than 5000 and/or more than 1 page) with the Dynamics 365 Web API in a Dynamics CRM organization with a C# plugin?

Question

I need to count all of the Task records with a regardingobjectid of a specific Account and a statecode of 0. This needs to be done with the new 365 Web API and REST that came with Microsoft Dynamics CRM 2016. Here is how the basic query (without paging) is done with Javascript:

var req = new XMLHttpRequest();
req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/tasks?$filter=_regardingobjectid_value eq 00000000-0000-0000-0000-000000000000 and  statecode eq 0", true);
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.setRequestHeader("Prefer", "odata.include-annotations=\"*\",odata.maxpagesize=5000");
req.onreadystatechange = function() {
    if (this.readyState === 4) {
        req.onreadystatechange = null;
        if (this.status === 200) {
            var results = JSON.parse(this.response);
            for (var i = 0; i < results.value.length; i++) {
                var activityid = results.value[i]["activityid"];
            }
        } else {
            Xrm.Utility.alertDialog(this.statusText);
        }
    }
};
req.send();

Furthermore, this must be done in a fashion that allows it to retrieve more than 5000 records. Information on how to do this can be found here: https://msdn.microsoft.com/en-us/library/gg334767.aspx

Here is how I could query up to 5000 records with a C# plugin and FETCH XML:

int count = 0;
            string fetchTemplate =
                @"<fetch version='1.0' output-format='xml - platform' mapping='logical' distinct='false'>
                    <entity name='activitypointer'>
                        <attribute name='activitytypecode' />
                        <attribute name='subject' />
                        <attribute name='prioritycode' />
                        <attribute name='regardingobjectid' />
                        <attribute name='statecode' />
                        <attribute name='instancetypecode' />
                        <attribute name='community' />
                        <attribute name='scheduledend' />
                        <attribute name='activityid' />
                        <filter type='and'>
                            <condition attribute='isregularactivity' operator='eq' value='1' />
                            <condition attribute='statecode' operator='eq' value='0' />
                        </filter>    
                        <link-entity name='account' from='accountid' to='regardingobjectid' alias='ab'>
                            <filter type='and'>
                                <condition attribute='accountid' operator='eq' uitype='account' value='{0}' />
                            </filter>
                        </link-entity>
                    </entity>
                </fetch>";
            fetchTemplate = String.Format(fetchTemplate, entityId.ToString());
            List<Entity> records = context.RetrieveAll(fetchTemplate);
            if (records != null && records.Count > 0)
            {
                count = records.Count;
            }

Any help on this would be greatly appreciated. I am using Microsoft Dynamics CRM Online 2016, Visual Studio Professional 2015, relevant Nuget packages, and the Plugin Registration Tool.

I have come to StackOverflow for help with this because I cannot find a simple example of a REST query online for Microsoft Dynamics CRM that can retrieve more than 5000 records in a C# plugin.

I am particularly interested in the @odata.nextlink property and how this enables the restful retrieval of multiple pages (and potentially more than 5000 records).

If you would like to help me enhance my current C# plugin code so that it can retrieve more than 5000 records, that would also be appreciated.

Answer 1

You don't need to retrieve all the records to perform a count. FetchXML includes aggregations that allow us to calculate max, min, average and COUNT. The following is an example to count all the accounts in the system:

<fetch version="1.0" mapping="logical" aggregate="true">
  <entity name="account">
    <attribute name="accountid" aggregate="count" alias="count" />
  </entity>
</fetch> 

The WebAPI have a $count query option, but unfortunately, it's also limited by the same restriction so it's not helpful in this case:

The count value does not represent the total number of entities in the system. It is limited by the maximum number of entities that can be returned.

So, after all this introduction, how we calculate the number of records using the WebAPI and avoiding the 5000 records restriction then? Passing our FetchXML as a query in the GET to the WebAPI, this is how to use it. After encoding the previous FetchXML example, we ended up with the following request:

GET/api/data/v8.2/accounts?fetchXml=%3Cfetch+version%3D%221.0%22+mapping%3D%22logical%22+aggregate%3D%22true%22%3E%3Centity+name%3D%22account%22%3E%3Cattribute+name%3D%22accountid%22+aggregate%3D%22count%22+alias%3D%22count%22+%2F%3E%3C%2Fentity%3E%3C%2Ffetch%3E+

And this is the response with our count:

If you still want to retrieve all the records, here you can find an example of how to use the paging cookie with the WebAPI.