Named Parameters with fsharp data SqlClient

Question

I am having trouble getting this code to compile when loading the T-Sql from an external file. The code works when the T-Sql is inserted directly in F# as:

 type GetRecentMedia =
        SqlCommandProvider<"DECLARE @MedId NVARCHAR(128);
                            DECLARE @TopN INT;

                            select Top(@TopN) * from media.mediaInfo
                            where MediaId = @MedId",
                            Admin.connectionString, ConfigFile = "Web.config">

But I get this error when trying to load T-Sql from a sql file:

Execute takes 0 arguments but here is given 2.

T-Sql:

DECLARE @MedId NVARCHAR(128);
DECLARE @TopN INT;

select Top(@TopN) * from media.mediaInfo
where MediaId = @MedId

F#:

module internal FeedRecentMedia =

    type GetRecentMedia =
        SqlCommandProvider<"Social\InstagramFeed_Select_GetRecentMedia.sql",
                            Admin.connectionString, ConfigFile = "Web.config">
module MediaTool =

    // get current media
    let rM = new FeedRecentMedia.GetRecentMedia()
    let curMedia = rM.Execute(MedId = "Id", TopN = 1) |> Seq.head

I tested the t-sql in management studio and it works there. I only get the above mentioned error. What am I missing here?

Answer 1

This is a documented limitation of the SqlClient type provider. The parameters you pass from F# code to T-SQL must be

• undeclared (which can be a problem if the type is ambiguous, e.g. for numeric types)
• used only once

Fortunately, the same workaround provided in the docs solves both problems: you declare a variable using the outer variable as parameter. This also lets you explicitly annotate the type:

type FizzOrBuzz = SqlCommandProvider<"
    DECLARE @x AS INT = @xVal
    SELECT 
        CASE 
            WHEN @x % 3 = 0 AND @x % 5 = 0 THEN 'FizzBuzz' 
            WHEN @x % 3 = 0 THEN 'Fizz' 
            WHEN @x % 5 = 0 THEN 'Buzz' 
            ELSE CONCAT(@x, '') --use concat to avoid nullable column
        END", connectionString>

let fizzOrBuzz = new FizzOrBuzz()
printfn "Answer on interview:\n%A" [ for i = 1 to 100 do yield! fizzOrBuzz.Execute(i) ]