SQL Server 2008 data types in LINQ to SQL and EF, they (mostly) did it

Last fall I'd asked the folks on the data access team about the possibility of including the new SQL Server 2008 data types in the new data access object mapping layers. At that time they said "no" but they also asked "why do you want it?" and "are people expecting it?". Someone must have had some compelling arguments.

I'm sure you've read this in other blogs, but in Visual Studio 2008 SP1 Beta, which arrived Monday, there was…drumroll please…support for all four date and time types in not only LINQ to SQL, but in ADO.NET Entity Framework. Thanks! I'm still a bit surprised about the inclusion of DateTimeOffset in EF (its a SQL Server-specific data type and EF is more platform-neutral than LINQ to SQL), but it was a cool surprise to have them there. 

Everyone's blog entry also points out there is some special support for Filestream storage (including a System.Data.Types.SqlFilestream class), but they identify Filestream as a data type. Filestream is a storage format rather than a data type, the actual data type is varbinary(max). Minor terminology nit. You can read and write FILESTREAM based types without resorting to PInvoke. Cool.

Of course, there were some SQL Server 2008 data types (the SQLCLR-based types) that aren't "in there". These are the spatial types (Geometry and Geography) and the HierarchyID data type. Perhaps for EF/LINQ to SQL version 2. These types are correctly identified in Server Explorer in a table but attempting to add a table containing them to an EF diagram fails (it actually leaves the type out of the diagram) and in LINQ to SQL throws an error "the data type is not supported".

That means if you have one of these types in your SQL Server 2008 database, you need to do something funky like treat it as a varbinary(max) and deserialize it (think IBinarySerialize) on the client yourself. Because the SQLCLR-based data types would have a one-to-one mapping to .NET classes (Geometry = Microsoft.SqlServer.Types.SqlGeometry, Geography = SqlGeography, HierarchyID = SqlHierarchyID) these would possibly be straightforward to implement in the next release. They WOULD be a potential model-breaker in that you would have "columns" (entity properties) that have their own properties and methods. Another whole level of indirection.

I've said before that IMHO, spatial support is the "killer developer feature" in SQL Server 2008. Let's hope for more API support in future. But THANKS for the inclusion of the date and time types.