One of the activities me and Hege have really gotten into the last year is scuba diving. I'm currently "PADI Advanced Open Water" certified and have 39 logged dives. Hege got me a an awesome Suunto D6 dive computer for Christmas (she has the D9 model). If you buy the (way over priced) computer cable you can sync your Suunto watch with your computer using the "Suunto Dive Manager" application. Once synced to your computer the app gives you all kinds of cool stats, like dive profile, air consumption (if you have the D9 model), temperature, maximum depth, total dive time and lots of other interesting stuff.

Being a programmer I immediately started poking around in Suunto DM trying to figure ut how to access my dive log from code. Turns out Suunto store all my dives in a Microsoft Access database. When I first discovered this I was pretty happy thinking this was going to be easy as stealing candy from a kid. This was until I discovered Suunto had applied a "one table to rule them all"-database design. The main "Items"-table has 82 (!) numbered columns (custom_1, custom_2 etc.). The only reasonable (?) reason I can think off for this design has to be that Suunto at some point migrated from a proprietary database-like binary format to Access, and that they kept some of the same "data structures" for easy migration. At least that's what I hope... for their sake.

My first idea was to see how well the ADO.NET Entity Framework would play with this database deisign. After all EF is supposed to give you that extra flexibility when mapping objects to data. I did some research only to realise that Access won't be supported by EF v1.0. Danny Simmons, developer on the ADO.NET EF team, gives some background information in a forum post from back in July 2007:

"OLE-DB ado.net provider has not been extended to support the entity framework, and we do not currently have plans to do so in our first release. Further, this would be a fairly difficult effort because OLE-DB doesn't provide enough information in general to translate CQTs (canonical query trees--the entity framework intermediate query representation) to a particular back-end query syntax."

I sent Danny an e-mail to check if this was still the case. Turns out it is. In theory you could use a third party ODBC provider, but according to Danny you probably going to need a custom Access provider in order to use ADO.NET Entity Framework on top of Access databases.

So, since ADO.NET EF was out of the picture I was pretty much left with the default LINQ to SQL support in .NET 3.5 (no way I was going to write the T-SQL by hand). Officially only SQL Server 2000 and 2005 is supported by LINQ to SQL, but it turns out the DataContext object accepts a IDbConnection in its constructor. By passing in a OleDbConnection you can have LINQ talk to an Access database. You will not get design time support in Visual Studio 2008, and have to write all the mapping classes your self. But at least you can execute some basic LINQ queries. The LINQ runtime will not now that it's talking to a Access database, and it will generate SQL code as if it was running against a SQL Server. But for basic SELECT queries I haven't gotten into trouble yet.

I'll write a follow up post on the actual implementation of my LINQ layer on top of the Suunto Dive Manager database. For now a class diagram might give you a an idea of how the end mapping looks like.

Do you care about Microsoft Access support in ADO.NET Entity Framework? Or are you perhaps into diving yourself? Let me know in the comments section!