Entity Mapping
In the first versions of SqlRepo we only mapped value typed members of entities based on member names. This meant that you had to make sure column names in the result set matched the property and field names of the target entity. It also meant that you had to execute separate queries to populate reference members and collections.
From v3 you can use a testable Entity Mapping Profile (EMP) to override the default behaviour, which remains as before through the DefaultEntityMappingProfile that is used if you do nothing to change it. It was important to maintain backward compatibility so we provide this default profile.
Before we get into the complexities of creating EMPs, we will look at how you use them.
EMPs can only be used with the following types as they are the only ones that return entities on execution
- SelectStatement
- InsertStatement
- ExecuteQueryProcedureStatement
- ExecuteQuerySqlStatement
Each of these types implements a UsingMappingProfile method that takes an instance of IEntityMappingProfile. The following snippet shows how you would apply this to a SelectStatement:
var repository = this.repositoryFactory.Create<Todo>();
var selectStatement = repository.Query();
var entityMappingProfile = new EntityMappingProfile<ToDo>();
/// statement and profile configuration omitted for brevity
var result = selectStatement.UsingMappingProfile(entityMappingProfile)
.Go();Other than the method you use to create the statement to be executed from a Repository, using an EMP is identical for the four statement types mentioned.
An EMP is a generic object that allows you to configure how an IEntityMapper should map the data set retrieved from a database into your entity types. Whilst you can simply create an instance using the constructor directly like this:
var entityMappingProfile = new EntityMappingProfile<ToDo>();We recommend injecting IEntityMappingProfileFactory into your services and components to support mocking and testing. In this case you create your profile like this:
var entityMappingProfile = this.entityMappingProfileFactory.Create<ToDo>();Once you have an EMP instance you have a fluent interface that allows you to configure how the data columns in a data set should be mapped to members of your entities. For members of the entity that are typed using the primitive types you can map by Column Name or Column Index. For reference types or collections you provide a separate EMP. Through this "nesting" of EMPs you can map the most complex entity graphs.
The EntityMappingProfile type implements the IEntityMappingProfile interface, which exposes the methods described below. The first argument to all methods is an Expression that selects the member of the entity that is being mapped.
The ForMember method is used for mapping Properties or Fields that are not collection types. There are three overrides.
IEntityMappingProfile<T> ForMember<TMember>(Expression<Func<T, TMember>> selector,
Action<IEntityValueMemberMapperBuilderConfig> config);This override is used for mapping simple value properties, including all the built-in types and Enums.
- selector: A lambda expression that selects the member of the entity being mapped
- e => e.Name
- config: An action that takes a mapping configuration builder that can be used to map the member to a column name or index
- c => c.MapFromColumnName("Name")
- c => c.MapFromIndex(0)
IEntityMappingProfile<T> ForMember<TMember>(Expression<Func<T, TMember>> selector,
IEntityMappingProfile mappingProfile)
where TMember: class, new();This override is used for mapping reference properties that are typed as a class using an existing EMP
- selector: A lambda expression that selects the member of the entity being mapped
- e => e.Location
- mappingProfile: An EMP instance for the target type
- locationMappingProfile
IEntityMappingProfile<T> ForMember<TMember>(Expression<Func<T, TMember>> selector,
Action<IEntityMappingProfile<TMember>> config)
where TMember: class, new();This override is used for mapping reference properties that typed as a class using an EMP built in line via an action.
- selector: A lambda expression that selects the member of the entity being mapped
- e => e.Location
- config: An action that takes an EMP instance and applies the relevant mapping configuration
- c => c.FormMember(e => e.Town, cc => cc.MapFromColumnName("Location_Name"))
The ForArrayMember method is specifically for members that are defined as a simple Array of classes. There are two overrides.
IEntityMappingProfile<T> ForArrayMember<TItem>(Expression<Func<T, TItem[]>> selector,
IEntityMappingProfile<TItem> mappingProfile)
where TItem: class, new();This override is used for mapping the contents of the array using an existing EMP
- selector: A lambda expression that selects the member of the entity being mapped
- e => e.Addresses
- mappingProfile: An EMP instance for the target type
- addressMappingProfile
IEntityMappingProfile<T> ForArrayMember<TItem>(Expression<Func<T, TItem[]>> selector,
Action<IEntityMappingProfile<TItem>> config)
where TItem: class, new();This override is used for mapping the contents of the array using an EMP built in line via an action
- selector: A lambda expression that selects the member of the entity being mapped
- e => e.Addresses
- config: An action that takes an EMP instance and applies the relevant mapping configuration
- c => c.FormMember(e => e.Town, cc => cc.MapFromColumnName("Location_Name"))
The ForGenericCollectionMember method is specifically for members that are defined as a generic collection of classes. There are two overrides.
IEntityMappingProfile<T> ForGenericCollectionMember<TCollection, TItem>(
Expression<Func<T, IEnumerable<TItem>>> selector,
IEntityMappingProfile<TItem> mappingProfile)
where TCollection: class, IEnumerable<TItem>, new() where TItem: class, new();This override is used for mapping the contents of the collection using an existing EMP
- selector: A lambda expression that selects the member of the entity being mapped
- e => e.Children
- mappingProfile: An EMP instance for the target type
- childMappingProfile
IEntityMappingProfile<T> ForGenericCollectionMember<TCollection, TItem>(
Expression<Func<T, IEnumerable<TItem>>> selector,
Action<IEntityMappingProfile<TItem>> config)
where TCollection: class, IEnumerable<TItem>, new() where TItem: class, new();- selector: A lambda expression that selects the member of the entity being mapped
- e => e.Children
- config: An action that takes an EMP instance and applies the relevant mapping configuration
- c => c.FormMember(e => e.Name, cc => cc.MapFromColumnName("Child_Name"))
Lets look at some examples.
Assuming we have the following types:
public enum Gender
{
Male,
Female
}
public class Address
{
public string Line1 { get; set; }
public string Line2 { get; set; }
public string PostCode { get; set; }
public string Region { get; set; }
public string Town { get; set; }
}
public class Location
{
public double Latitude { get; set; }
public double Longitude { get; set; }
public string Town { get; set; }
}
public class Person
{
public IEnumerable<Person> Children { get; set; }
public DateTime DateOfBirth { get; set; }
public Address[] Addresses { get; set; }
public Gender Gender { get; set; }
public int Id { get; set; }
public Location Location { get; set; }
public string Name { get; set; }
}Notice the members of Person. We have Id, Name and DateOfBirth that are typed using buit-in value types (we are aware string isn't strictly a value type, but for our purposes we will think of it that way). Gender is an Enum. Location is a reference property typed as a custom entity. Children is typed as a Generic Collection, while Addresses is typed as a simple array. Whilst we understand that strictly speaking Addresses is also IEnumerable, when configuring an EMP you will see we deal with them differently. We chose this solution to be specific and to keep the code clean, simple and avoiding reflection wherever possible.
Our goal here is to create an EMP for the Person type. However the type has three members that need thier own EMP Children, Addresses and Location. We provide support for building these EMPs inline, but for now we will create them separately and assume a factory is being used.
Lets start with Address:
var addressMappingProfile = this.entityMappingProfileFactory.Create<Address>();
addressMappingProfile.ForMember(e => e.Line1, c => c.MapFromColumnName("Address_Line1"))
.ForMember(e => e.Line2, c => c.MapFromColumnName("Address_Line2"))
.ForMember(e => e.Town, c => c.MapFromColumnName("Address_Town"))
.ForMember(e => e.Region,
c => c.MapFromColumnName("Address_Region"))
.ForMember(e => e.PostCode, c => c.MapFromColumnName("Address_PostCode"));Here we have mapped all members to a column name with a prefix of Address_
Now we will tackle Location
var locationMappingProfile = this.entityMappingProfileFactory.Create<Locations>();
locationMappingProfile
.ForMember(e => e.Latitude, c => c.MapFromColumnName("Location_Latitude"))
.ForMember(e => e.Longitude, c => c.MapFromColumnName("Location_Longitude"))
.ForMember(e => e.Town, c => c.MapFromColumnName("Location_Town"));Again nice and simple, we have mapped all members to a column name with a prefix of Location_
For Children we actually need an EMP for Person, since children are of course people. However the column names will be different to those for the parent.
var childMappingProfile = this.entityMappingProfileFactory.Create<Person>();
childMappingProfile.ForMember(e => e.Id, c => c.MapFromColumnName("Child_Id"))
.ForMember(e => e.Name, c => c.MapFromColumnName("Child_Name"))
.ForMember(e => e.Gender, c => c.MapFromColumnName("Child_Gender"))
.ForMember(e => e.DateOfBirth, c => c.MapFromColumnName("Child_DateOfBirth"));Now we have the profiles for the "nested" members we can go ahead and create the top level EMP, which is again for a Person
var personMappingProfile = this.entityMappingProfileFactory.Create<Person>();
personMappingProfile.ForMember(e => e.Id, c => c.MapFromColumnName("Id"))
.ForMember(e => e.Name, c => c.MapFromColumnName("Name"))
.ForMember(e => e.Gender, c => c.MapFromColumnName("Gender"))
.ForMember(e => e.DateOfBirth, c => c.MapFromColumnName("DateOfBirth"))
.FormArrayMember(e => e.Addresses, addressMappingProfile)
.ForGenericCollectionMember<List<Person>, Person>(e => e.Children, childMappingProfile)
.ForMember(e => e.Location, locationMappingProfile);All of the above can be merged into a single statement using the methods that support actions for building EMPs for "nested" types. The following example demonstrates how to do this, but mapping columns via Index rather than name.
var personMappingProfile = this.entityMappingProfileFactory.Create<Person>()
.ForMember(e => e.Id, c => c.MapFromColumnIndex(0))
.ForMember(e => e.Name, c => c.MapFromColumnIndex(1))
.ForMember(e => e.Gender, c => c.MapFromColumnIndex(2))
.ForMember(e => e.DateOfBirth, c => c.MapFromColumnIndex(3))
.FormArrayMember(e => e.Addresses, c => c.ForMember(e => e.Line1, cc => cc.MapFromColumnIndex(4))
.ForMember(e => e.Line2, cc => cc.MapFromColumnIndex(5))
.ForMember(e => e.Town, cc => cc.MapFromColumnIndex(6))
.ForMember(e => e.Region, cc => cc.MapFromColumnIndex(7))
.ForMember(e => e.PostCode, cc => cc.MapFromColumnIndex(8))
.ForGenericCollectionMember<List<Person>, Person>(e => e.Children,
c => c.ForMember(e => e.Id, cc => cc.MapFromColumnIndex(9))
.ForMember(e => e.Name, cc => cc.MapFromColumnIndex(10))
.ForMember(e => e.Gender, cc => cc.MapFromColumnIndex(11))
.ForMember(e => e.DateOfBirth, cc => cc.MapFromColumnIndex(12))
.ForMember(e => e.Location, c => c.ForMember(e => e.Latitude, cc => cc.MapFromColumnIndex(13))
.ForMember(e => e.Longitude, cc => cc.MapFromColumnIndex(14))
.ForMember(e => e.Town, cc => cc.MapFromColumnIndex(15)));In either case personMappingProfile can now be passed to the UsingMappingProfile of any of the statements that return entity results.