How to connect to SQL Server Compact Edition 4.0 with a type provider in F#?

Question

I'm attempting to connect to a SQL Server Compact Edition database from F#, and attempting to use a type provider. This is in the Visual Studio 11 Beta, so I realize there might be an issue because of that, but I think it's more likely that I just don't have the know-how yet.

However, I have noticed that there's no CE-specific type provider in Microsoft.FSharp.Data.TypeProviders and I'm unsure that a regular SqlDataConnection will do the trick, so that may be the issue right there.

However, when I try to create the connection, the IDE seems to recognize that I'm trying to hit a CE database, at least.

So, I have the following code:

type SqlConnection = 
  Microsoft.FSharp.Data.TypeProviders
    .SqlDataConnection<ConnectionString = @"Data Source=C:\\Path\\Database.sdf">
let db = SqlConnection.GetDataContext()

So it's pretty standard, more or less straight out of the add new LINQ to SQL with type provider item menu.

The tooltip I get over the connection string is "Provider 'System.Data.SqlServerCe.3.5' not installed." Looking that up seems to indicate that it's a problem with not having Sql Server CE installed, but I've got the libraries, am able to connect to the database using a regular SqlCEConnection and running SqlCeCommands and such. And since it's 4.0 and not 3.5, I'm not sure if it's looking for the wrong provider. I created the database right in VS 11 beta, so I was figuring that all versions should match up and such.

So in short, I'm wondering if I'm doing something wrong, or if the VS11 beta type provider libraries don't support CE 4.0 yet, or if there is something else I need to do to make it happen.

Thanks!

Answer 1

This "Works on my Machine" (using VS 11 beta, Entity Framework, based on the walkthrough here, http://msdn.microsoft.com/en-us/library/hh361038(v=vs.110).aspx :

open System.Data.Linq
open System.Data.EntityClient
open Microsoft.FSharp.Data.TypeProviders

let connectionString = "metadata=res://*/;provider=System.Data.SqlServerCe.4.0;provider connection string='data source=C:\\Data\\SQLCE\\Test\\nw40.sdf';"

type internal edmx = EdmxFile<"NWModel.edmx", ResolutionFolder = @"C:\Users\erik.COMMENTOR\Documents\Visual Studio 11\Projects\TestSqlCeFSharp">

let internal context = new edmx.nw40Model.nw40Entities(connectionString)

query { for supplier in context.Suppliers do
        select supplier }
|> Seq.iter (fun supplier -> printfn "%s" supplier.Company_Name)

Added references to: FSharp.Data.TypeProviders, System.Data.Entity, System.Data.Linq

Answer 2

Supported info of 'System.Data.SqlServerCe.4.0' in  Microsoft.FSharp.Data.TypeProviders
1.SqlDataConnection is not
2.DbmlFile is partial
    type dbml = DbmlFile<_>
    use cn = new SqlCeConnection(...)
    use db = new dbml.DbContext(cn)
3.EdmxFile is full
4.SqlEntityConnection is full