23

I am using LINQ to SQL with stored procedures in SQL Server 2008. Everything work well except one problem. L2S cannot generate the method for the stored procedure with user defined table type as parameter. Method signature in dbml design panel use object for parameter type instead of table type and when I tried to compile I got error:

Error: DBML1005: Mapping between DbType 'Structured' and
Type 'System.Object' in Parameter 'ParaName' of Function 'dbo.StoredProcName'
is not supported.

Is there a way to work around this of this problem? I don't want to go back to traditional ADO.NET data access.

casperOne
  • 73,706
  • 19
  • 184
  • 253
Dmitri Kouminov
  • 593
  • 2
  • 6
  • 12

3 Answers3

18

I don't think there's anything available right now - table-valued parameters were introduced in SQL Server 2008 - after LINQ and LINQ2SQL were out already.

I haven't found any mention in any of the blog post about support for table-valued parameters in LINQ2SQL in .NET 4.0 so far.

UPDATE:
Here's a blog post by Damien Guard on the fixes and improvement for Linq-to-SQL in the .NET 4.0 time frame.

UPDATE 2:
Unfortunately, according to Damien, this feature isn't supported in .NET 3.5, and will not be supported in .NET 4.0 either :-(

Can't be done in 3.5 or 4.0 right now
:( [)amien

casperOne
  • 73,706
  • 19
  • 184
  • 253
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Do you have any info on new feature (if any) in L2S in .NET4.0? – Dmitri Kouminov Jun 19 '09 at 20:01
  • Interesting. There is a light at the end of the tunnel. LINQ to SQL is alive. Great. Thanks. – Dmitri Kouminov Jun 19 '09 at 20:56
  • 1
    This answer kills me :( Nope, it's not your fault marc_s :) ... a mate and I are wanting to do L2S + TVP's today and cried when we thought we were so close to a killer solution :) Back to ole ADO.NET for this one tiny part of our applications. – Pure.Krome Jun 26 '09 at 02:26
4

I happened upon this post that provides a means of calling stored procedures with TVP parameters from Linq2Sql. It's a round about method that basically creates a partial class extension of your dataContext and passes a dataTable using an SqlCommand. I hope it helps.

cboler
  • 709
  • 4
  • 14
0

I had the exact same problem and Ryan Prechel's sample code from the following link helped me solve the problem.

How to pass table value parameters to stored procedure from .net code

Community
  • 1
  • 1
VicariousAT
  • 173
  • 1
  • 1
  • 8
  • 1
    Whilst this may theoretically answer the question, [it would be preferable](//meta.stackoverflow.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Rohit Gupta Jun 24 '15 at 20:52