Retrieve 5000+ Records in Dynamics 365 CE using CRM OData, Fetch XML and C#

If you want to retrieve 5000+ records in Dynamics 365 CE, you need to make use of Paging concept of CRM. By default 5000 records are retrieved in a single OData call in CRM. To get more, you need to make subsequent calls to OData Endpoint by passing a paging cookie in the request.

I will be using a simple C# Console Application to retrieve 5000+ records from Dynamics 365 CE, by making use of OData API Endpoint provided by CRM and passing Fetch XML in OData Call.

Concept:

• CRM OData Endpoints allows us to use Fetch XML to retrieve data from Microsoft Dynamics 365 CE. 
• If your resultset has more than 5000 records, you will be returned with first 5000 records ONLY in one OData call.
• To know if the resultset has more than 5000 records, we make use of the response from OData call. The response is added with a cookie value contained in a key - "@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"
• If you wish to fetch the next set of resultset data, you will have to pass this cookie in the Fetch XML root element in an attribute "paging-cookie"
• Note - The cookie value needs to be decoded, and special characters needs to be replaced with supported versions of the same in order to make a valid OData call, otherwise you will get 500 Internal Server Error, and error response as Invalid XML, or Malformed XML in the Paging Cookie, or An item with the same key has already been added.

Here is the main Program.cs file:

I have made use of two helper classes here:

• CrmOdataHelper.cs

• Contains methods for retrieving Authentication Token for making OData calls, and the actual helper method that makes the HTTP GET call for retrieve records from OData endpoint.

• Helper.cs

• Contains methods for performing Retrieve based on recursion, and another method for decoding the cookie to make a valid call to OData endpoint.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

namespace CrmOdataFetchLargeDataset { public class Program { public int? Count = 0; static void Main(string[] args) { int pageNumber = 1; var oDataHelper = new CrmOdataHelper(); var helper = new Helper(); helper.EnrichDetailsFromCRM(pageNumber, string.Empty, oDataHelper); var count = helper.Count; } } }

Here is the CrmOdataHelper.cs file:

This is kind of a HTTP helper class that is responsible for making HTTP requests and returning the response to the caller.

A key point to note in this class is line # 55 where we are passing "*" in "Prefer" request header. This is pretty important because only by doing this, we get a cookie value in the OData response if the resultset if having 5000+ records.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

namespace CrmOdataFetchLargeDataset { using System; using System.Net.Http; using System.Net.Http.Headers; using Microsoft.IdentityModel.Clients.ActiveDirectory; public class CrmOdataHelper { private readonly string clientId; private readonly string endpoint; private readonly string crmPassword; private readonly string crmUsername; private string _authToken = String.Empty; private string Token { get => GetToken(); set => _authToken = value; } public string CrmUri = string.Empty; public CrmOdataHelper() { this.crmPassword = "Password"; this.crmUsername = "Your-CRM-UserName"; this.endpoint = "https://login.windows.net/common"; this.clientId = "Your-Client-Id"; this.CrmUri = "https://YourCompany.api.crm9.dynamics.com"; } private string GetToken() { if (string.IsNullOrWhiteSpace(_authToken)) { var authContext = new AuthenticationContext(this.endpoint, false); var credentials = new UserCredential(this.crmUsername, this.crmPassword); var tokenResult = authContext.AcquireToken(this.CrmUri, this.clientId, credentials); _authToken = tokenResult.AccessToken; } return _authToken; } public HttpResponseMessage CrmWebApiFormattedGetRequest(string apiRequest) { using (var httpClient = new HttpClient()) { httpClient.BaseAddress = new Uri(this.CrmUri); httpClient.Timeout = new TimeSpan(0, 2, 0); httpClient.DefaultRequestHeaders.Add("OData-MaxVersion", "4.0"); httpClient.DefaultRequestHeaders.Add("OData-Version", "4.0"); httpClient.DefaultRequestHeaders.Add("Prefer", "odata.include-annotations = *"); httpClient.DefaultRequestHeaders.Accept.Add( new MediaTypeWithQualityHeaderValue("application/json")); httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue( "Bearer", this.Token); return httpClient.GetAsync(this.CrmUri + apiRequest).Result; } } public string GetResponseString(HttpResponseMessage responseMessage) { string responseString = null; if (responseMessage.IsSuccessStatusCode) { responseString = responseMessage.Content.ReadAsStringAsync().Result; } return responseString; } } }

