In this course, we will learn to retrieve +5000 records using the query expression in Microsoft Dynamics 365 CRM. Before we start, make sure to subscribe to CRM Crate so that you stay up-to-date in the field of Microsoft Dynamics 365 CRM.

Problem with IOrganizationService.RetrieveMultiple method

The IOrganizationService.RetrieveMultiple method is generally preferred to retrieve data from the Dynamics CRM in form of entity collection. But, to ensure best performance, each query request can return a maximum of 5000 rows. Therefore, only utilizing the stand alone IOrganizationService.RetrieveMultiple method is not a preferred solution for the use-case where more than 5000 records are have to be retrieved from the Dynamics CRM.

Custom logic to retrieve +5000 records using a query expression

Here is a custom logic to retrieve more than 5000 CRM records from a Query Expression.

• Utilize the below method by passing the Query Expression and CRM Service and obtain the bulk records (+5000) within the entity collection object.

        //Method Used For Retrieving Bulk CRM Records.
        public static EntityCollection RetrieveAllRecords(QueryExpression query, IOrganizationService _service, int count = 5000)
        {
            query.PageInfo = new PagingInfo();
            query.PageInfo.Count = count;
            query.PageInfo.PageNumber = 1;
            query.PageInfo.ReturnTotalRecordCount = true;


            EntityCollection entityCollection = _service.RetrieveMultiple(query);
            EntityCollection ecFinal = new EntityCollection();
            foreach (Entity i in entityCollection.Entities)
            {
                ecFinal.Entities.Add(i);
            }
            do
            {
                query.PageInfo.PageNumber += 1;
                query.PageInfo.PagingCookie = entityCollection.PagingCookie;
                entityCollection = _service.RetrieveMultiple(query);
                foreach (Entity i in entityCollection.Entities)
                    ecFinal.Entities.Add(i);
            }
            while (entityCollection.MoreRecords);
            return ecFinal;
        }
    }

• Consider below console application code driven on the business scenario where we are have to retrieve 100000 records from the Account entity.

using System;
using Microsoft.Xrm.Tooling.Connector;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.Diagnostics;

namespace CRM_Crate_Console_Application
{
    class Execution
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Console Application Started!");
            try
            {

                //Step 1 - Retrieving CRM Essential Information.
                string sEnvironment = System.Configuration.ConfigurationManager.AppSettings["Environment"].ToString();
                string sUserKey = System.Configuration.ConfigurationManager.AppSettings["UserKey"].ToString();
                string sUserPassword = System.Configuration.ConfigurationManager.AppSettings["UserPassword"].ToString();


                //Step 2- Creating A Connection String.
                string conn = $@" Url = {sEnvironment};AuthType = OAuth;UserName = {sUserKey}; Password = {sUserPassword};AppId = 51f81489-12ee-4a9e-aaae-a2591f45987d;RedirectUri = app://58145B91-0C36-4500-8554-080854F2AC97;LoginPrompt=Auto; RequireNewInstance = True";

                Console.WriteLine("Operating Environment : " + sEnvironment);

                //Step 3 - Obtaining CRM Service.
                using (var service = new CrmServiceClient(conn))
                {

                    if (service != null)
                    {
                        //Step 4 - Creating A Query Expression On Account Entity.
                        var queryExpression = new QueryExpression("account");
                        queryExpression.ColumnSet.AddColumns("accountid", "accountname", "createdon", "ownerid", "modifiedon", "modifiedby", "createdby");
                        queryExpression.AddOrder("createdon", OrderType.Ascending);

                        //Step 5- Call The Custom Method To Retrieve +5000 records.
                        EntityCollection entityCollection = RetrieveAllRecords(queryExpression, service);
                        if (entityCollection.Entities.Count == 0)
                        {
                            Console.WriteLine("Warning: No Record Found In : " + sEnvironment);
                        }
                        else
                        {
                            Console.WriteLine(" Found In : " + sEnvironment + " With Count : " + entityCollection.Entities.Count);
                            foreach (var entity in entityCollection.Entities)
                            {
                                try
                                {
                                    //Perform The CRM Operations As Per Your Need.
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine("Error Occured : " + ex.Message);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error Occured : " + ex.Message);
            }
        }

        //Method Used For Retrieving Bulk CRM Records.
        public static EntityCollection RetrieveAllRecords(QueryExpression query, IOrganizationService _service, int count = 5000)
        {
            query.PageInfo = new PagingInfo();
            query.PageInfo.Count = count;
            query.PageInfo.PageNumber = 1;
            query.PageInfo.ReturnTotalRecordCount = true;


            EntityCollection entityCollection = _service.RetrieveMultiple(query);
            EntityCollection ecFinal = new EntityCollection();
            foreach (Entity i in entityCollection.Entities)
            {
                ecFinal.Entities.Add(i);
            }
            do
            {
                query.PageInfo.PageNumber += 1;
                query.PageInfo.PagingCookie = entityCollection.PagingCookie;
                entityCollection = _service.RetrieveMultiple(query);
                foreach (Entity i in entityCollection.Entities)
                    ecFinal.Entities.Add(i);
            }
            while (entityCollection.MoreRecords);
            return ecFinal;
        }
    }
}

Code Logic –

A. Step 1 = Retrieve the three essential parameters (URL, User ID and Password) from the App.Config.

B. Step 2 = Create a string connection made up of CRM URL, User ID, Password, App ID and Redirect URI.

C. Step 3 = Utilize the class “CrmServiceClient” for obtaining the CRM service. Pass the above created connection string to CrmServiceClient and recieve the CRM Service in the return.

D. Step 4 = Create a query expression on Account entity.

E. Step 5 = Utilize our custom method to return more than 5000 records within the entity collection.

Thus, we learned to retrieve the more than 5000 records from Microsoft Dyanmics 365 CRM.