How to Retrieve More Than 5.000 Records in Dynamics 365

When you are working with entities like Accounts, Contacts, Leads, Opportunities you will most likely have more than 5.000 records in your environment. There would be cases like you want to bulk update most or all of the records in your system.

When you create your query and fetch the records whether with QueryExpression or FetchXML, you will realize that your query always will retrieve exactly 5.000 records if you have more than 5.000 records.

Why is that? Because by default, an organization service can return only the first 5,000 records, and page details for the next set of records if existent. We can use this page details and retrieve the rest of the records.

So, lets see how can we retrieve these records..

        public void RestoreOwnership()
        {
            QueryExpression query = new QueryExpression();
            query.EntityName = "opportunity";
            query.ColumnSet.AddColumns("opportunityid", "ownerid");

            OrderExpression order = new OrderExpression();
            order.AttributeName = "createdon";
            order.OrderType = OrderType.Descending;
            query.Orders.Add(order);

            var opportunityList = RetrieveAllRecords(query);

            Console.WriteLine($"Retrieved Opportunity Count: {opportunityList.Count}");

            for (int i = 0; i < opportunityList.Count; i++)
            {
                Console.WriteLine($"Index: {i}");
                UpdateOpportunityOwner(opportunityList[i], i);
            }
        }

 public List<Entity> RetrieveAllRecords(QueryExpression query)
        {
            var pageNumber = 1;
            var pagingCookie = string.Empty;
            var result = new List<Entity>();

            EntityCollection resp;

            var opportunityList = service.RetrieveMultiple(query).Entities;

            do
            {
                if (pageNumber != 1)
                {
                    query.PageInfo.PageNumber = pageNumber;
                    query.PageInfo.PagingCookie = pagingCookie;
                }
                resp = service.RetrieveMultiple(query);
                if (resp.MoreRecords)
                {
                    pageNumber++;
                    pagingCookie = resp.PagingCookie;
                }
                result.AddRange(resp.Entities);
            }
            while (resp.MoreRecords);

            return result;
        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s