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!