CODEWITHSUNDEEP
c#dynamics-crmcrmdynamics-crm-2015
omkar patade
omkar patade

Reputation: 1591

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

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?

Upvotes: 3

Views: 2992

Answers (2)

Christopher Klein
Christopher Klein

Reputation: 2793

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}");

Upvotes: 0

Aron
Aron

Reputation: 3935

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.

Upvotes: 1

Related Questions