Skip to main content

Open Data Protocol by Example

Chris Sells, Junlin Zheng

August 2014

The purpose of the Open Data protocol i (hereafter referred to as OData) is to provide a REST-based protocol for CRUD-style operations (Create, Read, Update and Delete) against resources exposed as data services. A “data service” is an endpoint where there is data exposed from one or more “collections” each with zero or more “entries”, which consist of typed named-value pairs. OData is published by Microsoft under OASIS (Organization for the Advancement of Structured Information Standards) Standards so that anyone that wants to can build servers, clients or tools without royalties or restrictions.

Exposing data-based APIs is not something new. The ODBC (Open DataBase Connectivity) is a cross-platform API set with data source provider implementations for data sources as wide ranging as SQL Server and Oracle to comma-separated values and Excel files. If you're a Windows programmer, you may be familiar with OLEDB or ADO.NET, which are COM-based and.NET-based APIs respectively for doing the same thing. And if you're a Java programmer, you'll have heard of JDBC. All of these APIs are for doing CRUD across any number of data sources.

Since the world has chosen to keep a large percentage of its data in structured format, whether it’s on a mainframe, a mini or a PC, we have always needed standardized APIs for dealing with data in that format. If the data is relational, the Structured Query Language (SQL) provides a set of operations for querying data as well as updating it, but not all data is relational. Even data that is relational isn’t often exposed for use in processing SQL statements over intranets, let alone internets. The structured data of the world is the crown jewels of our businesses, so as technology moves forward, so must data access technologies. OData is the web-based equivalent of ODBC, OLEDB, ADO.NET and JDBC, and it’s mature enough to be implemented by IBM’s WebSphere ii, be the protocol of choice for the Open Government Data Initiative iii and is supported by Microsoft’s own SharePoint, Office 365, Azure, Dynamics, ASP.NET Web API iv and WCF Data Services framework v. In addition, it can be consumed by Excel’s PowerPivot, Power Query, plain vanilla JavaScript, ODataCpp, Apache Olingo Java and JavaScript client, and Microsoft’s own Visual Studio development tool.

In a web-based world, OData is the data access API you need to know.

OData

You might be happy starting with the OData Protocol documentation vi. The OData Protocol specification defines how to standardize a typed, resource-oriented CRUD interface for manipulating data sources by providing collections of entries which must have required elements and that can be extended. In addition, it illuminates a data model for defining typed or untyped values on an entry (e.g. columns in a row), as well as a query language for getting just the entries and the data that we want. Of course, it also provides the idea of getting as well as updating and removing entries.

An OData service must support at least one of JSON vii or Atom viii format for its request and response bodies. JSON has become the OASIS Standard nowadays, while Atom is OASIS technical committee specification now.

Before we dive into the details on OData, I should mention that, as with any specification, different implementations support different features. I'll be discussing OData from the protocol point-of-view and demonstrating many of the features, but your mileage may vary service to service depending on which parts of the protocol they implement.

Now, let's get started on the OData service model.

Service Model

OData services are defined using a common data model. The service advertises its concrete data model in a machine-readable form, allowing generic clients to interact with the service in a well-defined way.

An OData service exposes two well-defined resources that describe its data model; a service document and a metadata document.

The service document lists entity sets, functions, and singletons that can be retrieved. Clients can use the service document to navigate the model in a hypermedia-driven fashion.

The metadata document describes the types, sets, functions and actions understood by the OData service. Clients can use the metadata document to understand how to query and interact with entities in the service.

In addition to these two “fixed” resources an OData service consists of dynamic resources. The URLs for many of these resources can be computed from the information in the metadata document.

Let’s take a look at service document first.

Service Document

A service document in JSON is represented as a single JSON object with at least two properties; odata.context and value. For example, we can request for the OData TripPin sample service to have a look:

URL: http://services.odata.org/V4/TripPinServiceRW/
Response:
{
     @odata.context: "http://services.odata.org/V4/TripPinServiceRW/$metadata",
     value: 
     [
         {
            name: "Photos",
            kind: "EntitySet",
            url: "Photos"
          },
          {
             name: "People",
             kind: "EntitySet",
             url: "People"
          },
          {
             name: "Airlines",
             kind: "EntitySet",
             url: "Airlines"
          },
          {
             name: "Airports",
             kind: "EntitySet",
             url: "Airports"
          },
          {
             name: "Me",
             kind: "Singleton",
             url: "Me"
          },
          {
             name: "GetNearestAirport",
             kind: "FunctionImport",
             url: "GetNearestAirport"
          }
     ]
}

The value of the odata.context property is the URL of the metadata document, which defines the basic content information of this service. And the value property is an array containing elements for each entity set, e.g. People, and function import with an explicit or default value of true for the attribute IncludeInServiceDocument, e.g. GetNearestAirport, and singletons, e.g. Me, exposed by the service.

Properties and Metadata

After getting the information about exposed entries by the service, but how did we determine what properties were needed inside a content element in the first place? If we had to just eyeball it, that’s going to make for a difficult programming experience. Luckily, we don’t have to do that – we can ask:

URL: http://services.odata.org/V4/TripPinServiceRW/$metadata
Response:
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
   <edmx:DataServices>
      <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="Microsoft.OData.SampleService.Models.TripPin">
		  ...
         <EntityType Name="Person" OpenType="true">
            <Key>
               <PropertyRef Name="UserName" />
            </Key>
            <Property Name="UserName" Type="Edm.String" Nullable="false">
               <Annotation Term="Org.OData.Core.V1.Permissions">
                  <EnumMemberReference Name="Org.OData.Core.V1.Permission/Read" />
               </Annotation>
            </Property>
            <Property Name="FirstName" Type="Edm.String" Nullable="false" />
            <Property Name="LastName" Type="Edm.String" Nullable="false" />
            <Property Name="Emails" Type="Collection(Edm.String)" Nullable="false" />
            <Property Name="AddressInfo" Type="Collection(Microsoft.OData.SampleService.Models.TripPin.Location)" Nullable="false" />
            <Property Name="Gender" Type="Microsoft.OData.SampleService.Models.TripPin.PersonGender" />
            <Property Name="Concurrency" Type="Edm.Int64" Nullable="false">
               <Annotation Term="Org.OData.Core.V1.Computed" Bool="true" />
            </Property>
            <NavigationProperty Name="Friends" Type="Collection(Microsoft.OData.SampleService.Models.TripPin.Person)" />
            <NavigationProperty Name="Trips" Type="Collection(Microsoft.OData.SampleService.Models.TripPin.Trip)" ContainsTarget="true" />
            <NavigationProperty Name="Photo" Type="Microsoft.OData.SampleService.Models.TripPin.Photo" />
         </EntityType>
         ...
      </Schema>
   </edmx:DataServices>
</edmx:Edmx>

The document returned from the OData $metadata operation is defined by the "OData Common Schema Definition Language" (aka "CSDL") specification ix , which says it's the Schema element under the Edmx and DataServices elements that you really care about, and also defines Microsoft's Entity Data Model (EDM), which is also the data model of OData. The CSDL specification tells us how we can interpret the result of the $metadata operation to see what kind of data is being exposed by the OData service. Here's what it says we can tell about the Person type exposed by the People collection defined in the service document:

  • The Person key is made up of a single property named UserName. The key is the thing we use to navigate to a specific entry, as we'll see. The reason that the Key entry can contain multiple sub-entries is that some entities have multiple values that make up the key.
  • The UserName is a string and is non-null.
  • The FirstName and LastName are strings and are both required to be provided when a new Person is created.
  • The Emails and AddressInfo are collections of string and Location respectively. They're both required to be provided when a new Person is created too.
  • The Gender is a PersonGender enumeration. It is optional.
  • The Concurrency is a 64-bit integer and is not null. It should be computed by the server.
  • We can navigate from People to Friends, Trips or Photo as defined by the NavigationProperty, which we'll look at more later.

The CSDL instance documents are easy enough to read that you mostly won't even need to dig through the CSDL specification, although it's there for letting you what the range of data types is and other niggling details. CSDL defines the usual suspect of primitive types, as shown in Table 1.

TypeMeaningJSON ExampleAtom Example
Edm.BinaryBinary dataBinaryValue: “FRwvAAI...”<d:BinaryValue m:type="Binary">FRwvAAI...</d:BinaryValue>
Edm.BooleanBinary-valued logicBooleanValue: true<d:BooleanValue m:type="Boolean">true</d:BooleanValue>
Edm.ByteUnsigned 8-bit integerByteValue: 1<d:ByteValue m:type="Byte">1</d:ByteValue>
Edm.DateDate without a time-zone offsetDateValue: “2010-02-26”<d:DateValue m:type="DateTime">
2010-02-26</d:DateValue>
Edm.DateTimeOffsetDate and time with a time-zone offset, no leap secondsDateTimeOffsetValue: “2002-10-10T17:00:00Z”
DateTimeOffsetValue: “2010-02-26T17:08:53.0900752-08:00”
<d:DateTimeOffsetValue m:type="DateTimeOffset">
2002-10-10T17:00:00Z</d:DateTimeOffsetValue>
<d:DateTimeOffsetValue m:type="DateTime">
2010-02-26T17:08:53.0900752-08:00</d:DateTimeOffsetValue>
Edm.DecimalNumeric values with fixed precision and scaleDecimalValue: 3.3<d:DecimalValue m:type="Decimal">3.3</d:DecimalValue>
Edm.DoubleIEEE 754 binary64 floating-point number (15-17 decimal digits)DoubleValue: 4.4<d:DoubleValue m:type="Double">4.4</d:DoubleValue>
Edm.DurationSigned duration in days, hours, minutes, and (sub)secondsDurationValue: “P104DT7H50M13.133S”<d:DurationValue m:type="Duration">
P104DT7H50M13.133S</d:DurationValue>
Edm.Guid16-byte (128-bit) unique identifierGuidValue: 223b00d9-e617-4a80-bf18-31bfd6e8e312<d:GuidValue m:type="Guid">
223b00d9-e617-4a80-bf18-31bfd6e8e312</d:GuidValue>
Edm.Int16Signed 16-bit integerInt16Value: 1<d:Int16Value m:type="Int16">1</d:Int16Value>
Edm.Int32Signed 32-bit integerInt32Value: 2<d:Int32Value m:type="Int32">2</d:Int32Value>
Edm.Int64Signed 64-bit integerInt64Value: 3<d:Int64Value m:type="Int64">3</d:Int64Value>
Edm.SByteSigned 8-bit integerSByteValue: 13<d:SByteValue m:type="SByte">13</d:SByteValue>
Edm.SingleIEEE 754 binary32 floating-point number (6-9 decimal digits)SingleValue: 4.4<d:SingleValue m:type="Single">4.4</d:SingleValue>
Edm.StreamBinary data streamStreamValue: “FRwvAAI...”<d:StreamValue m:type=”Stream”>FRwvAAI…</d:StreamValue>
Edm.StringSequence of UTF-8 charactersStringValue: “Beverages”

<d:StringValue>Beverages</d:StringValue>

<d:StringValue m:type="String">Beverages</d:StringValue>

