ODATA

OData define as abstract data model and a protocol that let any client access information exposed by any data source. The purpose of Open Data Protocol is to provide REST based protocol for CRUD style operation against resources exposed by data services.

OData is published by Microsoft under the Open Specification Promise so that anyone that wants to can build servers, clients or tools without royalties or restrictions.

You can access these resources via URL. OData protocol specifies how you can access data via HTTP queries. OData can be used to access table like structures similar to the way SQL does.

OData allows mixing and matching client and data sources. Some of the most important examples of data sources that support OData today are:

• Custom applications: Rather than creating its own mechanism to expose data, an application can instead use OData. Facebook, Netflix, and eBay all expose some of their information via OData today, as do a number of custom enterprise applications. To make this easier to do, OData libraries are available that let .NET Framework and Java applications act as data sources.

• Cloud storage: OData is the built-in data access protocol for tables in Microsoft's Windows Azure, and it's supported for access to relational data in SQL Azure as well. Using available OData libraries, it's also possible to expose data from other cloud platforms, such as Amazon Web Services.

• Content management software: For example, SharePoint 2010 and Web nodes both have built-in support for exposing information through OData.

• Windows Azure Marketplace Data Market: This cloud-based service for discovering, purchasing, and accessing commercially available datasets lets applications access those datasets through Odata.

The query of OData return complete lists of resources in the Xml-Atom format. In other words, OData relies on AtomPub is the protocol to provide not only getting Atom data, but also creating it, updating it and deleting it.

Instead of the default XML-Atom format OData also support JSON format. To get the result in JSON format if you add parmeter $format=json as in the following examples.

• http://services.odata.org/OData/OData.svc/CategoryTable?$format=json

• http://services.odata.org/OData/OData.svc/ProductTable?$format=json

• http://services.odata.org/OData/OData.svc/SupplieTabler?$format=json 

1. Reading Data

   Instead of taking all information you can create URL queries that find information about the some specific resources.

• /CategoryTable(1)  fetch category with id 1 

• /CategoryTable(1)/Products  fetch products that are related to category with id 1

• /SupplierTable(1)/Products  fetch products for the supplier with id 1

• /ProductTable(1)/Supplier/Products  find product with id 1, take its supplier, and return all products provided by this supplier

  2. Projection of fields  

By default, all fields in the resource are returned; however, you can specify subset of fields that you want to be returned in the query. As an example if you want to get only ID and name of product you will use the following query: 

• /ProductTable(1)?$select=ID, Name

3. Filtering

In the previous example you have saw how you can filter resources by ID e.g. /ProductTable(1). However, the same query can be created using the $filter parameter where you explicitly specify that you want to filter products by id e.g./ProductTable?$filter=ID eq 1. System parameter $filter enables you to specify any condition by other properties. As an example you can filter products by name as in the following examples /ProductTable?$filter=Name eq 'Milk'. In this section you can find various usages of $filter system option.

Relational operators

In the previous example you have saw how you can use eq operator for filtering. OData protocol enables you to use other relational operators such as not equal (ne), less than (lt), less or equal (le), greater than (gt), greater or equal (ge). Some examples are:

• /ProductTable?$filter=ID lt 4

• /ProductTable?$filter=ID ge 3

• /ProductTable?$filter=ID ne 2

Logical operators

You can create complex queries using the logical operators and, or, not and brackets. One example of complex query is shown in the following example: 

• /ProductTable?$filter=ID lt 7 and (Name eq 'Milk' or ID gt 3) or not(ID le 4 and ID ge 6)

Arithmetical operations

You can apply standard operators to add (add), subtract (sub), multiply (mul), divide(div), or find remainder(mod). Example of query that returns all products where a total value in stock (unit price * units in stock) is less that 45 (with condition that there are some items in stock) is:

• /ProductTable?$filter=(UnitPrice mul UnitsInStock) lt 45 and UnitsInStock ne 0

Note that currently you can use arithmetical functions only in $filter condition but not in the $select.

Numerical functions

If your properties are numbers you can apply floor, ceiling, and round functions. Example of the query that uses these functions is /ProductTable?$filter=floor(Price) eq 3 or ceiling(Price) eq 3

String functions

There are a lot of string functions you can use in your filter expressions - some of them (with examples) are:

• length(string) /CustomerTable?$filter=length(CompanyName) lt 10

• trim(string), toupper(string), tolower(string) - /ProductTable?$filter=toupper(Name) eq 'MILK'

• substringof(part, text) /CustomerTable?$filter=substringof(Sales,ContactTitle)

• endswith(text, part) /CustomerTable?$filter=endswith(ContactTitle,'Manager')

• startswith(text, part) /CustomerTable?$filter=startswith(ContactTitle,'Sales')