OData Syntax

Microsoft’ data service commonly provide RESTful interfaces that utilize OData syntax.

Filter Rows

Beside the option to retrieve all records beloning to a entity you have the ability to specify additional filter criteria for your query. The filter employs various syntax and operators, including acronyms such as eq for equal and gt for greater than, among others.

Symbol	Operator	Example
eq	=	$filter=size eq 100
ne	!=	$filter=size ne 200
gt	>	$filter=size gt 50
ge	>=	$filter=size ge 100
lt	<	$filter=size lt 300
le	<=	$filter=size le 125
and	&	$filter=size gt 100 and size lt 200
or	|	$filter=size lt 10 or size gt 400
not	!	$filter=not size eq 100
()	precedence	$filter=(size eq 100) or (...)

The query response has the following schema.

{
  odata.metadata: "http://.../$metadata",
  value:
    [
      {
        name: "Crocodile",
        url: "https://en.wikipedia.org/wiki/Crocodile"
      },
      {
        name: "Alligator",
        url: "https://en.wikipedia.org/wiki/American_alligator"
      }
    ]
}

Limit rows

The top command lets you limit the number of records your query will return.

/accounts?$top=10

Joining Tables

The way to join tables in Odata is to make use of the expand command which will return a nested document with the related items. For example to looks up all acounts and joins them with their addresses use the follwing query:

crm.dynamics.com/api/data/v9.2/acoutns?$select=name&$expand=Account_CustomerAddrss

The expand command takes a relationship to resolve the link between the tables. To know what name the relationship has you can do the following.

On the address table go to relationshpas and search for the name of the relationship to your source table.

Display name	Name	Related table	Relationship type
Parent	Account_CustomerAddress	Account	Many-to-one

Multiple Joins

A common way to join multiple lookups at the same time it is possible to state the tables with a comma separated.

...contacts(1939100)/?$expand=firstTable,secondTable

Update Records with Lookup

Records are connected to each other through relationships, with a foreign key set on one side of the relationship. In the context of OData, this is called binding. Assosiate with a single-valued naviagtion property.

{
  "parentcustomerid_account@odata.bind": "/accounts"
}

An example from Microsoft Dynamics 365 Web API to set the field Regarding.

{
  "regardingobjectid_account@odata.bind": "/accounts(a8d8cf490-...-6405bd2b29d8)"
}

Send this data as a POST request will set the field “_regardingobejctid_value” with the according GUID.