Handling the 5000 limit of FetchXml retrievals

When we retrieve records with FetchXml, most annoying constrain is limit of records it returns which is 5000. Here, we are checking how to overcome this limitation.

Paging solution

If you browse the web, you will find a lot of solutions using paging. Which is pretty cool. I am showing here one of the good codes I tried. This worked for me. For example I am retrieving all the active contacts in my CRM.

internal static void MainFunction(IOrganizationService service)
        {
            string fetchXml = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                        <entity name='contact' >
                                            <attribute name='firstname' />
                                            <attribute name='lastname' />
                                            <filter>
                                                <condition attribute='statecode' operator='eq' value='0' />
                                            </filter>
                                        </entity>
                                       </fetch>");

            List<Entity> contacts = GetTotalRecordsfromFetch(fetchXml, service);
            Console.WriteLine("contacts : " + contacts.Count);
         }
        
        public static List<Entity> GetTotalRecordsfromFetch(string fetchXML, IOrganizationService orgService)
        {
            List<Entity> lstEntity = new List<Entity>();
            int fetchCount = 5000;
            int pageNumber = 1;
            string pagingCookie = null;

            while (true)
            {
                string xml = CreateXml(fetchXML, pagingCookie, pageNumber, fetchCount);
                RetrieveMultipleRequest fetchRequest = new RetrieveMultipleRequest
                {
                    Query = new FetchExpression(xml)
                };

                var returnCollections = ((RetrieveMultipleResponse)orgService.Execute(fetchRequest)).EntityCollection;

                if (returnCollections.Entities.Count >= 1)
                {
                    lstEntity.AddRange(returnCollections.Entities);
                }

                if (returnCollections.MoreRecords)
                {
                    pageNumber++;

                    pagingCookie = returnCollections.PagingCookie;
                }
                else
                {
                    break;
                }
            }
            return lstEntity;
        }

        public static string CreateXml(string xml, string cookie, int page, int count)
        {
            StringReader stringReader = new StringReader(xml);
            XmlTextReader reader = new XmlTextReader(stringReader);

            XmlDocument doc = new XmlDocument();
            doc.Load(reader);

            XmlAttributeCollection attrs = doc.DocumentElement.Attributes;

            if (cookie != null)
            {
                XmlAttribute pagingAttr = doc.CreateAttribute("paging-cookie");
                pagingAttr.Value = cookie;
                attrs.Append(pagingAttr);
            }

            XmlAttribute pageAttr = doc.CreateAttribute("page");
            pageAttr.Value = System.Convert.ToString(page);
            attrs.Append(pageAttr);

            XmlAttribute countAttr = doc.CreateAttribute("count");
            countAttr.Value = System.Convert.ToString(count);
            attrs.Append(countAttr);

            StringBuilder sb = new StringBuilder(1024);
            StringWriter stringWriter = new StringWriter(sb);

            XmlTextWriter writer = new XmlTextWriter(stringWriter);
            doc.WriteTo(writer);
            writer.Close();

            return sb.ToString();
        }

Problem with Aggregate

Above solution doesn't work when u need to Aggregate records in FetchXml. For example I need to run below FetchXml to get count of Accounts group by Primary Contacts. Paging doesn't work here.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
  <entity name="account" >
    <attribute name="accountid" alias="AccountId" aggregate="count" />
    <attribute name="primarycontactid" alias="PrimaryContactid" groupby="true" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

At last, what I had to do was, not so pretty, to retrieve values by chunks. For example, below I am looping though the alphabet so that I am executing the fetch 26 times for each letter where contact name is starting from particular letter. 

internal static void MainFunction(IOrganizationService service)
        {
            const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            foreach (char ch in alphabet)
            {
                string fetchXml = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' aggregate='true' >
                                      <entity name='account' >
                                        <attribute name='accountid' alias='AccountId' aggregate='count' />
                                        <attribute name='primarycontactid' alias='PrimaryContactid' groupby='true' />
                                        <filter>
                                          <condition attribute='statecode' operator='eq' value='0' />
                                          <condition attribute='primarycontactidname' operator='like' value='{{0}}' />
                                        </filter>
                                      </entity>
                                    </fetch>");
                string fetchXmlCompiles = string.Format(fetchXml, ch + "%");

                EntityCollection accounts = service.RetrieveMultiple(new FetchExpression(fetchXmlCompiles));
                Console.WriteLine(accounts.Entities.Count);
            }
            Console.ReadLine();
         }

This is not the best, yet I don't know better way of doing this. If you find one, pl don't forget to comment below.

Reference;

https://nishantrana.me/2017/09/25/sample-code-to-retrieve-more-than-5000-records-using-fetchxml-in-crm/