OData Query Options

Introduction

This is a list of query options for OData.

Retrieving Objects

Retrieving All Objects

All objects can be retrieved by specifying the URI. For example: /odata/myservice/v1/Employees. You can see this if you specify the URI in a browser.

Retrieving a Single Object

A single object can be retrieved by passing the object identifier in the URI. For example: /odata/myservice/v1/Employees(8444249301330581).

Retrieving Associated Objects

For this example, imagine that you have four entities in your domain model: Employee, Car, Address, and City. They include the following:

• An association between Employee and Car
• An association between Employee and Address
• An association between City and Address

Associated objects can be retrieved by passing the $expand query parameter. For example: /odata/myservice/v1/Employees?$expand=Cars,Address($expand=City) (OData 4) or /odata/myservice/v1/Employees?$expand=Cars,Address/City (OData 3).

Counting the Number of Objects

Retrieving a Count of Objects

You can find out how many objects there are by passing the $count query option. In this case, the result is an integer which is the number of objects. For example: /odata/myservice/v1/Employees/$count.

(Inline) Count

For OData 4, by setting the $count query option to true, a count of the number of items returned will be included in the result. For example: ?$count=true.

For OData 3, by setting the $inlinecount query option to allpages, a count of the number of items returned will be included in the result. For example: ?$inlinecount=allpages.

Filtering

Filters are applied by appending a $filter=... parameter to the request. For example: /Employees?$filter=Name eq 'John'.

Passing attributes

This table describes how to pass values for different attribute types:

Comparison Operators

We support the following comparison operators:

Functions

¹ In OData 3, the contains function is called substringof, and its arguments are reversed For example, /Employees?$filter=substringof('f', Name)

Combining Filters

Filters can be combined with and, or, not, and (). For example: ?$filter=Name eq 'John' and (Age gt 65 or Age lt 11).

Filtering by Association

You can filter on attributes of an associated entity. The way you do this depends on whether the association exposes one object or a list of objects.

Filtering on an associated object or list in this way is possible when you expose associations as a link. It is not possible when you expose associations as an associated object ID.

Arithmetic Operators

The use of arithmetic operators such as add, sub, mul, div, and mod in filter expressions is not supported.

Sorting

You can sort the result using the $orderby query option. For example: ?$orderby=Name or ?$orderby=BirthPlace/CityName.

The default direction is ascending, and you can make this explicit. For example: ?$orderby=Name asc.

You can also order the result in a descending direction. For example: ?$orderby=Name desc.

It is possible to sort on multiple attributes, which have to be comma-separated. For example: ?$orderby=Name asc,Age desc.

Selecting fields

You can select which attributes and associations to return by specifying the $select query option. For example: ?$select=Name,Age.

Paging

Paging allows you to load data incrementally to better handle large amounts of data. Paging occurs when the client requests a lot of data, and the server returns a subset and a link to request the rest.

Top (Limit)

You can limit the number of returned objects using the $top query option, where the limit is a positive integer. For example: ?$top=100.

Skip (Offset)

You can skip a number of objects before retrieving the result using the $skip query option, where the offset is a positive integer. For example: ?$skip=100 will return objects starting with the 101st object in the list.

Null Literals

You can compare values against the null literal. For example: ?$filter=Name eq null.

In this example, Name is a string attribute that can have no assigned value in the database. Note that null means no value as opposed to '' (which is an empty string).

When you filter against associations, null literals can be quite useful. For example: ?$filter=Association_A_B ne null. In this example, you query for objects of entity type A that have at least one association set to objects of entity type B.

Passing Query Options in the Request Body

If the OData query is too long to be sent as a GET request, clients can send the query as a POST request to the /$query endpoint. For example, GET /Products?$select=Name,Price and POST /Products/$query with $select=Name,Price in the request body give the same result. These POST requests must specify the header Content-Type: text/plain.

Updating Objects

Updating Attributes

When a published resource has the Updatable capability, clients can update its attributes and associations by sending a PATCH request to the URL of the object (for example, PATCH /odata/myservice/v1/Employees(8444249301330581)).

Specify new values for attributes in the body of the request. Here is an example:

{
  "Name": "John",
  "FirstContact": "2012-12-03T07:16:23Z",
  "DateOfBirth": null
}

Updating Attributes of Enumeration Type

Attributes of an enumeration type can be updated by specifying the exposed value of the enumeration, without the prefix of the enumeration type, in the body of the PATCH request. For an attribute of type Country with values MyModule.Country.FR, MyModule.Country.BR and MyModule.Country.JP, exposed as France, Brazil, and Japan respectively, you can update your object as follows:

{
  "Country": "Brazil"
}

Updating asociations

When the association refers to a single object, use the @id syntax to set an associated object, or use null to empty the associated object. Here is an example:

{
  "Birthplace": { "@id": "Cities(511342)" },
  "Region": null
}

When the association refers to multiple objects, add objects to or remove objects from the association by using the @delta syntax:

{
  "Customers@delta": [
    {
      "@id": "Customers(484)"
    },
    {
      "@removed": {
        "reason": "changed"
      },
      "@id": "Customers(712)"
    }
  ]
}

Clients can only update an association from the entity that is the owner.

Inserting Objects

When a published resource has the Insertable capability, clients can create new objects by sending a POST request to the URL of the entity set (for example, POST /odata/myservice/v1/Employees).

The body of the request may specify attribute and association values just as with updates. There is one difference: when the association refers to multiple objects, objects are specified without using @delta. For example:

{
  "Customers": [
    { "@id": "Customers(484)" },
    { "@id": "Customers(712)" }
  ]
}

Clients can only set values for an association from the entity that is the owner.

Deleting Objects

When a published resource has the Deletable capability, clients can delete an object by sending a DELETE request to the URL of the object (for example, PATCH /odata/myservice/v1/Employees(8444249301330581)).