FetchXml linked entity limit in MS Dynamics CRM 2011

I discovered this today, it's only possible to have 9 linked entities on a fetchXml query. I suspect that this is due to laziness, as the AliasedValue only seems to go to 9.

I might be wrong and there could be a genuine reason, but ....

Construct FetchXml queries the easy way

We all are lazy, I mean, we all like to minimize energy output and what better way of doing that than letting others do the work for you.

An easy way of creating a FetchXml query is to use Advanced Find. (http://<hostname>/ORG/AdvancedFind/AdvFind.aspx)

1. Create the query that you wish to use. Bear in mind that some attributes might be missing, you can always add them later manually to your code.
2. Run Query, i.e. Press Find
3. Type the following in the address bar:   javascript:prompt("q:", resultRender.FetchXml.value);
4. Hit Enter
5. You'll get a window with the query used. Grab it and use it in your code.

If you don't have an address bar, in step 3, just hit Ctrl + N.

Luckily, Microsoft has recognised the productivity gains that this (easily generated FetchXml queries) can generate and has included a button to do this in Dynamics CRM 2011.

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.