none
Linq to Sql - Stored procedures with a parameter of SqlDbType.Structured ( User defined table variable type - SQL 2008+ ) RRS feed

  • Question

  • We access our database exclusively via Stored procedures.  We use Linq to Sql to wrap the stored procedure call but our only problem is when a stored procedure contains a user defined table type ( SQL2008 feature ).

    In this scenario we are updating a parent-child relationship of records in 1 hit.

    Linq to Sql does not seem to support this type of parameter.  What can I do to get this working.

     

    I have manage to get this working using standard ADO.NET, by creating a DataTable and passing that as the parameter but this seems a massive step backwards.

    Entity Framework 4 doesn't support direct execution of stored procedures so that has been ruled out as our data access strategy.

     

    Any guidance ?

     

    Cheers...

     

    Robert

    Monday, March 7, 2011 8:27 AM

Answers

  • Hi Robert,

     

    Thanks for your post.

    Just like I said above, LINQ to SQL doesn’t support this. I suggest you to do this in the SQL Server.

    If so, you need to see this example on CodeProject.

    SqlParameter param = cmd.Parameters.AddWithValue("@FileDetails", dt); 

    where dt is a DataTable, and the @fileDetails parameter is a table type in SQL:

    create type FileDetailsType as table 
    ( 
       
    FileName        varchar(50), 
       
    CreatedDate        varchar(50), 
       
    Size       decimal(18,0) 
    ) 

    This MSDN Developer's Guide article also would help.

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 10, 2011 9:42 AM
    Moderator

All replies

  • Hello Robert,

     

    Welcome to the MSDN Forum and thanks for posting here.

    As far as I know,  LINQ to SQL doesn't support mapping user-defined value types (only does primitives as values like string, int...).

    Yet, you could check this blog post on upcoming changes in LINQ to SQL for 4.0: http://damieng.com/blog/2009/06/01/linq-to-sql-changes-in-net-40


    As for your question itself, you could check this thread which seemingly has a solution which uses a computed column on the db table instead: http://stackoverflow.com/questions/781081/linq-to-sql-and-clr-user-defined-types ...I haven't tried it but hope that shows you a way.

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 8, 2011 6:48 AM
    Moderator
  • Hi Jackie,

     

    I'm already using .NET 4.0 and neither the Linq-to-Sql designer nor SqlMetal support the user defined table types.  What is the roadmap for Linq to Sql, or has MSFT already nailed coffin shut.

     

    Here is the output when using SqlMetal

     

    Microsoft (R) Database Mapping Generator 2008 version 4.0.30319.1

    for Microsoft (R) .NET Framework version 4.0

    Copyright (C) Microsoft Corporation. All rights reserved.

     

    Warning : SQM1025: Unable to extract stored procedure 'uspMerchantGroup_Insert' from SqlServer because parameter 'MerchantCodes' is the unsupported DbType 'Structured'.

     

    Tuesday, March 8, 2011 8:57 PM
  • Hi Robert,

     

    Thanks for your post.

    Just like I said above, LINQ to SQL doesn’t support this. I suggest you to do this in the SQL Server.

    If so, you need to see this example on CodeProject.

    SqlParameter param = cmd.Parameters.AddWithValue("@FileDetails", dt); 

    where dt is a DataTable, and the @fileDetails parameter is a table type in SQL:

    create type FileDetailsType as table 
    ( 
       
    FileName        varchar(50), 
       
    CreatedDate        varchar(50), 
       
    Size       decimal(18,0) 
    ) 

    This MSDN Developer's Guide article also would help.

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 10, 2011 9:42 AM
    Moderator