Content

OData queries

Introduction

OData is a standardized protocol for consuming Web APIs. OData builds on core protocols like HTTP and commonly accepted methodologies like REST. The Multivers Web API supports OData queries for all collection types.
OData is a standard protocol to query RESTful webserivces and supports filtering, sorting and paging. In this article the common features of OData queries are discussed based on some exmples. For more information about OData see http://www.odata.org/.

Order your data ($orderby)

Use the "$orderby" operator to sort your data. For example order the productlist on stock transfer price: 


    api/MVL00001/ProductInfoList?$orderby=StockTransferPrice

Filter your data ($filter)

Use the "$filter" operator to filter your data. The expression language that is used in $filter operators supports references to properties and literals. The literal values can be strings enclosed in single quotes, numbers and boolean values (true or false).

Operators

The operators supported in the expression language are shown in the following table.
Operator Description Example
Logical Operators
Eq Equal api/MVL00001/CustomerInfoList?$filter=City eq ‘AMSTERDAM’
Ne Not equal api/MVL00001/CustomerInfoList?$filter=City ne ‘AMSTERDAM’
Gt Greater than api/MVL00001/ProductInfoList?$filter=StockTransferPrice gt 20
Ge Greater than or equal api/MVL00001/ProductInfoList?$filter=StockTransferPrice ge 10
Lt Less than api/MVL00001/ProductInfoList?$filter=StockTransferPrice lt 20
Le Less than or equal api/MVL00001/ProductInfoList?$filter=StockTransferPrice le 100
And Logical and api/MVL00001/ProductInfoList?$filter=StockTransferPrice le 200 and StockTransferPrice gt 3.5
Or Logical or api/MVL00001/ProductInfoList?$filter=StockTransferPrice le 3.5 or StockTransferPrice gt 200
Not Logical negation api/MVL00001/ProductInfoList?$filter=not endswith(Description,’milk’)
Arithmetic Operators
Add Addition api/MVL00001/ProductInfoList?$filter=StockTransferPrice add 5 gt 10
Sub Subtraction api/MVL00001/ProductInfoList?$filter=StockTransferPrice sub 5 gt 10
Mul Multiplication api/MVL00001/ProductInfoList?$filter=StockTransferPrice mul 2 gt 2000
Div Division api/MVL00001/ProductInfoList?$filter=StockTransferPrice div 2 gt 4
Mod Modulo api/MVL00001/ProductInfoList?$filter=StockTransferPrice mod 2 eq 0
Grouping Operators
( ) Precedence grouping api/MVL00001/ProductInfoList?$filter=(StockTransferPrice sub 5) gt 10

Functions

In addition to operators, a set of functions are also defined for use with the filter query string operator. The following table lists the available functions.
Function Example
String Functions
bool substringof(string po, string p1) api/MVL00001/CustomerInfoList?$filter=substringof(‘Bekker’, Name) eq true
bool endswith(string p0, string p1) api/MVL00001/CustomerInfoList?$filter=endswith(Name, ‘B.V.’) eq true
bool startswith(string p0, string p1) api/MVL00001/CustomerInfoList?$filter=startswith(Name, ‘Van’) eq true
int length(string p0) api/MVL00001/CustomerInfoList?$filter=length(Name) eq 19
int indexof(string p0, string p1) api/MVL00001/CustomerInfoList?$filter=indexof(Name, ‘everly’) eq 1
string replace(string p0, string find, string replace) api/MVL00001/CustomerInfoList?$filter=replace(Name, ‘ ‘, ”) eq ‘AlfredsFutterkiste’
string substring(string p0, int pos) api/MVL00001/CustomerInfoList?$filter=substring(Name, 1) eq ‘everly Beauty Supplies’
string substring(string p0, int pos, int length) api/MVL00001/CustomerInfoList?$filter=substring(Name, 1, 2) eq ‘ev’
string tolower(string p0) api/MVL00001/CustomerInfoList?$filter=tolower(Name) eq ‘beverly beauty supplies’
string toupper(string p0) api/MVL00001/CustomerInfoList?$filter=toupper(Name) eq ‘BEVERLY BEAUTY SUPPLIES’
string trim(string p0) api/MVL00001/CustomerInfoList?$filter=trim(Name) eq ‘Beverly Beauty Supplies’
string concat(string p0, string p1) api/MVL00001/CustomerInfoList?$filter=concat(concat(City, ‘, ‘), CountryId) eq ‘Berlin, DE’
Math Functions
decimal round(decimal p0) api/MVL00001/OrderInfoList/1001?$filter=round(AmountInclVat) eq 735
decimal floor(decimal p0) api/MVL00001/OrderInfoList/1001?$filter=floor(AmountInclVat) eq 734
decimal ceiling(decimal p0) api/MVL00001/OrderInfoList/1001?$filter=ceiling(AmountInclVat) eq 735

