O…Data

Applications use data from many sources – from mainframe to flat files, XML, RDBMS, Excel, Microsoft Access and the list goes on. In parallel, we have many data access APIs/libraries based on the development platform: for C/C++ applications, we have ODBC, for COM/DCOM there is ADO, Java world enjoys JDBC and .NET applications have ADO.NET. That said, in this Internet ubiquitous world, data is increasing exposed via Internet too (a recent entry into this arena is Microsoft “Dallas”). More matured standards/protocols around the Internet, ever evolving network technology stacks and universal data representation schemes are making organizations inclining slowly towards this new model of “data over internet”, both on the consuming as well as publishing ends.



So how do we access data that is exposed over the web in a standard method irrespective of the platform? Enter OData! OData (Open Data) is the ODBC/ADO/JDBC/ADO.NET equivalent for accessing “internet enabled” business data. Independent of operating systems and development platforms, OData is a data access API (application-level protocol, to be specific – remember the OSI layers from good old college days?) based on other standards: HTML, REST and AtomPub (RFC5023) (a data publishing protocol which itself is based on the Atom syndication protocol). OData makes certain extensions to AtomPub to deliver a richer set of functionality. In the diagram below, the left-hand side represents the payload or data exchange format stack and the righ-hand side represents communication stack for OData.



Since all the constituent standards of OData are operating system and programming language independent, you can use OData to consume data (from any OData publisher) from a variety of development environments, OS platforms and devices.




From a consumer’s perspective, OData lets you do resource-specific CRUD operations purely via HTTP/HTTPS using plain XML, Atom or JSON as the payload. On the backend, the OData publisher uses REST model to expose its resources (Consumers, Products, Orders, etc.) and CRUD and service-specific operations on those resources. Of course, the publisher need not support all the CRUD operations on all the resources; it can decide and set appropriate operation permissions on the resources and actions on its own. Let us see an example: assume we have a simple database with a list of law firms each with multiple lawyers associated with it. Each lawyer can have more than one area of practice (such as Divorce, Corporate Compliance and Mortgage). Suppose you want to list the available lawyers and had it been an RDBMS you would write an SQL query like the following:


SELECT * FROM Lawyers;


In OData, you would make a simple HTTP GET request to the OData service as:


http://odata.example.org/Lawyers


Here, http://odata.example.org/ is called the service root URI and Lawyers is the resource path. A service can publish any number of resources at the same root URI. You can pass additional parameters and commands via URI query strings to fintune and shape the data. The service returns the result, list of lawyer entities in this case, in XML format conforming to AtomPub standards. A typical output is shown below highlighting a single entry from the feed (for brievety, many entities and shown collapaed). Note that the output XML does not show the complete the entity information, rather a link to edit that entity is included (append the href attribute value to the xml:base value at the top as in http://localhost:5684/Lawyer.svc/Lawyers(93). A OData client can use this URI to get the editable fields from the publisher). Also note that the content element has the primary key name and its value for the entry.



Extending the above query a bit further, if you want the list of lawyers specializing in accident related law, the typical SQL query would be:


SELECT * FROM Lawyers WHERE PracticeArea=‘Accident’;


The OData HTTP request doing the same job would be:


http://odata.example.org/Lawyers$filter=PracticeArea eq ‘Accident’;


This screenshot below shows a sample feed output for this query (there are two entries satisfying this filter but second one is collapsed):



We can also have more than one filter condition:


SELECT * FROM Lawyers WHERE PracticeArea=‘Accident’ AND City=‘Chicago’;

http://odata.example.org/Lawyers$filter=PracticeArea eq ‘Accident’ and City eq ‘Chicago’


To request specific columns (projection) as in the following:


SELECT LawyerID, Name, Exp FROM Lawyers WHERE Exp > 10;

http://odata.example.org/lawyers$filter=exp gt 10&$select=LawyerID,Name,Exp


The below XML fragment from the actual output highlights the projected properties from the above REST query:



To get a specific entity by its key value:


SELECT * FROM Lawyers WHERE LawyerID=1940

http://odata.example.org/lawyers(1940)/



How about composite primary keys? Pretty simple:


SELECT * FROM Lawyers WHERE LawyerID=1940 AND OfficeID=’IL2159′;

http://odata.example.org/lawyers(lawyerid=1940, officeid=’IL2159′)/


Notice that non-numeric values are enclosed in quotes in the URI query string irrespective of their context. To access a specific property, say birth date, of the entity with ID 491:


SELECT BirthDate FROM Lawyers WHERE LawyerID=491

http://odata.example.org/lawyers(491)/birthdate



If you want just the value without the XML part, append $value to the URI as in:



Here are some interesting URI queries for your curiosity!! J


http://odata.example.org/offices(‘IL2159′)/lawyers/$top=5&$orderby=name desc

