How to query paging using Dynamics 365 WebAPI (More than 5000 records)?

Recently I was working on a requirement where I had to build a WinForm application to get the records from multiple entity which looks like a very simple requirement and it was indeed but the problem came when I completed my code using WebAPI method and server-to-server authentication. The problem was the size of records which was more than 5000 and I had no idea how to query paging in this scenario.

Paging using the webAPI is a bit different to using the SOAP endpoint, so I've described the structure of request that should be used and how to navigate to the next page from the response.


* To execute the request for the next batch of records after maxbatchsize of request is reached, simply open a new request to the URI specified in the @odata.nextLink attribute using the same header as the initial request.

Here is the example of HTTP response which you'll get. Please look at the end carefully, we got the next page link in the @odata.nextLink attribute because the response reached the max allowed response per page.


{
   "@odata.context":"https://xxxxxx-in.crm5.dynamics.com/api/data/v9.1/$metadata#incidents(incidentid)",
   "value":[
      {
         "@odata.etag":"W/\"1198233166\"",
         "incidentid":"cb30619d-e137-43fa-8989-000c417d9fb1"
      },
      {
         "@odata.etag":"W/\"1145825546\"",
         "incidentid":"14a22584-00bc-e911-a84f-000d3a802d92"
      },
      {
         "@odata.etag":"W/\"1145825550\"",
         "incidentid":"b6b001a3-00bc-e911-a84f-000d3a802d92"
      },
      {
         "@odata.etag":"W/\"1145825565\"",
         "incidentid":"facde8ba-00bc-e911-a84f-000d3a802d92"
      },
      {
         "@odata.etag":"W/\"1165503192\"",
         "incidentid":"11594a8b-c0c4-e911-a855-000d3a802d92"
      }
   ],
   "@odata.nextLink":"https://xxxxxx-in.crm5.dynamics.com/api/data/v9.1/incidents?$select=incidentid&$filter=createdon%20ge%20'8/11/2019%2012:00:00%20AM'%20and%20createdon%20lt%20'10/12/2019%2012:00:00%20AM'%20and%20statuscode%20ne%20173710009%20and%20statuscode%20ne%206&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253cincidentid%2520last%253d%2522%257bB55B9D13-C7C4-E911-A855-000D3A802D92%257d%2522%2520first%253d%2522%257bCB30619D-E137-43FA-8989-000C417D9FB1%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}


Paging in API Calls:

In this below code, I am showing how to get the count of all cases created between a specifc date period by an especific user (I am counting total guid rather than just using the aggregate for more understanding). The first response will return 5000 records and then so on till the HTTP Response @odata.nextLink value comes null i.e no any further paging request is required.


private async Task<int> GetCount(string sUserId, DateTime From, DateTime To)
        {
            int iRecordCount = 0;
            var authToken = GetToken();
            using (var client = new HttpClient())
            {
                string requestIncident = InstanceUrl + "/incidents" + "?$select=incidentid&$filter=_createdby_value eq '" + sUserId + "' and createdon ge '" + From + "' and createdon  lt '" + To + "' and statuscode ne 173710009 and statuscode ne 6";
                var request = new HttpRequestMessage(HttpMethod.Get, requestIncident);
                client.Timeout = new TimeSpan(020);
                client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", authToken);
                var response = await client.SendAsync(request, HttpCompletionOption.ResponseHeadersRead);
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    var rawResult = JsonConvert.DeserializeObject<JObject>(response.Content.ReadAsStringAsync().Result);
                    var keyValues = JsonConvert.DeserializeObject<SearchData>(response.Content.ReadAsStringAsync().Result);
                    if (keyValues.value.Count > 0)
                        iRecordCount += keyValues.value.Count;
                    string nextPageurl = null;
                    if (rawResult["@odata.nextLink"] != null)
                        nextPageurl = rawResult["@odata.nextLink"].ToString(); //This URI is already encoded.
                    while (nextPageurl != null)
                    {
                        request = new HttpRequestMessage(HttpMethod.Get, nextPageurl);
                        response = await client.SendAsync(request);
                        if (response.StatusCode == HttpStatusCode.OK)
                        {
                            rawResult = JsonConvert.DeserializeObject<JObject>(response.Content.ReadAsStringAsync().Result);
                            keyValues = JsonConvert.DeserializeObject<SearchData>(response.Content.ReadAsStringAsync().Result);
                            if (rawResult["@odata.nextLink"] == null)
                                nextPageurl = null;
                            else
                                nextPageurl = rawResult["@odata.nextLink"].ToString();
                            if (keyValues.value.Count > 0)
                                iRecordCount += keyValues.value.Count;
                        }
                        else
                            nextPageurl = null;
                    }
                }
                return iRecordCount;
            }
        }




public class SearchData
    {
        public List<SearchDataValue> value;
    }

    public class SearchDataValue
    {
        public string incidentid;
    }

public string GetToken()
        {
            var AuthToken = string.Empty;
            string jsonResponse;
            try
            {
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
                using (var client = new HttpClient())
                {
                    var request = new FormUrlEncodedContent(new Dictionary<stringstring>
                    { {"grant_type","client_credentials"},{ "client_id",sClientId},{ "client_secret",sClientSecret},{ "resource",ServerAddress},});
                    request.Headers.Add("X-PresttyPrint""1");
                    var response = client.PostAsync(Token_Endpoint, request).Result;
                    jsonResponse = response.Content.ReadAsStringAsync().Result;
                }
                var values = JsonConvert.DeserializeObject<Dictionary<stringstring>>(jsonResponse);
                AuthToken = values["access_token"];
                return AuthToken;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception caught - " + ex.Message);
            }
            return AuthToken;
        }


To know in details about this Server-to-server authentication read this post: https://www.aayushsingh.in/2019/07/server-to-server-authentication.html

Comments

  1. Please keep sharing such great piece of article so we can get more knowledge about this type of topic. python absolute-value

    ReplyDelete

Post a Comment

Popular posts from this blog

How to show enlarge image when mouse hover on image or link in Asp.Net(c#) using JavaScript

StateCode and StatusCode Values for mostly used entities in Microsoft Dynamics CRM 2013

Search data in Gridview on Textbox Key press event using JQuery in Asp.Net- C#

go to top image