EF, LINQ to SQL, and TVP

| | Comments (0) | TrackBacks (0)

SQL Server 2008 has a great new feature called Table Value Parameters (TVP). I think there are a lot of applications for this new technology, but what about using it with some of Redmond's other new inventions, namely Entity Framework (EF) or LINQ to SQL? Specifically, can you drag and drop a stored procedure from the Server Explorer on to Entity Data Model Designer (in the case of EF) or the Object Relational Designer (in the case of LINQ to SQL)? The answer to this question is yes, but with a catch. Once you try to build the solution, you'll get one of the following compilation errors:

  • DBML1005: Mapping between DbType 'Structured' and Type 'System.Object' in Parameter 'source_key_list' of Function 'dbo.stp_GetCustomerSK' is not supported.
  • The function 'stp_GetCustomerSK' has a parameter 'source_key_list' at parameter index 0 that has a data type 'table type' which is not supported, the function was excluded.

The first error is what you'll get when you're trying to use TVPs with LINQ to SQL and the second what you'll get when using EF. These errors were reported from Visual Studio 2008 SP 1.

This state of affairs reinforces my opinion about these two ORM technologies. I think EF is an immature version one technology that should be avoided in most cases. (Try back with version two or three.) In the latter case, I think that it should only be used on products with a short life span considering that Microsoft is no longer investing any R & D dollars into it and will probably deprecate it in the next release of the .NET framework.