Visual Studio 2008 SP1: LINQ to SQL with SQL Server 2008 Date Time Types
Visual Studio 2008 SP1: LINQ to SQL with SQL Server 2008 Date Time Types
One of the pillars of Visual Studio 2008 and .Net Framework 3.5 Service Pack 1 is the support for SQL Server 2008 in Visual Studio 2008 tools and the related data access technologies. I decided to check out how LINQ to SQL works with SQL Server 2008 Date and Time types in Visual Studio 2008 and .Net Framework 3.5 Beta.
I created a simple database with a single table called friends, that holds my friends names and birth dates in several formats according the existing and new types.
create table Friends
(
FriendID int identity(1,1) not null primary key,
[Name] nvarchar(50) not null,
BirthDate date,
BirthTime time,
BirthDateTime datetime,
BirthDateTime2 datetime2,
BirthSmallDateTime smalldatetime,
BirthDateTimeOffset datetimeoffset
)
You can download the SQL 2008 Friends Schema.
When I dragged the Friends table from the Server Explorer to the OR/M Designer, I got a visual representation of the Friend class, with types corresponding to the table columns. This is the columns types mapping:
| Column |
Server Data Type |
CLR Type |
| BirthDate |
Date |
System.DateTime |
| BirthTime |
Time |
System.TimeSpan |
| BirthDateTime |
DateTime |
System.DateTime |
| BirthDateTime2 |
DateTime2 |
System.DateTime |
| BirthSmallDateTime |
SmallDateTime |
System.DateTime |
| BirthDateTimeOffset |
DateTimeOffset |
System.DateTimeOffset |
Working with this class was just as usual as with any other type, both for querying and for updating. The result was a GridView full with details of this table:
LINQ to SQL support for Table Valued Patameters
When I tried to drag a stored procedure that takes a table valued parameter and inserts multiple rows from it in a single db access, the designer has created a corresponding method that takes a System.Object type. When I saved the model to work with this method, I got the following compilation error:
DBML1005: Mapping between DbType 'Structured' and Type 'System.Object' in Parameter 'FriendsTable' of Function 'dbo.FriendsInsertMany' is not supported.
I am not sure whether the LINQ to SQL will support Table Valued Patameters in the RTM of the service pack.
Enjoy!