OData System Query Options Using the REST Endpoint

Directs that related records should be retrieved in the record or collection being retrieved.

If you want to retrieve related records, locate the name of the entity relationship that defines this relationship. You may have to view the entity relationship information in the application to correctly identify the relationship or the conceptual schema definition language (CDSL) for the Organization Data Service.

For example, to retrieve opportunity records related to accounts, use the opportunity_customer_accounts entity relationship. The query /AccountSet?$expand=opportunity_customer_accounts returns the opportunity records and the account records.

If you are limiting the columns returned, you must also include the name of the navigation property in the query. For example, the query /AccountSet?$select=Name,opportunity_customer_accounts&$expand=opportunity_customer_accounts returns only the account name and the expanded opportunity records.

Microsoft Dynamics CRM 2011 does not support querying a multi-level relationship property. For example, this query, intended to retrieve information about the owning user of opportunities related to accounts, will not work: /AccountSet?$expand=opportunity_customer_accounts/opportunity_owning_user.

By default, you can define up to six relationships to expand. This query, expanding both the opportunities and system user that owns the account, will work, /AccountSet?$select=Name,opportunity_customer_accounts,user_accounts&$expand=opportunity_customer_accounts,user_accounts.

Note
The number of related records returned is subject to the same limitation on the number of records returned. For more information, see Limitations on number of records returned. You can increase the number of relationships to expand by changing the MaxExpandCount setting value. For more information, see ServerSettings Table Metadata (Advanced Settings).

For more information, see OData: Filter System Query Option ($expand).

Specifies an expression or function that must evaluate to true for a record to be returned in the collection.

If you are retrieving additional sets of data using the next link you should not change the $filter query option value because this causes unpredictable results.

The OData specification for the Filter System Query Option describes the operators used to create an expression to evaluate in the filter. Microsoft Dynamics CRM uses all the logical operators and a subset of the functions available. Microsoft Dynamics CRM does not support use of the arithmetic operators. Filters can use grouped conditions indicated by parentheses. Comparisons of time values must use the following format: datetime’<time value>’, for example datetime'2010-07-15' or datetime'2010-07-15T16:19:54Z'.

The following table lists the operators supported by Microsoft Dynamics CRM.

You can use the $filter query option to filter for the value of an EntityReference attribute. For example, if you have the Id value for a SystemUser record, you can retrieve a list of all the account records that a user is assigned by using the following filter:

/AccountSet?$filter=OwnerId/Id eq (guid'" + SystemUserId + "')

The following table lists the functions supported by Microsoft Dynamics CRM.

For more information, see OData: Filter System Query Option ($filter).

Determines what values are used to order a collection of records. By default, the order is ascending. Use desc to reverse the order or asc to explicitly set the default.

If you are retrieving additional sets of data using the next link you should not change the $orderby query option value because this causes unpredictable results.

Note
You can select up to 12 columns using $orderby. When ordering by complex types, such as EntityReference or OptionSetValue, only the attribute name should be specified. For example, to order by the primary contact of an account, use the query $orderby=PrimaryContactId. Specifying $orderby=PrimaryContactId/Name will give you a Bad Request HTTP error (HTTP Error Code 400).

The following sample lists account records by country in ascending order, and by city in descending order:

/AccountSet?$select=Address1_Country,Address1_City,Name&$orderby=Address1_Country,Address1_City desc&$filter=(Address1_Country ne null) and (Address1_City ne null)

For more information, see OData: Filter System Query Option ($orderby).

Specifies a subset of properties to return and the order in which the columns of data will be organized. The default is to return all columns that corresponds to $select=*.

If you are using $expand to include related data and you are using $select to limit the columns returned, you must also include the name of the navigation property in the query. For example, the following query, /AccountSet?$select=Name,opportunity_customer_accounts&$expand=opportunity_customer_accounts, will return only the account name and the expanded opportunity records.

Note
There is a maximum path length of 2048 characters for POST and GET requests in Internet Explorer. You can easily reach this limitation if you were to include every attribute in the account entity in the $select option. For more information, see Microsoft KB 208427

For more information, see OData: Filter System Query Option ($select).

Sets the number of records to skip before retrieving records in a collection.

If you are retrieving additional sets of data using the next link you should not change the $skip query option value because this causes unpredictable results.

For more information, see OData: Filter System Query Option ($skip).

Determines a maximum number of records to return.

If you are retrieving additional sets of data using the next link you should not change the $top query option value because this will cause unpredictable results.

For more information, see OData: Filter System Query Option ($top).

Microsoft Dynamics CRM 2011 does not support using the following system query options:

• $inlinecount
  
  
• $count
  
  
• $format
  
  

Concepts

Other Resources