Work with OData query options
OData lets you use filter expressions in the OData URI to limit the results that are returned, filter the results on a specific field, sort the results, and so on.
You can provide specific query parameters to the OData URI. All query parameters start with a dollar sign ($). To begin specifying query parameters, provide a question mark (?) and the different query parameters. The query parameters need to be separated with an ampersand (&).
Select a subset of fields
If you want to retrieve records by using OData, but you don't need all fields defined in a record set, use the $select filter expression. With $select, you can specify the fields that you want to retrieve from the record set. Be careful when you use fields in a filter expression because they're case sensitive.
In the following example, the No, Description, and Unit Price fields are selected from the item record set. The dollar sign in front of the select keyword is important; without it, the command won't work.
/Items?$select=No,Description,Unit_Price
Order the result set
To order the result set, you can use the $orderby filter expression. The filter expressions are all run on the server level, which will improve the performance of the result set that you retrieve with an OData call.
With the orderby keyword, you can specify the direction of the sorting by entering asc (for ascending, which is the default) or desc (for descending).
In the following example, the result set is orderby on the Unit Price field. The fields that you use in the orderby filter expression don't need to be part of the select filter expression.
/Items?$select=No,Description,Unit_Price&$orderby=Unit_Price desc
Limit the result set
To retrieve a specific number of records, use the $top filter expression. This expression can be used together with the $skip filter expression to get the next number of records.
In the ensuing example, the first 10 records are skipped and the next five records are returned.
/Items?$select=No,Description,Unit_Price&$top=5&$skip=10
Filter the result set
You can filter the result set to retrieve only records where one or more fields comply with the conditions that are specified in the $filter filter expression.
The following example the retrieval of all records where Inventory is less than 3000 and the Base Unit of Measure is not equal to PCS.
/Items?$filter=Inventory lt 3000 and Base_Unit_of_Measure ne 'PCS'
You can specify multiple fields by using the and keyword and the or keyword. Use the following expressions to specify a condition:
lt - less than
gt - greater than
eq - equal
ne - not equal
le - less than or equal
ge - greater than or equal
The filter expression can also be used on FlowFilter fields. FlowFields that depend on the filtered FlowFilter will be recalculated.
In the ensuing example, the Location_Filter field is a FlowFilter. This FlowFilter is used in the calculation of the Inventory field, which is a FlowField.
/Items?$select=No,Inventory$filter=Location_Filter eq 'GREEN'
Get line details
For certain record sets, you can request extra details. In the following example, all SalesOrders are requested. However, in this example, you want to retrieve the corresponding SalesLines with the same request. To complete this task, use the $expand filter expression. If the EDM document displays a NavigationProperty for your entity, you can use the $expand filter.
The following example shows all corresponding SalesLines being requested.
/SalesOrders?$expand=SalesOrderSalesLines
You can also filter the SalesLines by using the $filter filter expression.
/SalesOrders?$expand=SalesOrderSalesLines($filter=No eq '1920-S')