Tuesday, September 23, 2008

ADO.NET Data Services + Oracle

Wow - this is wicked cool stuff.

ADO.NET Data Services allow you to expose IQueryable objects as RESTful endpoints in ATOM, POX, and JSON. Here's my brain dump of how cool this can be used for RAD purposes...

ADO.NET Data Services is being plugged as just a endpoint for ADO.NET Entities, and thus just SQL Server's LINQ to SQL implementation. Microsoft themselves are doing most of that damage - but I want to point out how it's much bigger than that!

If you're like me, you have the pleasure (yes, pleasure) of working with Oracle instead of SQL Server. Many times this leaves you feeling high and dry with all this cool new LINQ stuff. There is no realy good LINQ to Oracle implementation out there - but the team at NHibernate has come to the rescue.

NHibernate is a .NET port of the years old Java Hibernate project - providing ORM for your software. The importance of this framework in the RAD world is big - it allows you to very easily map a database to objects.

LINQ to NHibernate is almost a year old now, and is in the process of being implemented in the main trunk of NHibernate (currently it's there, but throws a NotImplementedException after mocking you by showing you the SQL it knows that it should use). However, in the NContrib trunk, I have found a quite functional version of LINQ to NHibernate!

Why do I bring this up? Because some kind people in the project have even gone so far as to implement the System.Data.Services.IUpdateable interface within the LINQ.NHibernate project. This means fully functional ADO.NET Data Services for your Oracle database.

With only a couple lines of code, you can expose your NHibernate objects as queryable JSON, ATOM, POX RESTful services. Simply extend NHibernateContext, provide a ProvideSession() implementation, and expose public IQueryable properties on your object. Use this class as the type parameter of a new System.Data.Services.DataService class, and you'll be all set.

ADO.NET data services provides the RESTful interface which is flexible enough to pass pagnation, ordering, filtering, etc straight through the web layer to the database layer. So a javascript request for:

http://server/someobject?$filter=someid eq '1'&$orderby=somefield

produces SQL sent straight to Oracle of

select *
from someobject
where someid = 1
order by somefield

It's also fully updatable (through the IUpdateable implementation) which means that POST, PUT, DELETE requests work in a similar fashion.  It provides it in a JSON format, allowing you to use your database tables as javascript collections.... wow... Again, the RAD implications are huge.

The only issue that I have is with performance - NHibernate doesn't seem to stream resultsets (instead building the object array all at once), and the wordy ATOM/JSON formats don't suit some applications. ADO.NET Data Services is NOT pluggable with custom serialization - I've decompiled and searched for an entry point for literally DAYS.

One other point to make, is that referencing these new data services from VS2008 SP1 provides you with a client side LINQ object which translates linq queries to filtered HTTP requests (as in the filter/order/etc format above, which then translates to SQL... So, cool.

I'd provide some detailed examples and implementations - but that'd ruin all the fun ;)