Tuesday 23 May 2017

MSCRM - Retrieve more than 5000 records using FetchXML - Simplified



I was working on a piece of C# code to retrieve more than 5000 records using Fetchxml. This may get bit tricky, so tried to simply the same as much as possible in the below post.

By default an organization service can return only the first five thousand records and page details for the next set of records, if exists.

The result entity collection exposes a Boolean attribute called MoreRecords, it will be true if more records available else false.

If more records available then we need to use the combo of pagingcookie and page(pagenumber) to retrieve rest of the records.

**Do not forget to add {0} in fetchxml which is used to include page details in below code

Code Snippet:

        public List<Entity> RetrieveAllRecords(IOrganizationService service, string fetch)
        {
            var moreRecords = false;
            int page = 1;
            var cookie = string.Empty;
            List<Entity> Entities = new List<Entity>();
            do
            {
                var xml = string.Format(fetch, cookie);
                var collection = service.RetrieveMultiple(new FetchExpression(xml));

                if (collection.Entities.Count >= 0) Entities.AddRange(collection.Entities);

                moreRecords = collection.MoreRecords;
                if (moreRecords)
                {
                    page++;
                    cookie = string.Format("paging-cookie='{0}' page='{1}'", System.Security.SecurityElement.Escape(collection.PagingCookie), page);
                }
            } while (moreRecords);

            return Entities;
        }


Example:

            var fetch = "<fetch {0}>" +
                        "    <entity name='accounts' >" +
                        "        <attribute name='accountid' />" +
                        "        <attribute name='name' />" +
                        "    </entity>" +
                        "</fetch>";
            var accountCol = RetrieveAllRecords(service, fetch);



2 comments: