F# Record Types with Entity Framework Code-Last
January 13, 2015 3 Comments
So based on the experience with code-first, I decided to look at using EF code-last (OK, database first). I considered three different possibilities
- 1) Use AutoMapper
- 2) Use Reflection
- 3) Hand-Roll everything
AutoMapper
If you are not familiar, Automapper is a library to allow you to,well, map types. The first thing I did was to create a database schema like this:
1 use FamilyDomain 2 3 CREATE TABLE Family 4 ( 5 Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 6 LastName varchar(255) NOT NULL 7 ) 8 9 CREATE TABLE Parent 10 ( 11 Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 12 FamilyId int NOT NULL, 13 FirstName varchar(255) NOT NULL 14 ) 15 16 CREATE TABLE Child 17 ( 18 Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 19 FamilyId int NOT NULL, 20 FirstName varchar(255) NOT NULL, 21 Gender varchar(10) NOT NULL, 22 Grade int NOT NULL 23 ) 24 25 CREATE TABLE Pet 26 ( 27 Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 28 ChildId int NOT NULL, 29 GivenName varchar(255) NOT NULL 30 ) 31 32 CREATE TABLE HomeAddress 33 ( 34 Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 35 FamilyId int NOT NULL, 36 StateCode varchar(2) NOT NULL, 37 County varchar(255) NOT NULL, 38 City varchar(255) NOT NULL 39 ) 40 41 ALTER TABLE Parent 42 ADD CONSTRAINT fk_Parent_Family 43 FOREIGN KEY (FamilyId) 44 REFERENCES Family(Id) 45 46 ALTER TABLE HomeAddress 47 ADD CONSTRAINT fk_HomeAddress_Family 48 FOREIGN KEY (FamilyId) 49 REFERENCES Family(Id) 50 51 ALTER TABLE Child 52 ADD CONSTRAINT fk_Child_Family 53 FOREIGN KEY (FamilyId) 54 REFERENCES Family(Id) 55 56 ALTER TABLE Pet 57 ADD CONSTRAINT fk_Pet_Child 58 FOREIGN KEY (ChildId) 59 REFERENCES Child(Id) 60 61 62 INSERT Family VALUES 63 ('Andersen') 64 65 INSERT Parent VALUES 66 (1,'Thomas'), 67 (1,'Mary Kay') 68 69 INSERT Child VALUES 70 (1,'Henriette Thaulow','Female',5) 71 72 INSERT Pet VALUES 73 (1,'Fluffy') 74 75 INSERT HomeAddress VALUES 76 (1,'WA','King','Seattle') 77
I then installed automapper and entity framework type provider to a FSharp project.
1 #r @"../packages/AutoMapper.3.3.0/lib/net40/AutoMapper.dll" 2 #r "FSharp.Data.TypeProviders.dll" 3 #r "System.Data.Entity.dll" 4 5 open Microsoft.FSharp.Data.TypeProviders 6 open System.Data.Entity 7 open AutoMapper 8 9 //Entity Framework Types via Type Provider 10 let connectionString = @"Server=.;Initial Catalog=FamilyDomain;Integrated Security=SSPI;MultipleActiveResultSets=true" 11 type EntityConnection = SqlEntityConnection<ConnectionString="Server=.;Initial Catalog=FamilyDomain;Integrated Security=SSPI;MultipleActiveResultSets=true",Pluralize=true> 12
I then created some local FSharp record types the reflect the domain:
1 type Pet = {Id:int; GivenName:string} 2 type Child = {Id:int; FirstName:string; Gender:string; Grade:int; Pets: Pet list} 3 type Address = {Id:int; State:string; County:string; City:string} 4 type Parent = {Id:int; FirstName:string} 5 type Family = {Id:int; Parents:Parent list; Children: Child list; Address:Address}
So then I was ready to start mapping. I started with a basic GET to a single type:
1 //AutoMapper setup 2 Mapper.CreateMap<EntityConnection.ServiceTypes.HomeAddress, Address>() 3 4 //Get one from the database 5 let context = EntityConnection.GetDataContext() 6 let addressQuery = query {for address in context.HomeAddresses do select address} 7 let address = Seq.head addressQuery 8 9 //map database to record type 10 let address' = Mapper.Map<Address>(address) 11
And I got a fail:
Source value:
SqlEntityConnection1.HomeAddress —> System.ArgumentException: Type needs to have a constructor with 0 args or only optional args
Parameter name: type
So I added [<CLIMutable>] to the record types like so
1 [<CLIMutable>] 2 type Pet = {Id:int; GivenName:string} 3 [<CLIMutable>] 4 type Child = {Id:int; FirstName:string; Gender:string; Grade:int; Pets: Pet list} 5 [<CLIMutable>] 6 type Address = {Id:int; State:string; County:string; City:string} 7 [<CLIMutable>] 8 type Parent = {Id:int; FirstName:string} 9 [<CLIMutable>] 10 type Family = {Id:int; Parents:Parent list; Children: Child list; Address:Address} 11
And I get the expected results
With one thing kinda interesting. The State is null because it is defined as “StateCode” on the server and “State” in the domain. Autopmapper is customizable to allow field name differences so that was a small issue. Feeling confident, I went ahead and created maps to all of the domain types and pulled down a complex type from the database
1 //AutoMapper setup 2 Mapper.CreateMap<EntityConnection.ServiceTypes.Pet, Pet>() 3 Mapper.CreateMap<EntityConnection.ServiceTypes.Child, Child>() 4 Mapper.CreateMap<EntityConnection.ServiceTypes.HomeAddress, Address>() 5 Mapper.CreateMap<EntityConnection.ServiceTypes.Parent, Parent>() 6 Mapper.CreateMap<EntityConnection.ServiceTypes.Family, Family>() 7 8 //Get Family from the database 9 let context = EntityConnection.GetDataContext() 10 let familyQuery = query {for family in context.Families do select family} 11 let family = Seq.head familyQuery 12 13 //map database to record type 14 let family' = Mapper.Map<Family>(family)
When I attempted to map it, I got a pretty ugly exception
Source value:
System.Data.Objects.DataClasses.EntityCollection`1[SqlEntityConnection1.Parent]at AutoMapper.MappingEngine.AutoMapper.IMappingEngineRunner.Map(ResolutionContext context)
So the problem is that automapper is not picking up on the foreign keys, which means I have to write the associations by hand. Ugh! I then tried to auto map to F# choice types like this:
1 type Gender = Male | Female
No dice.
Reflection
I quickly spun up another project that uses System.Reflection to map the types.
1 #r "System.Data.Entity.dll" 2 #r "FSharp.Data.TypeProviders.dll" 3 4 open System.Reflection 5 open System.Data.Entity 6 open Microsoft.FSharp.Data.TypeProviders 7 8 let connectionString = "Server=.;Database=FamilyDomain;Trusted_Connection=True;" 9 10 type entityConnection = SqlEntityConnection<ConnectionString = "Server=.;Database=FamilyDomain;Trusted_Connection=True;"> 11 12 let context = entityConnection.GetDataContext() 13 14 //Local Idomatic Types 15 [<CLIMutable>] 16 type Pet = {Id:int; ChildId:int; GivenName:string} 17 [<CLIMutable>] 18 type Child = {Id:int; FirstName:string; Gender:string; 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 //Reflection 27 let AssignMatchingPropertyValues sourceObject targetObject = 28 let sourceType = sourceObject.GetType() 29 let targetType = targetObject.GetType() 30 let sourcePropertyInfos = sourceType.GetProperties(BindingFlags.Public ||| BindingFlags.Instance) 31 sourcePropertyInfos 32 |> Seq.map(fun spi -> spi, targetObject.GetType().GetProperty(spi.Name)) 33 |> Seq.iter(fun (spi,tpi) -> tpi.SetValue(targetObject, spi.GetValue(sourceObject,null),null)) 34 targetObject 35 36 37 let newEfPet = entityConnection.ServiceTypes.Pet() 38 let newPet = {Id=0;ChildId=1;GivenName="Duke"} 39 40 AssignMatchingPropertyValues newPet newEfPet 41 42 context.DataContext.AddObject("Pet",newEfPet) 43 context.DataContext.SaveChanges()
Sure enough, reflection does what it is supposed to do:
The problem quickly becomes that by using reflection, I have to hand roll all of the relations. I might as well use Automapper (though apparently reflection is much faster than Automapper, even on a per-call basis).
Another problem with using reflection is that the field names in the database need to match the domain naming exactly. Finally, like automapper, there is not out of the box way to map choice types
Hand Roll
On my last stop of the entity framework code-last hit parade, I looked at what it would take to roll my own mappings. This has the greatest amount of yak shaving because I would have to spin up mapping from the domain and to the domain. The nice thing is that with that kind of detail, naming mismatches can be handled and the nested hierarchy and choice types are accounted for. I first started with a basic script that handled the gettting and setting as well as nested types:
1 #r "System.Data.Entity.dll" 2 #r "FSharp.Data.TypeProviders.dll" 3 4 open System.Linq 5 open System.Data.Entity 6 open Microsoft.FSharp.Data.TypeProviders 7 8 let connectionString = "Server=.;Database=FamilyDomain;Trusted_Connection=True;" 9 type entity = SqlEntityConnection<ConnectionString = "Server=.;Database=FamilyDomain;Trusted_Connection=True;"> 10 let context = entity.GetDataContext() 11 12 type Pet = {Id:int; ChildId: int; GivenName:string} 13 type Child = {Id:int; FirstName:string; Gender:string; Grade:int; Pets: Pet list} 14 type Address = {Id:int; State:string; County:string; City:string} 15 type Parent = {Id:int; FirstName:string} 16 type Family = {Id:int; LastName:string; Parents:Parent list; Children: Child list; Address:Address} 17 18 let MapPet(efPet: entity.ServiceTypes.Pet) = 19 {Id=efPet.Id; ChildId=efPet.ChildId; GivenName=efPet.GivenName} 20 21 let MapChild(efChild: entity.ServiceTypes.Child) = 22 let pets = efChild.Pet |> Seq.map(fun p -> MapPet(p)) 23 |> Seq.toList 24 {Id=efChild.Id; FirstName=efChild.FirstName; 25 Gender=efChild.Gender;Grade=efChild.Grade;Pets=pets} 26 27 let GetPet(id: int)= 28 let efPet = context.Pet.FirstOrDefault(fun p -> p.Id = id) 29 MapPet(efPet) 30 31 let GetChild(id: int)= 32 let efChild = context.Child.FirstOrDefault(fun c -> c.Id = id) 33 MapChild(efChild) 34 35 let myPet = GetPet(1) 36 37 let myChild = GetChild(1) 38
Of all of the implementations, the hand-rolled actually made the most sense to me. it was clean and, most importantly, it worked.
I then swapped out a Choice type for gender (was a string)
1 type Gender = Male | Female 2 type Pet = {Id:int; ChildId: int; GivenName:string} 3 type Child = {Id:int; FirstName:string; Gender:Gender; Grade:int; Pets: Pet list} 4 type Address = {Id:int; State:string; County:string; City:string} 5 type Parent = {Id:int; FirstName:string} 6 type Family = {Id:int; LastName:string; Parents:Parent list; Children: Child list; Address:Address} 7
And then added the choice type mapping and then updated child mapping
1 let MapGender(efGender) = 2 match efGender with 3 | "Male" -> Male 4 | _ -> Female 5 6 let MapChild(efChild: entity.ServiceTypes.Child) = 7 let pets = efChild.Pet |> Seq.map(fun p -> MapPet(p)) 8 |> Seq.toList 9 {Id=efChild.Id; FirstName=efChild.FirstName; 10 Gender=MapGender(efChild.Gender); 11 Grade=efChild.Grade;Pets=pets} 12
Sure enough, it worked like a champ
And finally, I tested the add on both the happy path and an expected exception.
1 let SavePet(pet: Pet)= 2 let efPet = entity.ServiceTypes.Pet() 3 efPet.ChildId <- pet.ChildId 4 efPet.GivenName <- pet.GivenName 5 context.DataContext.AddObject("Pet",efPet) 6 context.DataContext.SaveChanges() 7 8 let newPet = {Id=0;ChildId=1;GivenName="Lucky Sue"} 9 SavePet(newPet) 10 11 let failurePet = {Id=0;ChildId=0;GivenName="Should Fail"} 12 SavePet(failurePet)
Both worked as expected. Here is the exception case where there is not a child to be associated to a pet:
System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. —> System.Data.SqlClient.SqlException: 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 of all three ways, hand-rolling worked the best for me.
Jamie Dixon's Home 
I wonder what this looks like with AutoMapper projections (Project().To()) that goes all the way down to the LINQ layer to do mappings.
Pingback: F# Weekly #3, 2015 | Sergey Tihon's Blog
Pingback: F# Map To Seq – everockitworld