An inherent limitation placed on retrieving data via CRM 2011’s OData endpoint (OrganizationData.svc) is that the response can only include up to a maximum of 50 records (http://msdn.microsoft.com/en-us/library/gg334767.aspx#BKMK_limitsOnNumberOfRecords_). For the most part, this does not impact your ability to retrieve the primary entity data sets because each response will provide the URI for a subsequent request that includes a paging boundary token. As you can see below, the URI for the next page of results is provided in the JSON response object:
Here is what the full URI looks like from the example response shown above, including the $skiptoken paging boundary parameter:
- Silverlight OData paged retrieve demonstration: http://msdn.microsoft.com/en-us/library/gg985388.aspx
Nevertheless, this 50 record limit can become problematic when using $expand/.Expand() to return related entity records in your query results. The expanded record sets do not contain any paging boundary tokens, thus you will always be limited to the first 50 related records.
The next logical question – can this limit be increased? Yes, it can. 50 records is a default limit available for update via an advanced configuration setting stored in MSCRM_Config. Note that increasing the limit may only delay the inevitable for encountering results that exceed the limit. It may also cause your responses to exceed the message limits on your WCF bindings depending on how you have that configured. To avoid these scenarios, you may instead consider breaking up your expanded request into a series of requests where each result set can be paged. Also, before proceeding, I’d be remiss not to point out the following advisory from CRM 2011 SDK:
You should use the advanced settings only when indicated to do so by the Microsoft Dynamics CRM customer support representatives.
That said, now on with how to modify the setting…
The specific setting that imposes the 50 record limit is called ‘MaxResultsPerCollection’ and it’s part of the ServerSettings configuration table. Go here for the description of MaxResultsPerCollection and other ServerSettings available: http://msdn.microsoft.com/en-us/library/gg334675.aspx.
Advanced configuration settings can be modified either programmatically via the Deployment Service or via PowerShell cmdlet. The CRM 2011 SDK provides an example of how to set an advanced configuration setting via the Deployment Service here: http://msdn.microsoft.com/en-us/library/gg328128.aspx
To update the setting via PowerShell, execute each of the following at the PowerShell command-line:
Alternatively, you could use the PowerShell script provided in the CRM 2011 SDK to update advanced configuration settings: [SDK Install Location]\samplecode\ps\setadvancedsettings.ps1
To be able to execute scripts, you must first set your PowerShell execution policy to allow unsigned local scripts.
Then, execute the ‘setadvancedsettings.ps1’ PowerShell script from the SDK and pass in the configuration entity name (string), attribute name (string), and new value (Int32).
After performing either of the two methods above to update your setting, you can verify that the change was successful by inspecting the ServerSettingsProperties table in MSCRM_Config database.
Since these configuration values are cached, perform an IISRESET on the application server if you want them to take immediate affect.
Below are the results of a basic retrieve of AccountSet showing that more than the default 50 records were returned after applying this configuration change.
A few parting words…just because you can, doesn’t mean you should.