F# Select from SQL Server table synonym

Question

I have a synonym for a table in my SQL Server database.

I want to select rows from the table but when I create data context with a type provider I don't see the synonym in the created data context.

Synonym is created like this:

CREATE SYNONYM [AnotherDatabase_dbo].[MyTable] FOR [AnotherDatabase].[dbo].[MyTable]

I create data context like this:

type dbSchema = SqlDataConnection<"Data Source=MyServer\MyInstance;Initial Catalog=MyDatabase;Integrated Security=SSPI;">
let db = dbSchema.GetDataContext ()

How can I access the synonym from F#?

Answer 1

More of a workaround than a solution, but the code below allows you to access a synonym through F#. This uses the FSharp.Data library, available through NuGet or here: http://fsharp.github.io/FSharp.Data/. NB: Requires SQL 2012 or above.

open FSharp.Data

[<Literal>]
let connectionString = @"Data Source=MyServer\MyInstance;Initial Catalog=MyDatabase;Integrated Security=SSPI;"

[<Literal>]
let query = "select Id, Name from MyTable" 

type OptionQuery = SqlCommandProvider<query, connectionString>
let cmd = new OptionQuery()

cmd.AsyncExecute() 
|> Async.RunSynchronously
|> Seq.iter (fun row -> printfn "Found row: %d %s" row.Id row.Name)

System.Console.Write("Done")
System.Console.ReadKey()