Cannot use User Defined Table Types with the SqlCommandProvider

[daniellittledev]

Issue Summary

I'm attempting to use a User Defined Table Type to pass a list into a sql command. The type provider successfully infers the type of the User Defined Table and compiles correctly. However at runtime an error is thrown from the SqlClient indicating that a variable needs to be declared (even though it is declared).

To Reproduce

Here is a sample Table Type:

Create Type [dbo].[Identifiers] AS Table(
  [Id] UniqueIdentifier Not Null
);

Here is how you would use it, note that DeleteRecordsCommandProvider.Identifiers the type constructor is accessible off the provider.

type private DeleteRecordsCommandProvider = SqlCommandProvider<"
    Declare @Ids as Identifiers = @MyIds;
    Delete [Records]
    From [Records] target
    Join @Ids source on source .Id = target.Id
    " , staticConnectionString>

type DeleteRecordsDbCommand = DeleteRecordsParameters -> Async<int>
let deleteRecords (context: DatabaseContext) : DeleteRecordsDbCommand =
    fun parameters -> async {

    use cmd = new DeleteRecordsCommandProvider(context.connection, transaction = context.transaction)

    let ids = parameters.recordIds |> List.map(fun x -> DeleteRecordsCommandProvider.Identifiers x)

    return! cmd.AsyncExecute(
        MyIds = ids
    )
}

Error

However, when the following code is run the following error is thrown:

System.Data.SqlClient.SqlErrorCollection
Must declare the scalar variable "@InputIds".

Expected behavior

I would expect this command to execute successfully.

What you can do

• I am willing to contribute a PR with a unit test showcasing the issue
• I am willing to test the bug fix before next release

[smoothdeveloper]

@daniellittledev could you check if using TableVarMapping parameter fixes the issue?

FSharp.Data.SqlClient/src/SqlClient.DesignTime/SqlCommandProvider.fs

Line 77 in eb45a54

<param name='TableVarMapping'>List table-valued parameters in the format of "@tvp1=[dbo].[TVP_IDs]; @tvp2=[dbo].[TVP_IDs]"</param>

Here is sample in the tests:

FSharp.Data.SqlClient/tests/SqlClient.Tests/TVPTests.fs

Lines 154 to 157 in eb45a54

	type MappedTVP =
	SqlCommandProvider<"
	SELECT myId, myName from @input
	", ConnectionStrings.AdventureWorksLiteral, TableVarMapping = "@input=dbo.MyTableType">

AFAIR, there are some technicalities as to why those TVP can't be inferred as it is done with other parameters, but you can still define commands using those, with extra/explicit declarations.

[daniellittledev]

Thanks, I had not seen that, I'll try it out.

[vcrobe]

Hi there,

I'm in the same situation that is @daniellittledev

I've this query

type Query = SqlCommandProvider<"
    declare @ids as dbo.Identifiers = @idProducts

    SELECT *
    FROM ep
    WHERE ep.ID in (
	        SELECT ID from @ids
    )
", connectionString>

When I run the code I get the following error:

System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@idProducts".

I'm using the package FSharp.Data.SqlClient version 2.0.7.

Which version of the package I have to install in order to use TableVarMapping?

Also: How should I use TableVarMapping? Any example?

I've been able to use User Defined Table Values sucessfully with a Stored Procedure but now I need to create a very simple query so I think using a SP is overkill.

[smoothdeveloper]

@vcrobe can you try adding , TableVarMapping = "@idProducts=dbo.Identifiers"

[vcrobe]

@smoothdeveloper thank you for your quick response!

I did it and now I get this error:

The type provider 'FSharp.Data.SqlCommandProvider' reported an error: Must declare the scalar variable "@SQLCOMMANDPROVIDER_idProducts".