Query options

This articles goes over the various query options implemented by the queryable API.

$filter

The $filter query option allows API consumers to filter the result set of a query.

It implements the syntax and basic functionality of the OData specification.

Example

/api/v1/Inventory/Parts?$filter=PartNumber eq 'TEST' AND StandardPrice gt 200

Filters parts where the part number is exactly TEST and the standard price of the part is greater than 200.

See Parts for more information about the part queryable.

Supported query operations

The supported query operations from the OData standard are as follows.

  • Eq
  • Neq
  • Lt
  • Gt
  • Le
  • Ge
  • And
  • Or

Supported query functions

The supported query functions from the OData standard are as follows.

  • add
  • mul
  • div
  • mod
  • isnull
  • isnotnull
  • contains*

* Implemented as IN and not as Contains, see below for more information.

Like function

In addition to the standard OData functions, we have also implemented a like function that translates into an SQL LIKE query.

Example (no encoding)
$filter=like(CategoryString, '%test%')

Contains()

The way contains works is a bit different from how OData specifies. It will instead translate into the equivalent of an IN query.

The following contains statement:

contains(PartNumber, 'Test1', 'Test2', 'Test3')

Will translate into:

PartNumber IN ('Test1', 'Test2', 'Test3')

$select

The $select query option allows API consumers to request only a subset of fields for a queryable entity.

It implements the comma-separated syntax defined in the OData specification.

Example

/api/v1/Inventory/Parts?$select=Id,PartNumber,Description

Selects only the business key identifier, part number and description of all parts.

See Parts for more information about the part queryable.

$expand

The $expand query option allows API consumers to request inner entities of a queryable entity. A property may be expanded if it has the Expandable attribute in the API Reference.

The implementation is partially based on the syntax defined in the OData specification.

Example

/api/v1/Sales/CustomerOrders?$expand=Rows,MailingAddress

Queries all customer orders along with its customer order rows and mailing address.

See CustomerOrders for more information about the customer order queryable.

$orderby

The $orderby query option allows API consumers to order the result of a query.

The implementation is based on the syntax defined in the OData specification.

Example

/api/v1/Inventory/Parts?$orderby=PartNumber desc

Queries all parts ordered by part number in descending order.

See Parts for more information about the part queryable.

$top

The $top query option allows API consumers to limit the result of a query.

The implementation is based on the syntax defined in the OData specification.

Example

/api/v1/Inventory/Parts?$top=5

Queries the first five parts.

$skip

The $skip query option allows API consumers to skip a specified amount of results in a query.

The implementation is based on the syntax defined in the OData specification.

/api/v1/Inventory/Parts?$skip=5

Queries all but the first five parts.