Querying OData.org’s SQL Azure Northwind Sample Data Service
Updated 3/30 and 3/31/2010 with responses to Scott Hanselman’s comment about lack of OData compression in the OData’s AtomPub Protocol Overhead section.
Updated 3/28/2010 with minor fixes and additions.
Alex Barnett, Group Manager, Intuit Partner Platform, offers a recent third-party assessment of OData in his Why OData Matters (IMHO) post of 3/19/2010. Intuit offers a Windows Azure SDK (Beta) for Intuit Partner Platform.
OData.org’s URI query examples use a publicly accessible, abbreviated Northwind version with the OData: URI Conventions. This version includes only the Categories, Products and Suppliers tables:
Note: The OData Team has added to this example a product (Bread), category (Food) and supplier (Exotic Liquids) with an ID of 0. The single Suppliers entry supports e-tag-based concurrency management.
The Complete Northwind Sample Database as an OData Data Source
OData.org offers a publicly accessible full instance of the Northwind sample database, which is more suitable for testing queries that retrieve related items or collections because, unlike AdventureWorksLT, all but one item has a complete complement of related items. That is, all customers except one (FISSA Fabrica Inter. Salchichas S.A.) have related Orders records, all Orders have related Order_Details, all Order_Details have related Products, etc. Another Northwind benefit is that most Microsoft-oriented developers are more familiar with Northwind’s structure than AdventureWorksLT’s:
(Shippers, Suppliers, and Territories, as well as 11 views, are omitted from the preceding capture.)
Important: If you’re using Internet Explorer’s Atom/RSS Feed Reading feature, you must disable it to see AtomPub content formatted by IE 7 and 8’s XML stylesheet. To disable Feeds:
- Choose Tools, Internet Options to open the dialog of the same name.
- Click the Content tab and the Feeds and Web Slices section’s Settings button to open the dialog of the same name.
- Clear the Turn On Feed Reading View checkbox.
- Click OK twice to close the dialogs.
Retrieving Data Source Metadata
Adding the $metadata Query Option to the http://services.odata.org/Northwind/Northwind.svc/ Service Root URI returns the full description of each service collection, including [primary] key Name and data Type and other datatype-dependent attributes, such as Nullable, MaxLength, FixedLength, and Unicode for strings, as well as NavigationProperty attributes for related entries:
Chapter 15, “Using the Entity Framework as a Data Source,” of my Professional ADO.NET 3.5 with LINQ and the Entity Framework book describes ADO.NET Data Services’ URI query syntax, including Query String Options, Comparison, Logical, Arithmetic, and Grouping Operators, as well as Datetime, Math, and Type Functions. The chapter also covers the substitution of the JSON protocol for AtomPub with AJAX.
Shawn Wildermuth reports in his SQL Azure's OData Support post of 3/24/2010 that the following operations and functions aren’t working in SQL Azure as of that date:
- Stored procedures
- Tables/views with no primary key
- Date and Math scalar functions
- EntityClient support
- $expand query option
- Many:Many relationships
- Multi-level relationship navigation
- Media Link Entries
Note that SQL Azure itself doesn’t support tables without clustered primary keys (heap tables).
Ed Katibah (a.k.a. Spatial Ed) replied to my “Is an extension of the OData protocol in the works for spatial features?” question on 3/19/2009:
We are actively discussing this internally and hope to have some proposals firmed up soon.
Ed is Spatial Program Manager, SQL Server, Microsoft Corporation.
Retrieving Collections and Individual Items
Append the collection name to the Service Root URI, as in http://services.odata.org/Northwind/Northwind.svc/Order_Details/ to return all 2,155 members. To return the top N item(s) in the order of the primary key, replace the trailing / with ?top=# where # is the number of items you want. Here’s IE8 displaying the first Order_Details item:
Alternatively, you can skip N item(s) by replace the trailing / with ?skip=#, where # is the number of items you want to skip. To specify sorting on a column other than the primary key, add the ?orderby=ColumnName Query Option, as in http://services.odata.org/Northwind/Northwind.svc/Products?$skip=10&$top=2&$orderby=ProductName.
For a composite primary key, append the Key values in parenthesis, separated by a comma: http://services.odata.org/Northwind/Northwind.svc/Order_Details(OrderID=10248,ProductID=11) for Order_Details or http://services.odata.org/Northwind/Northwind.svc/Suppliers(1) for tables with numeric or http://services.odata.org/Northwind/Northwind.svc/Customers(‘ALFKI’)/ for tables with string primary keys.
OData’s AtomPub Protocol Overhead
AtomPub isn’t a very efficient data transfer protocol. Fiddler 2.0’s Transformer feature reports the size of the entire Order_Details document to be 630,279 bytes or 292.5 bytes per item. Here’s Fiddler 2.0’s display of the HTTP Request and Response messages to retrieve all Order_Details items:
The actual data size of the Order_Details table is 72 KB, which indicates that AtomPub multiplies Data Transfer Out bytes by a factor of 8.75. Microsoft charges $0.15/GB of Data Transfer Out ($0.10/GB of Data Transfer In), so Microsoft might repay its entire development cost for enabling SQL Azure to use the OData protocol with added data egress charges for uncompressed content.
Update 3/30/2010: Scott Hanselman posted a Creating an OData API for StackOverflow including XML and JSON in 30 minutes tutorial on 3/28/2010 and added the following comment on 3/30/2010:
If you compressed the OData with GZip, it'll be as small or smaller than JSON...
I replied on the same date:
I agree, but despite that Accept-Encoding: gzip, deflate header, the SQL Azure OData response isn't compressed.
You can determine whether the data is compressed or not by selecting Fiddler2’s response Transformer tab, which displays “No Compression”:
I started a When Will the SQL Azure OData Provider Deliver GZipped Responses? thread on 3/30/2010 in the SQL Azure — Getting Started forum. Microsoft’s forum moderator, Yi-Lun Luo, suggested I submit a feature request, so here it is: Enable GZip/Deflate Compression of SQL Azure OData Response of 3/31/2010 on the MyGreatWindowsAzureIdea.com wishlist. I’ve also sent a similar e-mail to SQLAzureLabs@microsoft.com.
David Robinson, who’s a Senior Program Manager on the SQL Azure team and the technical editor for my Cloud Computing with the Windows Azure Platform book, replied to my e-mail on 3/31/2010 as follows:
… We definitely want to have a more compact wire format for OData, although it's not clear that compression is the right answer. There are two goals for having the more compact format; reducing server (and, to a lesser degree, client) processing and reducing the bits on the wire. Compression solves the later, but for many scenarios the former is at least as important. We are looking at ways to provide this, but also must stay true to the interoperability tenets that OData is all about.
My take is compression solves the data egress cost problem.
Note: You can download and install Eric Lawrence’s Fiddler 2.0 from here. The author describes Fiddler 2.0 as follows:
Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP(S) traffic, set breakpoints, and "fiddle" with incoming or outgoing data. Fiddler includes a powerful event-based scripting subsystem, and can be extended using any .NET language.
Fiddler is freeware and can debug traffic from virtually any application, including Internet Explorer, Mozilla Firefox, Opera, and thousands more.
Fiddler is an indispensible utility for any developer working with RESTful protocols, such as AtomPub.
Retrieving Related Items (1:Many Navigation)
Append the related collection name to a query that returns a single item, such as http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')/Orders to return all all orders for Alfreds Futterkiste. The following capture shows the first of six orders:
Alternatively, you can use the $links query option to return only the link(s) to related items:
http://services.odata.org/Northwind/Northwind.svc/Customers('ALFKI')/$links/Orders
Applying the $expand Query Option to the service URI and a collection as in http://services.odata.org/Northwind/Northwind.svc/Categories?$expand=Products expands the many side of the navigation property inline with items of the parent collections, in this case each category followed by each product in the category. Here’s Categories(1)/Products(1):
Retrieving Related Items (Many:1 Navigation)
Returning the Categories record for a Products item is an example of many:1 navigation (the reverse of 1:many navigation) with the following syntax: http://services.odata.org/Northwind/Northwind.svc/Products(5)/Category:
An alternative is http://services.odata.org/Northwind/Northwind.svc/Products(5)/$links/Category.
Stay tuned for future posts about OData services for SQL Azure and Microsoft Codename “Dallas.”
Atom 1.0 site feed
