We're nearing the end of our SQL journey, having explored ADO.NET, ADO.NET extensions, Micro ORMs and by the end of this post we'll have covered Full ORMs.

Just to remind us of the big picture:

Having just looked at Micro ORMs I won't go into the detail of what an ORM is but more so what the ecosystem looks like in F#.

Entity Framework 📦

A lot of people have asked if they can use Entity Framework with F#. The short answer... YES🙌

While looking for an example I came across this great post by Felipe Luz who walks through setting up your F# project to use Entity Framework so I'll pass the buck 🦌

EFCore.FSharp 🎀

If you were hoping for a F# friendly wrapper of Entity Framework, EFCore.FSharp is what you're looking for. There is also with an awesome introductory post by Simon Reynolds.

So if I'm not going to cover Entity Framework what's the focus of this blog?

SqlTypeProvider 📡

SqlTypeProvider is a type provider that can access a live database.

Let's get started 🏃‍♂️

open FSharp.Data.Sql

type Sql = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER, "CONNECTION_STRING">

let context = Sql.GetDataContext()

Now that the setup is out the way, how do we use the provider?

Query Expressions

Query expressions are a type of computation expression.

The only difference between the tryGetPokemonTrainer function from the last post and this post will be the computation expressions. So I'll focus on those rather than copying pasting the same function.

let pokemonTrainerDto =
    query {
        for pt in context.Dbo.PokemonTrainer do 
        where (pt.Id = trainderId) 
        select (
            {
                Id = pt.Id
                Name = pt.Name
                Wins = pt.Wins
                Losses = pt.Losses
            })
    }
    |> Seq.toList
    |> List.tryHead

let pokemonDtos = 
    query {
        for p in context.Dbo.Pokemon do 
        join type_pokemon in context.Dbo.PokemonTypePokemon on (p.PokeIndex = type_pokemon.PokeIndex)
        join t in context.Dbo.PokemonTypes on (type_pokemon.PokemonTypeId = t.PokemonTypeId)
        where (p.PokemonTrainerId = trainerId)
        select (
            { 
                PokeIndex = p.PokeIndex
                Name = p.Name
                EvolutionName = p.EvolutionName |> Option.ofObj
                Level = p.Level 
                PokemonTypeName = t.PokemonTypeName 
                PokemonTrainerId = p.PokemonTrainerId 
            })
    }
    |> Seq.toList

Worth noting

• SqlTypeProvider needs a live connection string in order to work
• If your domain types are not constrained and can be freely created you could directly map into those removing the need for DTOs
• I really like that you can create the return shape in the select ⭐

Summary 🌈

Getting SqlTypeProvider set up is quick and easy, being able to safely dot into tables and columns made for a great development experience. Since the provider always needs a live connection to your database this makes it tricky for the various stages of development like CI, luckily Flavio Lucio has your back with his post on Structuring an F# project with SQL Type Provider on CI.

Next time 👀

In my next blog we'll be looking at the final piece of the puzzle. Code generation tools!