Edm.TimeOfDayClock time 00:00-23:59:59.999999999999TimeOfDayValue: "07:59:59.999"<d:TimeOfDayValue m:type=”TimeOfDay”>07:59:59.999</d:TimeOfDayValue>
Edm.GeographyAbstract base type for all Geography typesGeographyValue: “SRID=4326;POINT (10.22 10)”<d:GeographyValue m:type="Geography">
SRID=4326;POINT (10.22 10)</d:GeographyValue>
Edm.GeographyPointA point in a round-earth coordinate systemGeographyPointValue: “SRID=4326;POINT (10.22 10)“<d:GeographyPointValue m:type="GeographyPoint
">SRID=4326;POINT (10.22 10)</d:GeographyPointValue>
Edm.GeographyLineStringLine string in a round-earth coordinate systemGeographyLineStringValue: “SRID=4326;LINESTRING (-140.4 49.98, -177.6 77)”<d:GeographyLineStringValue m:type="GeographyLineString"
>SRID=4326;LINESTRING (-140.4 49.98, -177.6 77)</d:GeographyLineStringValue>
Edm.GeographyPolygonPolygon in a round-earth coordinate systemGeographyPolygonValue: “SRID=4326;POLYGON ((-110 33.1, -110.15 35.97, 87.75 11.45, -110 33.1),
(-110 35.97, -110.15 36.97, 23.18 45.23, -110 35.97))”
<d:GeographyPolygonValue m:type="GeographyPolygon">
SRID=4326;POLYGON ((-110 33.1, -110.15 35.97, 87.75 11.45, -110 33.1), (-110 35.97, -110.15 36.97, 23.18 45.23, -110 35.97))</d:GeographyPolygonValue>
Edm.GeographyMultiPointCollection of points in a round-earth coordinate systemGeographyMultiPointValue: “SRID=4326;MULTIPOINT ((11.2 10.2), (11.6 11.9))”<d:GeographyMultiPointValue m:type="GeographyMultiPoint">
SRID=4326;MULTIPOINT ((11.2 10.2), (11.6 11.9))</d:GeographyMultiPointValue>
Edm.GeographyMultiLineStringCollection of line strings in a round-earth coordinate systemGeographyMultiLineStringValue: “SRID=4326;MULTILINESTRING ((11.2 10.2, 11.6 11.9), (17.2 16.2, 19.6 18.9))”<d:GeographyMultiLineStringValue m:type="GeographyMultiLineString">
SRID=4326;MULTILINESTRING ((11.2 10.2, 11.6 11.9), (17.2 16.2, 19.6 18.9))</d:GeographyMultiLineStringValue>
Edm.GeographyMultiPolygonCollection of polygons in a round-earth coordinate systemGeographyMultiPolygonValue: “SRID=4326;MULTIPOLYGON (((11.2 10.2, 11.6 11.9, 87.75 11.45, 11.2 10.2), (17.2 16.2, 19.6 18.9, 87.75 11.45, 17.2 16.2)))”<d:GeographyMultiPolygonValue m:type="GeographyMultiPolygon">
SRID=4326;MULTIPOLYGON (((11.2 10.2, 11.6 11.9, 87.75 11.45, 11.2 10.2), (17.2 16.2, 19.6 18.9, 87.75 11.45, 17.2 16.2))) </d:GeographyMultiPolygonValue>
Edm.GeographyCollectionCollection of arbitrary Geography valuesGeographyCollectionValue: “SRID=4326;GEOMETRYCOLLECTION (POINT (-12 -19.99))”<d:GeographyCollectionValue m:type="GeographyCollection">
SRID=4326;GEOMETRYCOLLECTION (POINT (-12 -19.99))</d:GeographyCollectionValue>
Edm.GeometryAbstract base type for all Geometry typesGeometryValue: “SRID=0;POINT (10 10.22)”<d:GeometryValue m:type="Geometry">SRID=0;POINT (10 10.22)</d:GeometryValue>
Edm.GeometryPointfPoint in a flat-earth coordinate systemGeometryPointValue: “SRID=0;POINT (10 10.22)”<d:GeometryPointValue m:type="Geometry">SRID=0;POINT (10 10.22)</d:GeometryPointValue>
Edm.GeometryLineStringLine string in a flat-earth coordinate systemGeometryLineStringValue: “SRID=0;LINESTRING (49.98 -140.4, 77 -177.6)”<d:GeometryLineStringValue m:type="GeometryLineString">
SRID=0;LINESTRING (49.98 -140.4, 77 -177.6)</d:GeometryLineStringValue>
Edm.GeometryPolygonPolygon in a flat-earth coordinate systemGeometryPolygonValue: “SRID=0;POLYGON ((33.1 -110, 35.97 -110.15, 11.45 87.75, 33.1 -110), (35.97 -110, 36.97 -110.15, 45.23 23.18, 35.97 -110))”<d:GeometryPolygonValue m:type="GeometryPolygon">
SRID=0;POLYGON ((33.1 -110, 35.97 -110.15, 11.45 87.75, 33.1 -110), (35.97 -110, 36.97 -110.15, 45.23 23.18, 35.97 -110))</d:GeometryPolygonValue>
Edm.GeometryMultiPointCollection of points in a flat-earth coordinate systemGeometryMultiPointValue: “SRID=0;MULTIPOINT ((10.2 11.2), (11.9 11.6))”<d:GeometryMultiPointValue m:type="GeometryMultiPoint">
SRID=0;MULTIPOINT ((10.2 11.2), (11.9 11.6))</d:GeometryMultiPointValue>
Edm.GeometryMultiLineStringCollection of line strings in a flat-earth coordinate systemGeometryMultiLineStringValue: SRID=0;MULTILINESTRING ((10.2 11.2, 11.9 11.6), (16.2 17.2, 18.9 19.6))<d:GeometryMultiLineStringValue m:type="GeometryMultiLineString">
SRID=0;MULTILINESTRING ((10.2 11.2, 11.9 11.6), (16.2 17.2, 18.9 19.6))</d:GeometryMultiLineStringValue>
Edm.GeometryMultiPolygonCollection of polygons in a flat-earth coordinate systemGeometryMultiPolygonValue: SRID=0;MULTIPOLYGON (((10.2 11.2, 11.9 11.6, 11.45 87.75, 10.2 11.2), (16.2 17.2, 18.9 19.6, 11.45 87.75, 16.2 17.2)))<d:GeometryMultiPolygonValue m:type="GeometryMultiPolygon">
SRID=0;MULTIPOLYGON (((10.2 11.2, 11.9 11.6, 11.45 87.75, 10.2 11.2), (16.2 17.2, 18.9 19.6, 11.45 87.75, 16.2 17.2)))</d:GeometryMultiPolygonValue>
Edm.GeometryCollectionCollection of arbitrary Geometry valuesGeometryCollectionValue: SRID=0;GEOMETRYCOLLECTION (POINT (-19.99 -12)<d:GeometryCollectionValue m:type="GeometryCollection">
SRID=0;GEOMETRYCOLLECTION (POINT (-19.99 -12)</d:GeometryCollectionValue>

Table 1: OData Primitive Types

A navigation property, however, is not a primitive, which defines the relationship between two entity types. When we look at a Person entry with requests odata.metadata=full, this gives us what we need to know in order to navigate:

URL: http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')?$format=application/json;odata.metadata=full
Response:
{
    @odata.context: "http://services.odata.org/V4/TripPinServiceRW/$metadata#People/$entity",
    @odata.type: "#Microsoft.OData.SampleService.Models.TripPin.Person",
    ...
    @odata.editLink: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')",
    UserName: "russellwhyte",
    FirstName: "Russell",
    LastName: "Whyte",
    ...
    Friends@odata.associationLink: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends/$ref",
    Friends@odata.navigationLink: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends",
    ...
}

With the metadata explicitly in hand, we’ve got all the information we need to know to create and interpret entries of type Person:

  • To get to the related friends, we follow the relative URL in the Friends@odata.navigationLink to a feed document which can have any number of friends in it. We know it’ll take us to friends because of the rel=".../Friends”.
  • We know that the properties in the content element map to properties on the Person entity type and what types each of the properties is. If it’s not specified, the default type is Edm.String.

And how do we know that the data in this entry is a Person and not something else? OData uses the @odata.type annotation, e.g. "Microsoft.OData.SampleService.Models.TripPin.Person", to specify the specific type in that CSDL document.

However, while I have explained the link element to find related friends on a given person, I still haven't told you how I know to construct a URL to retrieve a specific person with which to work. For that, we'll need to know about another major OData feature: the query language defined by OData URL Conventions x.

Query Navigation

Given the amount of time we’ve spent poring over XML documents defining services, feeds, entries and metadata, you may have noticed that the “edit” link of each entry in our TripPin sample contains the name of the database table and the unique identifier from that table, e.g.

http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')

In fact, OData provides an entire query language directly in the URL format you use to retrieve entries from a service. This is very different from AtomPub that makes no statement about the format of the entry links, promising only that you can follow an “edit” link to the full data representation of the entry suitable for editing. This guarantee is also true in OData, but OData allows you to form URLs based on what you know (and can discover) about the underlying data. For example, you can start at the top level service document and keep drilling, e.g.

URL: http://services.odata.org/V4/TripPinServiceRW/
Response:
{
   @odata.context: "http://services.odata.org/V4/TripPinServiceRW/$metadata",
   value:
   [
      {
        name: "Photos",
        kind: "EntitySet",
        url: "Photos"
      },
      {
        name: "People",
        kind: "EntitySet",
        url: "People"
      },
      {
        name: "Airlines",
        kind: "EntitySet",
        url: "Airlines"
      },
      {
        name: "Airports",
        kind: "EntitySet",
        url: "Airports"
      },
      {
        name: "Me",
        kind: "Singleton",
        url: "Me"
      },
      {
        name: "GetNearestAirport",
        kind: "FunctionImport",
        url: "GetNearestAirport"
      }
   ]
}

Using the url value, we can then go to:

URL: http://services.odata.org/V4/TripPinServiceRW/People?$format=application/json;odata.metadata=full
Response:
{
    @odata.context: "http://services.odata.org/V4/(S(ck3fzk3dze0kmzjcruxiz31i))/TripPinServiceRW/$metadata#People",
    @odata.nextLink: "http://services.odata.org/V4/TripPinServiceRW/People?%24format=application%2fjson%3bodata.
    metadata%3dfull&%24skiptoken=8",
    value:
    [
        {
            @odata.type: "#Microsoft.OData.SampleService.Models.TripPin.Person",
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')",
            @odata.etag: "W/"08D17DBDFB9CCAAC"",
            @odata.editLink: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')",
            UserName: "russellwhyte",
            FirstName: "Russell",
            LastName: "Whyte",
            ...
            Friends@odata.associationLink: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends/$ref",
            Friends@odata.navigationLink: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends",
            ...
        },
        ...
    ]
}

We can take the @odata.editLink and pull just the entry document for each of the entities in the response. However, if you know the identifier for any of the entries, you can compose that with the name of the collection inside parentheses and get yourself that entry, as we’ve already seen, as well as being able to get to the related Friends:

URL: http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends?$format=application/json;odata.metadata=full
Response:
{
    @odata.context: "http://services.odata.org/V4/(S(ck3fzk3dze0kmzjcruxiz31i))/TripPinServiceRW/$metadata#People",
    value:
    [
        {
            @odata.type: "#Microsoft.OData.SampleService.Models.TripPin.Person",
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')",
            @odata.etag: "W/"08D17DBDFB9CCAAC"",
            @odata.editLink: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')",
            UserName: "scottketchum",
            FirstName: "Scott",
            LastName: "Ketchum",
            ...
            Friends@odata.associationLink: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')/Friends/$ref",
            Friends@odata.navigationLink: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')/Friends",
            Trips@odata.associationLink: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')/Trips/$ref",
            Trips@odata.navigationLink: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')/Trips",
            ...
        },
        ...
    ]
}

Again, we’re given a collection of entities that associated with this relationship, in this case the friends associated with the specified person. And from here we can see that we can get back to the person for each friend, as well as to the friends, trips and photos associated with this friend. Also, notice that the link title is singular Person, showing us the other end of our one-to-many relationship.

Backing up to the person, in addition to navigating through relationships, we can also pull out specific properties from the content:

URL: http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/FirstName
Response:
{
   @odata.context: "http://services.odata.org/V4/TripPinServiceRW/$metadata#People('russellwhyte')/FirstName",
   value: "Russell"
}

In this case, there is no Atom or AtomPub document to be seen – this is just the document that contains the specific piece of information we’re after. You can go ever further and just ask for the value itself:

URL: http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/FirstName/$value
Response:
Russell

Here, there’s no JSON or XML at all – just the raw value.

Likewise, if you’d like a count of entities in a collection instead of the entities themselves, you can use $count, e.g.

URL: http://services.odata.org/V4/TripPinServiceRW/People/$count
Response:
20

Finally, if you’d like to get the set of navigation links between one entry and another without getting the set of related entries, you can do that with $ref, e.g.

URL: http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends/$ref
Reponse:
{
    @odata.context: "http://services.odata.org/V4/TripPinServiceRW/$metadata#Collection($ref)",
    value:
    [
        {
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends('scottketchum')"
        },
        {
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends('ronaldmundy')"
        },
        {
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends('javieralfred')"
        },
        {
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')/Friends('angelhuffman')"
        }
    ]
}

Here, we’re not getting back a feed or an entry, but rather a set of links for the Friend entries associated with a specific Person.

So, if you happen to know the unique identifiers of the things you’re after, you can navigate to them directly or anything related to them, whether it’s a set of associated entries in a feed, a property, the value of a property or even the count of entries, simply by using the appropriate URL format (and you can keep going arbitrarily deep).

Query Options

In addition to the value and the count, OData defines other URL options to customize the output of your query. For example, $orderby allows you to order a query result by one or more properties in ascending or descending order:

http://services.odata.org/V4/TripPinServiceRW/People?$orderby=FirstName desc

In this case, we’re ordering by the FirstName property of each person in descending order. If we want to combine query options, we can do so using the standard “&” query string operator:

http://services.odata.org/V4/TripPinServiceRW/People?$orderby=FirstName desc&$top=4

In this case, we’re shorting and then pulling off the top 4.

Table 2 shows the full set of query options.

Query OptionDescription
$filterA Boolean expression for whether a particular entry should be included in the feed, e.g. Categories?$filter=CategoryName eq 'Produce'. The Query Expression section describes OData expressions.
$expandExpand related resources in line with the retrieved resources, e.g. Categories/$expand=Products would expand Product data in line with each Category entry.
$selectLimit the properties on each entry to just those requested, e.g. Categories?$select=CategoryName,Description.
$orderbyOne or more comma-separated expressions with an optional “asc” (the default) or “desc” depending on the order you’d like the values sorted, e.g. Categories?$orderby=CategoryName desc.
$topReturn entries from the top of the feed, e.g. Categories?$top=4.
$skipHow many entries you’d like to skip, e.g. Categories?$skip=4.
$countA Boolean value of true or false request a count of the matching resources included with the resources in the response, e.g. Categories?$count=true
$searchA free-text search expression xi to match for whether a particular entry should be included in the feed, e.g. Categories?$search=blue OR green
$formatReturn the response in particular format xii without access to request headers for standard content-type negotiation

Table 2: OData Query Options

And as if that weren’t enough, you can ask an OData service to evaluation expressions for you.

Filter Expressions

OData packs a lot of power into the URL format for querying. We’ve already seen that it uses $-prefixed keywords for special operations and query options. The expression language will similarly look a little strange because characters common in other expression languages, like + and &, already have meanings in the URL syntax. To avoid stepping on URL toes, OData defines an expression language for filtering based on words instead of symbols, e.g.

http://services.odata.org/V4/TripPinServiceRW/People?$filter=(length(UserName) add 4) eq 16

This example returns a single entry feed where the length of UserName is 12.

The filter expressions built into OData are listed in Table 3.

OperationExampleDescription
Grouping(x add 4) eq 3Used to make clear or override default operator precedence (which I can never remember anyway).
Method callstartswith(Description, “Bread”)Call a built in method.
Negate-xChange numeric sign.
Notnot xLogical not.
Andx and yConditional and.
Orx or yConditional or.
Multiplyx mul y 
Dividex div y 
Modulox mod y 
Addx add y 
Subtractx sub y 
Less thanx lt y 
Greater thanx gt y 
Less than or equalx le y 
Greater than or equalx ge y 
Equalsx eq y 
Not equalsx ne y 
Type coercioncast(T), cast(x, T)Perform a type coercion if possible.
Type comparisonisof(T), isof(x, T)Whether targeted instance can be converted to the specified type.
String literal'a string' 
Date literal2012-12-03 
Decimal literal4.5 
GUID literal1225c695-cfb8-4ebb-aaaa-80da344efa6a 
Single literal4.5
4.5e9
 
Double literal4.5
4.5e21
 
Integer 16 literal16
-16
 
Integer 32 literal32
-32
 
Integer 64 literal64
-64
 
Binary literalT0RhdGE 
Null literalnullDistinguished value meaning the absence of a value.
Byte literalFF 
Boolean literaltrue
false
 
TimeOfDay literal07:59:59.999 
DateTimeOffset literal2012-12-03T07:16:23Z 

Table 3: OData Expressions

You’ll notice as part of the list of operations that there is one for method calls, which is also part of the OData query expression language for $filter.

Built-in Methods

OData defines several methods to be used as part of your query strings, e.g.

http://services.odata.org/V4/TripPinServiceRW/People/?$filter=startswith(FirstName,’Kei’)

As you might imagine, this example filters the results based on the first three letters of the FirstName of each Person. The full list of methods is shown in Table 4.

MethodExampleDescription
containscontains(x,‘foo’)Whether the second parameter string value is a substring of the first parameter string value
endswithendswith(x, 'foo')Whether the end of the first parameter value matches the second parameter value.
startswithstartswith(x, 'foo')Whether the beginning of the first parameter values matches the second parameter value.
lengthlength(x)The number of characters in the specified parameter value.
indexofindexof(x, 'foo')Index of the first occurrence of the second parameter value in the first parameter value or -1 otherwise.
substring

substring(x, 1)

substring(x, 1, 2)

String value starting at the character index specified by the second parameter value in the first parameter string value. Selecting length is specified by the third parameter.
tolowertolower(x)String value with the contents of the parameter value converted to lower case.
touppertoupper(x)String value with the contents of the parameter value converted to upper case.
trimtrim(x)String value with the contents of the parameter value with all leading and trailing white-space characters removed.
concatconcat(x, ‘foo’)String value which is the first and second parameter values merged together with the first parameter value coming first in the result.
yearyear(d)The year component value of the parameter value.
monthmonth(d)The month component value of the parameter value.
dayday(d)The day component value of the parameter value.
hourhour(d)The hour component value of the parameter value.
minuteminute(d)The minute component value of the parameter value.
secondsecond(d)The second component value of the parameter value.
fractionalsecondsfractionalseconds(d)The fractional second component of the parameter value
datedate(d)The date part of the parameter value
timetime(d)The time part of the parameter value
totaloffsetminutestotaloffsetminutes(d)The signed number of minutes in the time zone offset part of the parameter value
nownow()The current point in time (date and time with time zone)
maxdatetimemaxdatetime()The latest possible point in time
mindatetimemindatetime()The earliest possible point in time
totalsecondstotalseconds(d)The duration of the value in total seconds, including fractional seconds
roundround(x)The nearest integral value to the parameter value, following the rules defined in IEEE754-2008.
floorfloor(x)The largest integral value less than or equal to the parameter value, following the rules defined in IEEE754-2008.
ceilingceiling(x)The smallest integral value greater than or equal to the parameter value, following the rules defined in IEEE754-2008.
isof

isof(type)

isof(x, type)

Whether the current instance or the object referred by the first parameter is assignable to the type specified.
cast

cast(type)

cast(x, type)

Cast the current instance or the object referred by the first parameter to the type specified.
geo.distancegeo.distance(x, y)The shortest distance between the two points in the coordinate reference system signified by the two points’ SRIDs
geo.intersectsgeo.intersects(x, y)Whether the point specified by the first parameter lies within the interior or on the boundary of the polygon specified by the second parameter.
geo.lengthgeo.length(x)The total length of its line string parameter in the coordinate reference system signified by its SRID.

Table 4: OData QueryMethods

Lambda Operators

OData also defines two operators that evaluate a Boolean expression on a collection. Both must be prepended with a navigation path that identifies a collection. The argument of a lambda operator is a lambda variable name followed by a colon (:) and a Boolean expression that uses the lambda variable name to refer to properties of the related entities identified by the navigation path. e.g.

http://services.odata.org/V4/TripPinServiceRW/People?$filter=Friends/any(f:f/FirstName eq ‘Keith’)

As you might imagine, this example filters the results based on the Friends’ first name of each Person. The two operators are shown in Table 5.

MethodExampleDescription
anyProducts/any(p:p/UnitPrice gt 20)Whether the expression is true for any member of the collection. This operator without an argument returns true if the collection is not empty.
allProducts/all(p:p/UnitPrice ge 10)Whether the expression is true for all members of the collection.

Table 5: OData Query Lambda Operators

Functions

In addition to the entities and query methods, both of which you can use as part of $filter queries, you can also form requests to functions exposed from a service endpoint. If you look at the CSDL (via $metadata), you can see that functions are denoted differently than entities:

<edmx:Edmx ...>
   <edmx:DataServices>
      <Schema ... Namespace="Microsoft.OData.SampleService.Models.TripPin">
        ...
         <Function Name="GetInvolvedPeople" IsBound="true" IsComposable="true">
            <Parameter Name="trip" Type="Microsoft.OData.SampleService.Models.TripPin.Trip" Nullable="false" />
            <ReturnType Type="Collection(Microsoft.OData.SampleService.Models.TripPin.Person)" Nullable="false" />
         </Function>
         <Function Name="GetNearestAirport" IsComposable="true">
            <Parameter Name="lat" Type="Edm.Double" Nullable="false" />
            <Parameter Name="lon" Type="Edm.Double" Nullable="false" />
            <ReturnType Type="Microsoft.OData.SampleService.Models.TripPin.Airport" Nullable="false" />
         </Function>
         <EntityContainer Name="DefaultContainer">
            <FunctionImport Name="GetNearestAirport" 
               Function="Microsoft.OData.SampleService.Models.TripPin.GetNearestAirport" EntitySet="Airports" 
                IncludeInServiceDocument="true">
               <Annotation Term="Org.OData.Core.V1.ResourcePath"
                 String="Microsoft.OData.SampleService.Models.TripPin.GetNearestAirport" />
            </FunctionImport>
            ...
         </EntityContainer>
      </Schema>
   </edmx:DataServices>
</edmx:Edmx>

The difference between a function and an entity is that an entity has up to four operations available to it, depending on how you call it, i.e. create, read, update and delete (aka CRUD). A function, on the other hand, is just one operation with return data and without observable side effects to be invoked with HTTP method GET.

In this case, one of our functions is named GetInvolvedPeople, which is bound to an entity Trip. Another is named GetNearestAirport as FunctionImport.

The return type of GetInvolvedPeople is a collection of Person, so in the event of a successful result, we’ll see a response like this:

URL: http://services.odata.org/V4/TripPinServiceRW/Me/Trips(1001)/Microsoft.OData.SampleService.Models.TripPin.GetInvolvedPeople
Response:
{
    @odata.context: "http://services.odata.org/V4/TripPinServiceRW/$metadata#People",
    value:
    [
        {
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')",
            @odata.etag: "W/"08D17DC88B761B0E"",
            @odata.editLink: "http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')",
            UserName: "russellwhyte",
            FirstName: "Russell",
            LastName: "Whyte",
            ...
        },
        {
            @odata.id: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')",
            @odata.etag: "W/"08D17DC88B761B0E"",
            @odata.editLink: "http://services.odata.org/V4/TripPinServiceRW/People('scottketchum')",
            UserName: "scottketchum",
            FirstName: "Scott",
            LastName: "Ketchum",
            ...
        }
    ]
}

Notice the format for our output is just like query for entities because that’s the mapping the OData specification mandates.

However, there are lots of operations that OData doesn’t mandate as part of the specification, which is one of the reasons why functions are handy – they can do things on the server-side that aren’t part of the spec. In some cases, just like the built in query operations, you’ll want to provide arguments to these functions, whose parameters are always packed into the query string syntax like so:

http://services.odata.org/V4/TripPinServiceRW/GetNearestAirport(lat=1.0,lon=1.0)

At this point, it’s clear why functions look like queries, even including the ability to pass parameters and filter the results. And, like queries, function results come back as JSON by default.

The return type of GetNearestAirport is an Airport, so the response will be like this:

URL: http://services.odata.org/V4/TripPinServiceRW/GetNearestAirport(lat=1.0,lon=1.0)
Response:
{
    @odata.context: "http://services.odata.org/V4/TripPinServiceRW/$metadata#Airports/$entity",
    @odata.id: "http://services.odata.org/V4/TripPinServiceRW/Airports('LIRA')",
    @odata.editLink: "http://services.odata.org/V4/TripPinServiceRW/Airports('LIRA')",
    IcaoCode: "LIRA",
    Name: "Rome Ciampino Airport",
    IataCode: "CIA",
    Location:
    {
        Address: "Via Appia Nuova, 1651",
        City:
        {
            CountryRegion: "Italy",
            Name: "Rome",
            Region: ""
        },
        Loc:
        {
            type: "Point",
            coordinates:
            [
                12.5947222222222,
                41.7991666666667
            ],
            crs:
            {
                type: "name",
                properties:
                {
                    name: "EPSG:4326"
                }
            }
        }
    }
}

Actions

Other than Functions, there’re also Actions can be exposed by OData service that may return data and may have side effects when invoked with HTTP method POST. Actions look very similar to Functions in CSDL (via $metadata):

<edmx:Edmx ...>
   <edmx:DataServices>
      <Schema ... Namespace="Microsoft.OData.SampleService.Models.TripPin">
         ...
         <Action Name="ResetDataSource" />
         <Action Name="ShareTrip" IsBound="true">
            <Parameter Name="person" Type="Microsoft.OData.SampleService.Models.TripPin.Person" Nullable="false" />
            <Parameter Name="userName" Type="Edm.String" Nullable="false" />
            <Parameter Name="tripId" Type="Edm.Int32" Nullable="false" />
         </Action>
         <EntityContainer Name="DefaultContainer">
            <ActionImport Name="ResetDataSource" Action="Microsoft.OData.SampleService.Models.TripPin.ResetDataSource" />
            ...
         </EntityContainer>
      </Schema>
   </edmx:DataServices>
</edmx:Edmx>

In this case, we have ResetDataSource as ActionImport without arguments and return data, and ShareTrip bound to a person.

URL: http://services.odata.org/V4/TripPinServiceRW/ResetDataSource
Response:
HTTP/1.1 204 No Content
Cache-Control: no-cache
Server: Microsoft-IIS/8.0
OData-Version: 4.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Set-Cookie: ARRAffinity=855507b8b588a9631bbfe6c471e2a39ff121f557e0cdc9b27fa49385eca5518a;Path=/;Domain=services.odata.org
Date: Mon, 04 Aug 2014 09:09:04 GMT
URL: http://services.odata.org/V4/TripPinServiceRW/Me/Microsoft.OData.SampleService.Models.TripPin.ShareTrip
Payload:
{
    "userName":"ronaldmundy",
    "tripId":5
}

Response:
HTTP/1.1 204 No Content
Cache-Control: no-cache
Server: Microsoft-IIS/8.0
OData-Version: 4.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Set-Cookie: ARRAffinity=855507b8b588a9631bbfe6c471e2a39ff121f557e0cdc9b27fa49385eca5518a;Path=/;Domain=services.odata.org
Date: Mon, 04 Aug 2014 09:14:01 GMT

Notice that there are return codes in the 200s, so they were successful calls, but they’re 204, indicating that there were no content returned.

JSON Format

By default, data comes back as JSON, the JavaScript Object Notation. The JSON format is especially interesting if you’re using JScript from inside the browser. Even better, if you’re using a modern JavaScript library, like jQuery, you can just form a request and the Accept header will be set and the JSON data parsed automatically, integrating the results of an OData request seamlessly into your JavaScript program, like so:

<html>
  <head>
    <title>OData JSON Test</title>
    <script src="Scripts/jquery-2.1.1.min.js" type="text/javascript"></script>
  </head>
  <body>
    <script type="text/javascript">
        $(document).ready(function () {
            $("#foo").text("fetching...");
            var url = "http://services.odata.org/V4/TripPinServiceRW/People";
            $.getJSON(url, function (result) {
              $("#foo").text(result.value[0].FirstName);
            });
        });
    </script>
    <p id="foo">loading page...</p>
  </body>
</html>

Here, we’re using jQuery’s getJSON function on a URL from our sample data service. When the results come back, our function will be called and we’ll set the text of an HTML <p> element to the first name of the first Person that’s returned. The results are similarly simple, as Figure 1 shows.

Figure 1: OData in JSON from the browser

The call to getJSON translates into a call on the browser’s underlying XMLHttpRequest object, which yields an HTTP request/response pair like this one:

Request:
GET http://services.odata.org/V4/TripPinServiceRW/People HTTP/1.1
Accept-Language: en-US,en;q=0.8,zh-Hans-CN;q=0.5,zh-Hans;q=0.3
Accept: application/json, text/javascript, */*; q=0.01
Accept-Encoding: gzip, deflate, peerdist
User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; Trident/7.0; rv:11.0) like Gecko
Connection: Keep-Alive
Host: services.odata.org
Pragma: no-cache
Cookie: ...

Response: 
HTTP/1.1 200 OK
Cache-Control: no-cache
Content-Length: 4301
Content-Type: application/json;odata.metadata=minimal;odata.streaming=true;IEEE754Compatible=false;charset=utf-8
Vary: Accept-Encoding
Server: Microsoft-IIS/8.0
OData-Version: 4.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Date: Tue, 05 Aug 2014 02:46:18 GMT

{
    "@odata.context":"http://services.odata.org/V4/ TripPinServiceRW/$metadata#People",
    "@odata.nextLink":"http://services.odata.org/V4/TripPinServiceRW/People?%24skiptoken=8",
    "value":
    [
        {
            "@odata.id":"http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')",
            "@odata.editLink":"http://services.odata.org/V4/TripPinServiceRW/People('russellwhyte')",
            "UserName":"russellwhyte",
            "FirstName":"Russell",
            "LastName":"Whyte",
            ...
        },
        ...
    ]
}

If you’re unfamiliar with the JSON format, it’s just the JavaScript data initialization format. In this case, the HTTP response content is parsed and turned into data accessible directly from JavaScript. Notice that the top-level root of the data returned in this OData response is “value” (and it always will be, according to the OData specification), so I can just reach into the array of entities returned and dig through the properties as you’d expect, e.g. UserName, FirstName, LastName, etc.

You’ll also notice several things of interest that you won’t see in normal JSON data. The first are the @odata.id and @odata.editLink properties, which provides the URL to the type data for the entity in case you’d like access to or edit it in your JavaScript program. The second is @odata.etag property, which is an opaque string value that can be used in a subsequent request to determine if the value of the entity has changed. It’s optional in the response.

As you can see, the jQuery getJSON function does everything you need to get results from a data service without any muss or fuss. Similarly, the OData community has built a number of client libraries for making it just as easy to get to OData results as in JavaScript.

Client Libraries

While it’s true that OData is accessible from any environment that supports HTTP and JSON (or XML), there are a number of OData client libraries across various platforms to make you even more productive:

  • Microsoft .NET Framework Client library for V4 and OData Client Code Generator are available as separate downloads for .NET.
  • C++: ODataCpp provides client request handling functionality and client side proxy generation capabilities.
  • Java: Maintained at Apache Olingo. Currently support OData V2, will support OData V4 soon.
  • JavaScript: The version that supports OData V3 available at Nuget, while the V4 version is also at Olingo and will be released soon.
  • Power Query: Already support consuming OData V3 services. Will support V4 soon. Available here.

I won’t bother you by showing you examples of every OData client library or tool, especially as more are being developed all the time, but you can take a detailed look at what it takes to create a .NET client using the .NET Data Services client library in our team blog here.

Server Implementations

In the same way that there are several client toolkits for consuming OData, there are several server-side implementations as well:

Again, I won’t make you look at every one of the server implementations of OData, but let’s take a quick look at exposing a database using ASP.NET Web API. In fact, you can get a simple but complete walkthrough here.

As you can see, ASP.NET Web API makes a handy server and client-side library for managing and manipulating your OData services.

Where are we?

As you’ve seen, the Open Data protocol (OData) is an application of Atom and JSON for typed CRUD operations and rich queries in a standardized way that enables a large number of services to expose their data, relational or not, and a large number of clients to consume it. While this technology has its roots in Microsoft technology, all the specifications are released under the Open Source Promise to enable anyone to play in this space, which includes Microsoft tools like Visual Studio, Excel and SharePoint, as well as non-Microsoft solutions like JavaScript and so on. Data on the web wants to be open. OData is here to help us make it so.

Microsoft is conducting an online survey to understand your opinion of the MSDN Web site. If you choose to participate, the online survey will be presented to you when you leave the MSDN Web site.

Would you like to participate?