HOW TO: Connect to SQL Server Compact from F#

I have previously blogged about connection to SQL Server Compact from other technologies than pure ADO.NET:

ASP Classic

VBA (Excel)

Silverlight

PowerShell

F# version 3.0 in Visual Studio 2012 makes it relatively easy to connect to a SQL Server Compact database (I am NOT a F# programmer, and even I could connect, so it must be very easy!).

In this blog post I will show the required steps to connect to a SQL Server Compact 4.0 database file and start using it via Entity Framework with F#.

In VS 2012 (currently RC), create a new F# application:

Now add references to the required libraries (Entity Framework and F# Type Providers):

FSharp.Data.TypeProviders
System.Data.Entity
System.Data.Linq

I order to access SQL Server Compact via EF 4 from F#, and Entity Data Model file (.EDMX) file is required. And the EDMX Wizard and Designer only works with C# and VB.NET projects, so to create this, add a Visual C# Console project to the solution:

Now add the EDMX file to this project, right click the project, select Add, New Item…, Data,

Select generate from database, and point to the desired SQL Server Compact 4.0 database.

Just say no:

The Console project now contains an EDMX file, and a useful connection string in app.config.

Now go back to the F# project clear program.fs, and add the following “using” statements:

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

Grab the connectionString value from the console project app config, and add this line:

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

Notice the changes to the provider connection string - =" has been change to single quote, and backslashes have been escaped.

Now add the EDMX file from the Console project to the F# project as Content:

You can now initialize the EF type provider like so:

type internal edmx = EdmxFile<"NWModel.edmx">

And start using the context like this:

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

let internal res = query { for supplier in context.Suppliers do select supplier }

res|> Seq.iter (fun supplier -> printfn "%s" supplier.Company_Name)

let wait = Console.ReadKey()