Restrict the number of records returned ($top and $skip)

You can restrict the amount of data returned by calculating an offset and the number of records to return. The offset is set using the "$skip" operator and the number of items returned via the "$top" operator.
For example the three products with the highest prices: 


    api/MVL00001/ProductInfoList?$orderby=StockTransferPrice desc&$top=3
To query the next three products use the "$skip" operator. For example: 

    api/MVL00001/ProductInfoList?$orderby=StockTransferPrice desc&$top=3&$skip=3

Restrict the number of columns returned ($select)

The "$select" operator allows a client to pick a subset of the properties of an entity to be retrieved when querying.

For example, we could use "$select" to return only the "OrderId" and "AmountInclVat" properties of the "OrderInfoList": 


    api/MVL00001/OrderInfoList/1001?$select=OrderId,AmountInclVat
The response looks like the following sample: 

    [
        {
            "amountInclVat": 734.71,
            "orderId": "20134003"
        },
        {
            "amountInclVat": 2612.05,
            "orderId": "20134005"
        },
        {
            "amountInclVat": 7068.60,
            "orderId": "20134008"
        }
    ]

Including lazy loaded fields ($expand)

A URI with an $expand segment indicates that entries associated with the entity must be represented inline (i.e. eagerly loaded). Some fields of certain types have a significant impact on the performance of the request, those fields are not returned by default and will have a null value. Add the OData $expand query option to indicate which lazy-loaded values should be returned.

The IncomeStatement type has a property that can be expanded. The breakdown per period for each statement is not returned by default because it incurs a significant performance penalty. 

        
GET api/MVL01001/IncomeStatement
            
{
  "fiscalYear": 2014,
  "lastTransactionPeriod": 9,
  "statements": [
    {
      "description": "Crediteuren",
      "details": null,
      "fiscalYear": 2014,
      "formula": "[16.00.00(1)]",
      "isFormulaValid": true,
      "statementId": "CRED",
      "total": 30107.96
    },
    // more statements...
  ]
}
The 'details' property is set to null here. The $expand parameter can be used to have the result include the details for each statement. 
        
GET api/MVL01001/IncomeStatement?$expand=Statements/Details
            
{
  "fiscalYear": 2014,
  "lastTransactionPeriod": 9,
  "statements": [
    {
      "description": "Crediteuren",
      "details": [
        {
          "cumulative": 5303.66,
          "endDate": "31-1-2014",
          "periodAbbreviation": "per. 1",
          "periodDescription": "Periode 1",
          "periodId": 1,
          "startDate": "1-1-2014",
          "total": 5303.66
        },
        // more details...
      ],
      "fiscalYear": 2014,
      "formula": "[16.00.00(1)]",
      "isFormulaValid": true,
      "statementId": "CRED",
      "total": 30107.96
    }
    // more statements...
  ]
}
The WebAPI documentation page shows the $expand parameter whenever it can be used and indicates which properties can be expanded.