http://odata.example.org/offices(‘NYC19′)/lawyers/$filter=(practicearea eq ‘Loans’) and (startswith(name, ‘Nick’) eq true)&$orderby=name desc

http://odata.example.org/offices(‘WAM2′)/lawyers/$filter=(address/city eq ‘Redmond’) and (practicearea eq ‘corporate’ or practicearea eq ‘banking’) and (year(jdate) ge 1999)&$skip=10&$top=10&$select=Name,jdate


These URIs may look ascaringly complex but if you pay close attention to them with a pinch of SQL semantics, they are straight forward and easy to understand! For more filtering options, refer to section 4 of the OData URI Conventions.


By default, the OData service returns results in AtomPub XML format. However, if you would prefer you can get it in JSON too (if the publisher supports it).


http://odata.example.org/Lawyers$filter=City eq ‘Chicago’&$select=LawyerID,Name,City&$format=json


WCF Data Services (formerly ADO.NET Data Services, codename “Astoria”) currently does not support this query string convention for returning JSON data. Instead you have to specify the MIME type for JSON in the Accept header of the HTTP request.


GET http://localhost:5684/Lawyer.svc/Lawyers(7350)/ HTTP/1.1

Accept: application/json

Host: localhost:5684

HTTP/1.1 200 OK

Server: ASP.NET Development Server/10.0.0.0

Date: Wed, 13 Oct 2010 20:21:12 GMT

X-AspNet-Version: 4.0.30319

DataServiceVersion: 1.0;

Content-Length: 458

Cache-Control: no-cache

Content-Type: application/json;charset=utf-8

Connection: Close

 

{

“d” : {

“__metadata”: {

“uri”: “http://localhost:5684/Lawyer.svc/Lawyers(7350)”, “type”: “LawyerOData.LawyerInfo”

}, “LawyerID”: 7350, “Name”: “John Woodlee”, “Exp”: 17, “PracticeArea”: “Employment”, “BirthDate”: “\/Date(20995200000)\/”, “JDate”: “\/Date(423014400000)\/”, “OfficeID”: “MAB1″, “Address”: {

“__metadata”: {

“type”: “LawyerOData.AddressInfo”

}, “AddressInfoID”: 72, “AddressLine”: “Cinema Way”, “City”: “Natick”, “State”: “MA”

}

}

}


Now that we have seen some basic examples of OData requests, let us briefly discuss some of the concepts that make OData as a whole.


Property: is the most granular item in a OData data stream, equivalent to a column in a database table. A property is typed and can be a simple type (integer, float, string, boolean, etc) or a complex type – another entry (see next para). Think of this is a class property which is also a class as in Lawyer. Eample: http://odata.example.org/lawyers(491)/Name refers to the Name property of the lawyer ID 491.


Entry: is a structured item that contains one or more properties, much like a row in a table. An entry can have any number of properties, with a single or composite primary key. Example: http://odata.example.org/lawyers(491)/ represents a lawyer entry whose primary key is 491.


Feed: is a collection of entries and as you might have probably guessed it is conceptually like a database table. Typically OData services expose multiple feeds representing mutliple resource sets. For example, the URI http://odata.example.org/lawyers represents the lawyer feed.


Service Documents: are XML docuents OData services expose for the clients to discover the feeds available. Generally, a OData service makes its service document available at its root URI. A sample service document exposing a single feed Lawywers is shown below:



Metadata: desribes the strucure of the entries available in the service feed. It provides details including the entry name, its constituent property names and their types, primary key and entry relationships in XML form. Think of this as what WSDL is to web services. (If you know Enity Framework, then the metadata is nothing but the CSDL document of the entity data model (EDM)). You can access a service’s metadata by appending $metadata to the service root URI as in http://odata.example.org/$metadata. OData clients such as Visual Studio’s Add Service Reference feature uses this URI to generate the client-side proxy and entity classes. The below screenshot shows the EDM model for the sample OData service I used for this article:



Associations: As mentioned above, an association describes the relationship between two entries (in database terms, foreign key relationships). Association information is available as part of the service metadata. CSDL referes association properties of entries as navigation properties because they are used to navigate from a parent entry to child ones to grand-children and so on. Example: http://odata.example.org/lawyers(491)/addresses represents the list of addresses (feed) that the lawyer ID 491 has.


In OData, associations are called Links. Don’t worry about these terminologies because they are just different names to the same thing depending on the context.


Though OData is new, it is already supported by products. Some of them are Microsoft SharePoint 2010, Windows Azure’s Table Stoage Services and IBM Websphere. Of course you can add OData support for your own data/products by writing your own data service provider (DSP).


If you would like to know more about OData protocol, check out http://www.odata.org. It has everything you need to get started and use the protocol in production environments. It also has some public sample OData URIs that you can play with and understand it better.


I hope this post gave you a broad idea about OData and its querying features. I’ll talk about update, delete and insert features in a future post.