The dreaded fetchxml 5000 results limit.

Following on from my last post, I was trying to finish up a windows service to clear all the old calendars in our system, when I hit the dreaded 5000 limit in fetchxml queries.

In essence, by default, the most results an fetchxml query will return is 5000. I vaguely remembered this limit from a while ago when we had another deletion situation like this, of a custom entity but that is by the by. I had a look on-line and found that most solutions use a DataSet object, which did not seem like the right object for me this time, as all I wanted was a list of GUIDs, so I just used a List<string> object (calendars in the code example below) and pass each GUID, after it's been stripped of its wiggly brackets ({}), to the deletion method described in my previous post.

Code Example:

   1 bool finished = false;
   2 int fetchPage = 0;
   3 
   4 while (!finished)
   5      {
   6          string fetch = "<fetch version='1.0' mapping='logical' page='" + fetchPage.ToString() + "' count='5000'><entity name='calendar'><attribute name='calendarid'/><filter type='and'>"
   7              + "<condition attribute='createdon' operator='lt' value='" + deletionDate.ToString("yyyy-MM-dd") + "'/>"
   8              + "<condition attribute='modifiedon' operator='lt' value='" + deletionDate.ToString("yyyy-MM-dd") + "'/>"
   9              + "</filter></entity></fetch>";
  10 
  11          string result = crmService.Fetch(fetch);
  12 
  13          XmlDocument doc = new XmlDocument();
  14          doc.LoadXml(result);
  15 
  16          //We'll be finished when nomorerecords is 0. 
  17          if (Convert.ToInt32(doc.FirstChild.Attributes["morerecords"].Value) == 0)
  18          {
  19              finished = true;
  20          }
  21 
  22          XmlNode resultsetNode = doc.SelectSingleNode("//resultset");
  23 
  24          foreach (XmlNode xn in resultsetNode.ChildNodes)
  25          {
  26              //Not the most elegant solution, but I guess it should be quicker than a regex.
  27              calendars.Add(xn.FirstChild.InnerXml.Remove(0, 1).Replace("}", string.Empty));
  28          }
  29 
  30          fetchPage++;
  31      }

Note, that you can set TurnOffFetchThrottling in the registry(HKLM\Software\Microsoft\MSCRM) to 1, to get around this problem as well, but this might create problems performance problems, which is why Microsoft must have introduced this arbitrary limit.