SQL Azure's OData Support


Posted by Shawn Wildermuth on Mar 24, 2010 on 9:50AM

OData

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:

  •  https://odata.sqlazurelabs.com/OData.svc/v0.1/w9hratewlg/XBoxGames/Games?$filter=Genre_1/Name eq 'Shooter'

Date Filtering:

  •  https://odata.sqlazurelabs.com/OData.svc/v0.1/w9hratewlg/XBoxGames/Games?$filter=ReleaseDate gt datetime'2009-12-12T12:00'

Ordering by Dates:

  •  https://odata.sqlazurelabs.com/OData.svc/v0.1/w9hratewlg/XBoxGames/Games?$orderby=ReleaseDate

 




Application Name WilderBlog Environment Name Production
Application Ver 1.1.0.0 Runtime Framework .NETCoreApp,Version=v1.1
App Path D:\home\site\wwwroot Runtime Version .NET Core 4.6.24628.01
Operating System Microsoft Windows 6.2.9200 Runtime Arch X86