Get total aggregate records count if records is having more than 50000 records

Question

We have huge number on records in our CRM entity. I am trying to fetch the total number of record with the help of aggregate count in fetch xml. But it has limitation of 50000 records. I think there is a way to change that setting in On-premise CRM. But i dont want to change that.

Previously we were using pagination method to fetch total count (5000 each time). But it takes a lot of time

public static int GetTotalRowCount(string fetchXml)
{
  try
  {
    using (OrganizationServiceContext svcContext = new OrganizationServiceContext(ServerConnection.CrmService))
    {
      int totalCount = 0;
      int fetchCount = 5000;
      int pageNumber = 1;
      string pagingCookie = null;
      string xml = string.Empty;
      RetrieveMultipleRequest fetchRequest1 = null;
      EntityCollection entityCollection = null;

      xml = CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount);
      fetchRequest1 = new RetrieveMultipleRequest
      {
        Query = new FetchExpression(xml)
      };

      entityCollection = ((RetrieveMultipleResponse)svcContext.Execute(fetchRequest1)).EntityCollection;

      while (entityCollection.MoreRecords)
      {
        //moving to next page
        pageNumber++;

        xml = CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount);
        fetchRequest1 = new RetrieveMultipleRequest
        {
          Query = new FetchExpression(xml)
        };

        entityCollection = ((RetrieveMultipleResponse)svcContext.Execute(fetchRequest1)).EntityCollection;
        totalCount = totalCount + entityCollection.Entities.Count;
      }

      return totalCount;
    }
  }
  catch (Exception ex)
  {
  }
}

but it takes a lot of time. Hence i changed it to aggregate count method - Changed Fetchxml like this -

<fetch mapping='logical' output-format='xml-platform' no-lock='true' distinct='false' aggregate='true'>
  <entity name='abc_data'>
    <attribute name='abc_id' aggregate='count' alias='count'/>.....

code like this

 int Count = 0;
 FetchExpression fetch = new FetchExpression(fetchXml);
 EntityCollection result = ServerConnection.CrmService.RetrieveMultiple(fetch);
 if (result.Entities.Count > 0)
 {
     Entity entity = result.Entities[0];
     AliasedValue value = (AliasedValue)entity["count"];
     Count = (int)value.Value;
  }
  return Count ;

Now here it gives an exception if records are more than 50000.

So is there way to fetch 50000 record at a time with the help of aggregate count and loop through it to fetch total count?

See [AGGREGATE QUERY RECORD LIMIT EXCEEDED - Modifying the On Premise Settings](http://paul-way.com/crm-2013-aggregate-query-record-limit-exceeded/) and [Use Advanced Configuration Settings](https://learn.microsoft.com/en-us/previous-versions/dynamics-crm2011/developers-guide/gg328128(v=crm.5)) on how to change this for your on-premise system. — Filburt, Mar 26 '19 at 11:45
@omkar, A year ago, I worked with "Dynabacus Solution" for D365, in which it returns the total number of records (Without any limitation) of selected entities, I tried to find that solution for you but no luck, the publisher might have removed that solution from their site. — Sagar R, Mar 26 '19 at 14:00

Aron · Answer 1 · 2019-03-26T13:11:17.383

1

The limitations on the FetchXML aggregation are a challenge that we all face. I wanted to solve the problem once and for all so I built an online tool called AggX to run aggregates on any number of rows. It is currently free to use.

You can check it out at https://aggx.meta.tools, and please note it only works with Dynamics 365 Online. Also, please note that if you have a large number of rows that will take over 5 minutes to run, you should monitor AggX to avoid having it log you out automatically after several minutes of idle time.

If your system is on-prem or you want to write your own code to do aggregates, you can devise an algorithm to split up the data into chunks of less than 50,000 rows. Then you can run the FetchXML aggregate on each chunk and sum the results. That's how the engine of AggX works.

edited Mar 26 '19 at 13:11

answered Mar 26 '19 at 12:11

Aron

3,877
3
14
21

Yes i tried doing that .. changed my fetchxml like - .. so that i can fetch chunk of 50000 at a time .. but still it returns more than 50000 – omkar patade Mar 26 '19 at 12:20
FetchXML is hardcoded to stop at 5,000 regardless of the page size we set. You have to chunk based on the data itself like "name starts with 'A'", then "name starts with 'B'", and so on. Or "created in January", then created in February" and so on. – Aron Mar 26 '19 at 14:22
1

These suggested solutions for a professional product are absolutely sub standard. What are we paying for exactly? – Case 303 Apr 01 '22 at 16:36

score 0 · Answer 2 · answered Jan 24 '23 at 22:14

If you just want the count

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/reference/retrievetotalrecordcount?view=dataverse-latest

                string[] entitylist = new string[] { };
                Array.Resize(ref entitylist, entitylist.Length + 1);
                entitylist[entitylist.Length - 1] = "someentityname";
                RetrieveTotalRecordCountRequest req = new RetrieveTotalRecordCountRequest
                {
                    EntityNames = entitylist
                };
                EntityRecordCountCollection m = ((RetrieveTotalRecordCountResponse)OrganizationService.Execute(req)).EntityRecordCountCollection;
                long count = 0;
                foreach (var i in m)
                {
                    Console.WriteLine(i.Key + " =" + i.Value);
                    count += i.Value;
                }
                Console.WriteLine($"Count ={count}");

Get total aggregate records count if records is having more than 50000 records

2 Answers2