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