Limitations when using OData in CRM to retrieve records

This post describes the limitations of the OData endpoint in CRM 2011, which should still be relevant for CRM 2013 and 2015. There is no reason to believe that the OData endpoint will be developed further as Microsoft is working on a brand new WebApi to replace both the current OData endpoint and the SOAP endpoint.

Please refer to this article for general information on the available options when Qerying CRM using OData: OData system query options using the OData endpoint

Re-blogged from http://blogs.msdn.com/b/crm/archive/2011/03/02/using-odata-retrieve-in-microsoft-dynamics-crm-2011.aspx:

With the release of Microsoft Dynamics CRM 2011, Microsoft have added a Windows Communication Foundation (WCF) data services (ODATA) endpoint. The endpoint facilitates CRUD operation on entities via scripts using Atom or Json format. This post mentions some of the considerations when using the endpoint, specifically around the use of retrieves.

First, the operations supported over this endpoint are limited to create, retrieve, update and delete. The REST philosophy does not support other operations and so we followed J. Microsoft did not implement others since the story around service operations was not fully developed in the current WCF data services offering.

The $format and $inlinecount operators are not supported. CRM’s OData endpoint only supports $filter, $select, $expand, $top, $skip, $orderby

Some of the restrictions when using the implemented operators are.

Operator Restrictions
$expand
  • Max expansion 6
$top
  • Page size is fixed to max 50 records
  • $top gives the total records returned across multiple pages
$skip
  • When using with distinct queries, we are limited to the total (skip + top) record size = 5000.
  • In CRM the distinct queries does not use paging cookie are and so we are limited by the CRM platform limitation to the 5000 record.
$select
  • One level of navigation property selection is allowed I.e.

…/AccountSet?$select=Name,PrimaryContactId,account_primary_contact

…/AccountSet?$select=Name,PrimaryContactId,account_primary_

contact/LastName&$expand=account_primary_contact

$filter
  • Conditions on only one group of attributes are allowed. By a group of attribute I am referring to a set of conditions joined by And/Or clause.
  • The attribute group may be on the root entity

…/TaskSet?$expand=Contact_Tasks&$filter=Subject eq ‘test’ and Subject ne null

  • (or) on the expanded entity.

…/TaskSet?$expand=Contact_Tasks&$filter=Contact_Tasks/FirstName eq ‘123‘

  • Arithmetic, datetime and math operators are not supported
  • Under string functions, Substringof, endswith and startswith are supported
$orderby
  • Order are only allowed on the root entity.
Navigation
  • Only one level of navigation is allowed in any direction of a relationship. The relationship could be 1:N, N:1, N:N
Advertisements