And here is the main Helper.cs file:

Here I make use of Recursion to make subsequent calls to CRM based on the check whether the OData response contains a cookie. There is another method that decodes the cookie, and replaces all special characters with supported versions of the same.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

using Newtonsoft.Json; using System.Net.Http; using System.Text; using System.Xml; namespace CrmOdataFetchLargeDataset { public class Helper { public int? Count = 0; public void EnrichDetailsFromCRM(int pageNumber, string pagingCookie, CrmOdataHelper utility) { var fetch = "/api/data/v8.2/opportunity?fetchXml=<fetch distinct='false' mapping='logical' page='{0}' count='5000' {1}> <entity name='opportunity'> <attribute name='estimatedvalue' /> </entity> </fetch>"; var query = string.Empty; if (!string.IsNullOrWhiteSpace(pagingCookie)) { query = string.Format(fetch, pageNumber, "paging-cookie='" + pagingCookie + "'"); } else { query = string.Format(fetch, pageNumber, " "); } HttpResponseMessage httpResopnse = utility.CrmWebApiFormattedGetRequest(query); string response = utility.GetResponseString(httpResopnse); if (response.Contains("@Microsoft.Dynamics.CRM.fetchxmlpagingcookie")) { var myDtoObject = JsonConvert.DeserializeObject<GenericDto<OpportunitiesData>>(response); Count += myDtoObject?.Value.Count; XmlDocument doc = new XmlDocument(); doc.LoadXml(myDtoObject.Cookie); XmlElement root = doc.DocumentElement; string s = root.Attributes["pagingcookie"].Value; pagingCookie = GetDecodedCookie(s); this.EnrichDetailsFromCRM(++pageNumber, pagingCookie, utility); } else { var myDtoObject = JsonConvert.DeserializeObject<MyDtoClass>(response); Count += myDtoObject?.Value.Count; } } public string GetDecodedCookie(string cookie) { StringBuilder b = new StringBuilder(cookie); b.Replace("%253c", "%26lt;"); b.Replace("%2520", " "); b.Replace("%253d", "="); b.Replace("%2522", "%26quot;"); b.Replace("%253e", "%26gt;"); b.Replace("%253c", "%26lt;"); b.Replace("%2520", " "); b.Replace("%257b", "{"); b.Replace("%257d", "}"); b.Replace("%252f", "/"); return b.ToString(); } } }

I have used a Generic DTO class to be used for all deserializations of OData responses. Here is my GenericDto class:

 public class GenericDto<T> where T : class
    {
        /// <summary>
        /// Gets or sets the context.
        /// </summary>
        [JsonProperty("@odata.context")]
        public string Context { get; set; }

        [JsonProperty("@Microsoft.Dynamics.CRM.fetchxmlpagingcookie")]
        public string Cookie { get; set; }

        /// <summary>
        /// Gets or sets the value.
        /// </summary>
        public List<T> Value { get; set; }
    }

And here is the Entity specific class with necessary properties required for Deserialization:

    public class OpportunitiesData
    {
        /// <summary>
        /// Gets or sets the ETAG
        /// </summary>
        [JsonProperty("@odata.etag")]
        public string Etag { get; set; }

        [JsonProperty("estimatedvalue")]
        public string EstimatedValue { get; set; }

        [JsonProperty("opportunityid")]
        public Guid OpportunityId { get; set; }
    }

Hope this is helpful. Happy Coding 😊😊