OData system query options using the OData endpoint

 

Applies To: Dynamics CRM 2013

You can refine the results of your query by using system query options. The following table lists the query string options defined in the OData protocol that are implemented in the OData endpoint for Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM Online.

Option	Description
$expand	Directs that related records should be retrieved in the record or collection being retrieved.
$filter	Specifies an expression or function that must evaluate to true for a record to be returned in the collection.
$orderby	Determines what values are used to order a collection of records.
$select	Specifies a subset of properties to return.
$skip	Sets the number of records to skip before it retrieves records in a collection.
$top	Determines the maximum number of records to return.

$expand

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 (CSDL) 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’re 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 2013 and Microsoft Dynamics CRM Online doesn’t support querying a multilevel relationship property. For example, this query, intended to retrieve information about the owning user of opportunities related to accounts, won’t 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 ServerSettings MaxExpandCount setting value.

More information: OData: Filter System Query Option ($expand)

$filter

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

If you’re retrieving additional sets of data using the next link, you shouldn’t 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, but doesn’t 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.

Operator	Description	Example
eq	Equal	/AccountSet?$filter=Address1_City eq 'Redmond'
ne	Not equal	/AccountSet?$filter=Address1_City ne null
gt	Greater than	/AccountSet?$filter=CreditLimit/Value gt 1000
ge	Greater than or equal	/AccountSet?&$filter=CreditLimit/Value ge 1000
lt	Less than	/AccountSet?$filter=CreditLimit/Value lt 1000
le	Less than or equal	/AccountSet?$filter=CreditLimit/Value le 1000
and	Logical and	/AccountSet?$filter=CreditLimit/Value ge 1000 and Address1_StateOrProvince eq 'TX'
or	Logical or	/AccountSet?$filter=AccountCategoryCode/Value eq 2 or AccountRatingCode/Value eq 1
not	Logical Negation	/AccountSet?$filter=(AccountCategoryCode/Value ne null) and not (AccountCategoryCode/Value eq 1)

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.

Function	Example
startswith	/AccountSet?$filter=startswith(Name, 'a')
substringof	/AccountSet?$filter=substringof('store',Name)
endswith	/AccountSet?$filter=endswith(Name, '(sample)')

More information: OData: Filter System Query Option ($filter)

$orderby

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’re retrieving additional sets of data using the next link, you shouldn’t 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/region 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)

More information: OData: Filter System Query Option ($orderby)

$select

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 correspond to $select=*.

If you use $expand to include related data and you use $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, returns 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 limit if you were to include every attribute in the account entity in the $select option. For more information, see Microsoft KB 208427

More information: OData: Filter System Query Option ($select)

$skip

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

If you’re retrieving additional sets of data using the next link, you shouldn’t change the $skip query option value because this causes unpredictable results.

More information: OData: Filter System Query Option ($skip)

$top

Determines a maximum number of records to return.

If you’re retrieving additional sets of data using the next link, you shouldn’t change the $top query option value because this will cause unpredictable results.

More information: OData: Filter System Query Option ($top)

Unsupported system query options

Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM Online doesn’t support using the following system query options:

• $inlinecount

• $count

• $format

See Also

Query Microsoft Dynamics CRM 2013 data using the OData endpoint
OData: URI Conventions