F# Record Types with SqlProvider Code-Last
January 20, 2015 1 Comment
As I talked about last week, I was looking at different ways of using the Entity Framework type provider to map to my domain model. While I was working on the process, Ross McKinley saw some of my whining on Twitter and suggested that I take a look at SqlProvider.
He made a good case to use this type provider over entity framework. Specifically:
- There is no code bloat/file bloat/code-gen issues that you get with EF
- It targets Sql Server like EF, but also can handle Oracle, Postgres, MySql, and other RDBMS
- It has had a update in the last year
So that was a good enough reason to take a look. The project site is a bit lacking in terms of examples but between what is on GitHub and on Ross’s blog, you can get a pretty good idea of how to accomplish basic crud tasks. I was interested in how well it handles nested types and F# choice types. I fired up Visual Studio and installed it from nuget.
I then created the same domain types I was working with earlier –> note the Choice type for gender.
1 #r "../packages/SQLProvider.0.0.9-alpha/lib/net40/FSharp.Data.SqlProvider.dll" 2 3 open System.Linq 4 open FSharp.Data.Sql 5 open System.Security.Principal 6 7 type sqlSchema = SqlDataProvider< 8 ConnectionString = @"Server=.;Database=FamilyDomain;Trusted_Connection=True;", 9 UseOptionTypes = true > 10 11 let context = sqlSchema.GetDataContext() 12 13 //Local Idomatic Types 14 type Gender = Male | Female 15 [<CLIMutable>] 16 type Pet = {Id:int; ChildId:int; GivenName:string} 17 [<CLIMutable>] 18 type Child = {Id:int; FirstName:string; Gender:Gender; Grade:int; Pets: Pet list} 19 [<CLIMutable>] 20 type Address = {Id:int; State:string; County:string; City:string} 21 [<CLIMutable>] 22 type Parent = {Id:int; FirstName:string} 23 [<CLIMutable>] 24 type Family = {Id:int; LastName:string; Parents:Parent list; Children: Child list; Address:Address} 25 26
I then added in the same code that I used for the Entity Framework Type Provider, made some changes (like you get subtypes via querying the foreign key and I am not using Linq to query the data store
1 let MapPet(efPet: entity.dataContext.``[dbo].[Pet]Entity``) = 2 {Id=efPet.Id; ChildId=efPet.ChildId; GivenName=efPet.GivenName} 3 4 let MapGender(efGender) = 5 match efGender with 6 | "Male" -> Male 7 | _ -> Female 8 9 let MapChild(efChild: entity.dataContext.``[dbo].[Child]Entity``) = 10 let pets = efChild.fk_Pet_Child |> Seq.map(fun p -> MapPet(p)) 11 |> Seq.toList 12 {Id=efChild.Id; FirstName=efChild.FirstName; 13 Gender=MapGender(efChild.Gender); 14 Grade=efChild.Grade;Pets=pets} 15 16 let GetPet(id: int)= 17 context.``[dbo].[Pet]`` 18 |> Seq.where(fun p -> p.Id = id) 19 |> Seq.head 20 |> MapPet 21 22 let GetChild(id: int)= 23 context.``[dbo].[Child]`` 24 |> Seq.where(fun c -> c.Id = id) 25 |> Seq.head 26 |> MapChild 27 28 let myPet = GetPet(1) 29 30 let myChild = GetChild(1)
And then I added some code to insert a new pet
1 let SavePet(pet: Pet)= 2 let ssPet = context.``[dbo].[Pet]``.Create() 3 ssPet.ChildId <- pet.ChildId 4 ssPet.GivenName <- pet.GivenName 5 context.SubmitUpdates() 6 7 let newPet = {Id=0;ChildId=1;GivenName="Kiss"} 8 SavePet(newPet) 9 10 let failurePet = {Id=0;ChildId=0;GivenName="Should Fail"} 11 SavePet(failurePet)
And pow on the expected happy path
and pow pow on the expected exception
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "fk_Pet_Child". The conflict occurred in database "FamilyDomain", table "dbo.Child", column ‘Id’.
The statement has been terminated.at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
So this is pretty cool. But then it got better, I showed some of this code to Ross and he told me I was doing everything wrong. Basically, I need to think about the get code less imperative linq and more like computed expressions. The biggest downside to how I wrote the gets is that the TP would pull all of the records from the database locally before filtering them. So going back to the documentation, I changed the getPet functional to this
1 let GetPet(id: int)= 2 query {for p in context.``[dbo].[Pet]`` do 3 where (p.Id = id) 4 select {Id=p.Id; ChildId=p.ChildId; GivenName=p.GivenName}} 5 |> Seq.head 6
And it still works
The nice thing is that I no longer need the mapPet function as the project happens in the select clause. So this is pretty cool and very powerful. Time to learn some more query syntax!
Jamie Dixon's Home 
Pingback: F# Weekly #4, 2015 | Sergey Tihon's Blog