Shawn Wildermuth's Rants and Raves

Thanks for visiting my blog! See more about me here: About Me

SQL Azure's OData Support
SQL Azure's OData Support
March 24, 2010

I’ve ported my XBoxGames Database (see this blog article for copies of the .mdf files) to SQL Azure and added OData support. You can find the feed here:

https://odata.sqlazurelabs.com/OData.svc/v0.1/w9hratewlg/XBoxGames

While this rocks. it did expose some of the lack of full OData support in SQL Azure but they’re working on them immediately.  Michael Pizzo is working on this support and send this list of what is and isn’t working currently:

Supported:

Requests of the form “http://odata.sqlazurelabs.com/OData.svc/v0.1/[SQLAzureServerName]/[SQLAzureDatabaseName]” are recognized and correctly routed

  • Supports PowerPivot, Astoria Client/ASR, LinqPad, etc.

Integration with Portal, ACS

  • Impersonation based on security token for authenticated access
  • Anonymous access mapped to a particular user (read-only reference data, browser access,…)
  • Configuration information is read from a configuration table, hosted in sql azure

Metadata is dynamically loaded (changes to SqlAzure schema are immediately available through OData endpoint)

  • Pluralization support for entityset naming
  • IDs based on PKs
  • Relationships identified based on FKs
  • Goal is to generate an equivalent model to what EntityDesigner generates by default when doing modelgen from a database

The following requests are supported:

  • ServiceDocument (/)
  • metadata (/$metadata)
  • entityset (/entitysetname)
  • identity operator (/entitysetname(id))
  • property navigation (/entityset(id)/propertyname)
  • count (/$count)
  • single segment relationship navigation (/entityset(id)/navigationpropertyname)

The following query operators are supported:

  • $top
  • $skip
  • $orderby
  • $filter with basic expression support
  • $inlinecount
  • $select
  • $skiptoken

ServerDriven paging is supported

  • first 50 records plus NextLink

The following are supported in expressions:

  • math operators (add, subtract, multiply, divide, mod)
  • comparison expressions (equal, notequal, greaterthan, greaterthanorequal, lessthan, lessthanorequal)
  • logic expressions (AND, OR, NOT)
  • A host of string functions (length, substring, left, right, trim, replace, remove, upper, lower, startswith, endswith, concat, indexof)

Basic updating support

  • user-defined key values
  • autoincrement key values

**Not supported (yet):
**Stored procs
Tables/views w/no primary key
Date and Math scalar functions
EntityClient support
$expand
Many:Many relationships
Multi-level relationship navigation
Media Link Entries
Other feature requests?

Other Bugs:

Not listed in Michael’s notes are a couple of other issues i’ve run into (but i’ve reported them to him so I expect they’re going to be addressed soon):

Filtering along relationships:

Date Filtering:

Ordering by Dates: