Entity Framework with F#

Introduction to using Entity Framework with SQL Express and F# Console Apps and Web APIs

Updated: 03 September 2023

Foreword, migrations don’t work with F# (maybe some time but no hopes)

Create the Database

Because we can’t quite work with the normal EF Migrations we can either use C# to manage our data layer as per this article buuuuut I don’t really want to do that, so let’s just use SQL for now. Generally though I feel like maybe EF is not the way to go with F# but for the purpose of discussion

1
CREATE DATABASE TestDatabase
2
GO

And then run the following query on the database

1
CREATE TABLE [TestDatabase].[dbo].[Persons] (
2
    PersonId int,
3
    LastName varchar(255),
4
    FirstName varchar(255),
5
    Address varchar(255),
6
    City varchar(255)
7
)
8
GO

Console App

Create a new console app, you can do this using Visual Studio or the dotnet cli

1
mkdir EFApp; cd EFApp
2
dotnet new console --language F#

Adding the Types

Assuming we have a database that’s already configured and we want to add mappings for our application we beed to define the type as well as the context

1
[<CLIMutable>]
2
type Person =
3
    { PersonId : int
4
      FirstName : string
5
      LastName : string
6
      Address : string
7
      City : string}
8

9
type PersonDataContext() =
10
    inherit DbContext()
11

12
    [<DefaultValue>]
13
    val mutable persons : DbSet<Person>
14

15
    member public this.Persons with get() = this.persons
16
                               and set p = this.persons <- p
17

18
    override __.OnConfiguring(optionsBuilder : DbContextOptionsBuilder) =
19
        optionsBuilder.UseSqlServer("YOUR CONNECTION STRING")
20
        |> ignore

Using the Context

Next we can just make use of the DbContext that we created to access the database as we usually would using EF

1
[<EntryPoint>]
2
let main argv =
3
    let ctx = new PersonDataContext()
4

5
    ctx.Persons.Add(
6
        { PersonId = (new Random()).Next(99999)
7
          FirstName = "Name"
8
          LastName = "Surname"
9
          Address = "Address"
10
          City = "City" }
11
    ) |> ignore
12

13
    ctx.SaveChanges() |> ignore
14

15
    let getPersons(ctx : PersonDataContext) =
16
        async {
17
            return! ctx.Persons.ToArrayAsync()
18
                |> Async.AwaitTask
19
        }
20

21
    let persons = getPersons ctx |> Async.RunSynchronously
22

23
    persons
24
    |> Seq.iter Console.WriteLine
25

26
    0 // return an integer exit code

Web API

IF we want to use it with a Web API we can do that pretty much the same as above, however we’ll set up the DBContext as a service so it can be used with Dependency Injection

1
mkdir EFWebApi; mkdir EFWebApi
2
dotnet new webapi --language F#

Set Up the Types

We’ll use the same type setup as in the console app but but note that we make the type public. We can define this in a file called Person.fs, ensure this is the topmost file in your project

1
[<CLIMutable>]
2
type Person =
3
    { PersonId : int
4
      FirstName : string
5
      LastName : string
6
      Address : string
7
      City : string }
8

9
type PersonDataContext public(options) =
10
    inherit DbContext(options)
11

12
    [<DefaultValue>]
13
    val mutable persons : DbSet<Person>
14

15
    member public this.Persons with get() = this.persons
16
                               and set p = this.persons <- p

Note also that we’ve updated the type to make use of the DbContextOptionsBuilder and that we’re not going to override the OnConfiguring method as we’ll be using the service setup

Service Configuration

In our startup file we can configure the service in the ConfigureServices method, I’ve just left that part of the Startup.fs file below

1
type Startup private () =
2
    new (configuration: IConfiguration) as this =
3
        Startup() then
4
        this.Configuration <- configuration
5

6
    // This method gets called by the runtime. Use this method to add services to the container.
7
    member this.ConfigureServices(services: IServiceCollection) =
8
        // Add framework services.
9
        services.AddControllers() |> ignore
10

11
        // Configure EF
12
        services.AddDbContext<PersonDataContext>(
13
            fun optionsBuilder ->
14
                optionsBuilder.UseSqlServer(
15
                    this.Configuration.GetConnectionString("Database")
16
                ) |> ignore
17
            ) |> ignore

Usage

If we want to make use of the DBContext we can just reference it a controller’s constructor as a dependency

1
[<ApiController>]
2
[<Route("[controller]")>]
3
type PersonController (logger : ILogger<PersonController>, ctx : PersonDataContext) =
4
    inherit ControllerBase()

And we can define some routes that make use of this within the PersonController type

1
[<HttpGet>]
2
[<Route("{id}")>]
3
member this.Get(id : int) =
4
    let person =  ctx.Persons.FirstOrDefault(fun p -> p.PersonId = id)
5

6
    if (box person = null)
7
    then this.NotFound() :> IActionResult
8
    else this.Ok person :> IActionResult
9

10
[<HttpPost>]
11
member this.Post(person : Person) : IActionResult=
12

13
    let createPerson(person : Person) : Person =
14
        ctx.Persons.Add(person) |> ignore
15
        ctx.SaveChanges() |> ignore
16
        ctx.Persons.First(fun p -> p = person)
17

18
    match person.PersonId with
19
    | 0 -> this.BadRequest("PersonId is required") :> IActionResult
20
    | _ ->
21
        match box(ctx.Persons.FirstOrDefault(fun p -> p.PersonId = person.PersonId)) with
22
        | null ->  createPerson person |> this.Ok :> IActionResult
23
        | _ ->
24
            ctx.Persons.First(fun p -> p.PersonId = person.PersonId)
25
            |> this.Conflict :> IActionResult

And this will allow you to make use of the provided endpoints on your application using either of the following:

GET /person/<ID>
POST /person

1
{
2
  "personId": 1,
3
  "firstName": "John",
4
  "lastName": "Jackson",
5
  "address": "125 Green Street",
6
  "city": "Greenville"
7
}

If we would also like to verify if these records are being created in the database we can run:

1
SELECT TOP (1000) [PersonId]
2
      ,[LastName]
3
      ,[FirstName]
4
      ,[Address]
5
      ,[City]
6
  FROM [TestDatabase].[dbo].[Persons]
7
GO