SQL Azure's OData Support
SQL Azure's OData Support
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:
