F# & Entity Framework Core

with .NET5 & PostgreSQL

Preface

As a minority & newcomer in .NET, I spent some weekends figuring out how to make F# work with databases.

I haven’t used C# nor Microsoft SQL Server nor .NET 4.x . I don’t want to write raw SQL within F# neither!

Most of the online materials are outdated and stayed in pre-dotnet 5. So I decided to put this self-reference note online.

Content

1. EF Core (F# design-time support)
2. Simple F# console programme with CRUD operations

EF Core Design-time Support (beta)

EF Core design-time support on F# is pretty new. It had just turned beta last month. Most posts wrote in dotnet core 2.x–3.x suggested using C#was the only way to build a design time EF core.

1. Create a new dotnet project

md AddressBook
cd AddressBook
dotnet new console -lang F#

2. Install local tool dotnet-ef

dotnet new tool-manifest
dotnet tool install dotnet-ef

3. Add dependency packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package EntityFrameworkCore.FSharp --prerelease

It requires a prerelease flag before the package turns into the stable release

4. Create a Model file AddressBookModel.fs

Don’t forget to add its reference into AddressBook.fsproj
<Compile Include="AddressBookModel.fs" />

5. Generate migration files

dotnet ef migrations add Initial -v

If you hit an undefined namespace of EntityFrameworkCore error:
1. Go to AddressBook.fsproj
2. Delete <IncludeAessets> in <PackageReference Include="EntitlyFrameworkCore.FSharp">

EF will auto-generate two files, AddressBookContextModelSnapshot.fs and 202106060303110_Initial.fs. The numbers prefix, which represents the date and time, will be difference every time.

6. Migration

It’s ok to use asterisk in the AddressBook.fsproj

<ItemGroup>
  <Compile Include="AddressBookModel.fs" />
  <Compile Include="Migrations/*.fs" />
  <Compile. Include="Program.fs" />
</ItemGroup>  

7. Finally, we can run the migration

dotnet ef database update

Apart form the AddressBook table, it also creates a migrations history table similar those in rails/laravel

A point to note: Postgres is case sensitive when the table / fields names are double-quoted; EFCore does double-qouted what you typed in the source code.

Simple F# Console app with CRUD

What does the program do:

1. Insert 2 records, viz aRecord and bRecord. (line 28–30)
2. Select whole table. (line 33)
3. Use F# query syntax to select the first record (in term of Id) (line 38–42) i.e. the item with the smallest Id (“Chan Tai Man”)
4. Looks silly, just a demo on 2 common actions: (i) obtain a field in a record and (ii) obtain a record with Id (line 44)

5. Update email of the selected item in step3.

6. Delete the item with Id = 1000, viz “Wong Siu Man”

The final result of the program.

Reflections

• F# gives in its immutable and functional favour for EFcore’s operations.
• When passing through NPGSQL, PostgresSQL’s identifiers will become case sensitive (as they are double-quoted in the generated SQL). It may result in conflicts of naming conventions between the database and .NET code. It seems developers do have not many controls over this part right now.
• F# is still a second class dotnet citizen — even the dotnet ecosystem becomes more developer-ergonomic, and C# is subsuming more functional stuff.
• Some lovely syntaxes are still pending to implement:
  https://github.com/fsharp/fslang-suggestions/issues/506
• Wish better interop with C#!

— — — — — — — — — —

Update on 11/6/2021

About database update for immutable record, it seems EF core is working on it (https://github.com/dotnet/efcore/issues/11457).

Meanwhile, I noticed EFCore uses the id key for .Update(). It means we can supply a record with Id, which similar to HTTP PUT, EFcore will do the rest for us.

//HTTP PUT (replace as a whole)
let cRecord = { Id = 9999
                Name = "ABC"
                Email = "a@bc" }
ctx.AddressBook.Update(cRecord) |> ignore
ctx.SaveChanges() |> ignore

For partial updates (similar to HTTP PATCH), this approach will duplicate a record with same Id ( record and record' in the most bottom code snippet). It causes a System.InvalidOperationException.

The instance of entity type ‘AddressBook’ cannot be tracked because another instance with the same key value for {‘Id’} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.

A workaround is simple mark record’s EntityState as Detached.

let record =  ctx.AddressBook.Find 1
ctx.Entry(record).State <- EntityState.Detached
let record' = { record with
   Name = "Test2"
   Email ="test2@test.com"
}
ctx.AddressBook.Update record'  |> ignore
ctx.SaveChanges() |> ignore

Another way is set AsNoTracking in Linq ( I’m quite not sure why Linq in F# is longer but less FP than that in C#).

I’m petty fine with the first workaround, and stay turn for EFcore team to settle the issue. As they mentioned C# 9 introduce immutable record type, so hopefully they will solve the issue in near future.