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
• [x] I am willing to test the bug fix before next release

[smoothdeveloper]

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

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/eb45a54728545429339b619acc69d2871a9bbd30/src/SqlClient.DesignTime/SqlCommandProvider.fs#L77

Here is sample in the tests:

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/eb45a54728545429339b619acc69d2871a9bbd30/tests/SqlClient.Tests/TVPTests.fs#L154-L157

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".