One of the things that's been really bothering me for quite a while now is the aspect of mapping in the Entity Framework.
Technical things can trouble me. I like to get them into my head, understood and then I can move on. I have a very, very bad memory and the only way I can remember anything is by understanding how it works and then I find it relatively easy to piece back together at a later point.
One of the big things about Entity Framework versus (say) [LINQ to SQL] is that you have this Conceptual->Logical mapping layer that abstracts you away from the logical level.
The thing that's been bothering me is essentially - what can that mapping layer do for you?
Now, I know it can do specific things (e.g. table-per-type inheritance) but what I'd really like is a definitive list of exactly what you can do with the mapping layer.
I thought I'd start trying to build something here deliberately starting with the simple and moving towards the slightly more complex.
1) Renaming an Entity Type
I can take an entity like Customers here from Northwind;
and I can rename it to be called (e.g.) Foo;
and everything still "just works".
2) Renaming an Attribute
I can take an attribute like "CustomerID" below;
and I can rename it to "ID" for instance;
and things still work.
3) Removing an attribute.
If certain attributes in my Entity don't interest me then I can remove them from the conceptual model. For example, below I've removed all the non-null parts of the Customer table;
and I can still use this as a valid Entity in that I can query, insert, etc. using this entity.
Note that if I try and omit an attribute of an entity where that attribute is non-NULL without a default value specified in the store then I get into a mess and the model validation tells me;
4) Specifying a default value for an attribute that does not have a default value.
Whereas ContactName in my store does not have a default value, I can add one for ContactName in my model. That is;
Then code such as;
Customer c = new Customer()
{
ID = "MyCm2",
Company = "Foo"
};
Console.WriteLine(c.ContactName);
will write out "Default Value" which is quite cool. I can also do this with non-nullable columns such as Company so that if my code leaves them null then the default will be used for (e.g.) inserts.
5) Overriding a default value for an attribute that has a default value.
Clearly, this is just the same as (4) because by the time we've hit the DB the attribute will be set and so the store-provided default will not be used.
6) Changing the data type of an attribute.
If I have a store attribute such as CustomerID which is nvarchar(10) then I can change my model to represent that as (say) string(5) and be more restrictive than the store.
I could also try and be less restrictive than the store - e.g. setting my model to be string(20) but the model validation catches that;
Similarly, if I try and change the data type of CustomerID to be Boolean or Int32 then that fails with similar error messages (although you might see cases where you wanted to do automatic Int/String conversion but I don't think that functionality is there).
If I start with a numeric type - say Order.Freight which is a decimal in the database then I can play with the types a little there. I can ramp down (but not up) the precision and the scale of the decimal.
I can change the data type to be (say) an Int16, Int32, Int64 - letting me use a narrowing conversion in order to use a subset of the values that can be represented in the database column.
7) Changing the nullability of an attribute.
If I have a nullable column in my store, I can map that as a non-nullable column in my model. I'm not 100% sure on this one because it's hard for me to separate this from the value type/reference type aspects of the CLR type system so what follows is just a "for instance".
If I have Order.Freight which is decimal(NULL) in the DB then I can model that as decimal(not null) or, as here, I've used Int32(not null) as the Int32 part just follows on from (6) above;
What that means is that in the programming model I will end up with Order.Freight being of CLR type Int rather than Int? and so (e.g.) if a programmer forgets to set a value for Freight then we'll end up with a value of 0 in the database rather than a value of NULL.
As you can see, this doesn't really apply to CLR reference types because they can actually have a NULL value so if we do this same trick with a string based attribute then we still end up with NULLs hitting the database. That is, I can control whether we end up with Int or Int? but the model doesn't (AFAIK) enforce nullability before going to the database.
8) Multiple store tables, single entity type
Imagine that I've got a couple of tables in my database (I'm deviating from Northwind here) looking something like;
create table oldCars
(
id int identity primary key,
make nvarchar(20) not null,
model nvarchar(20) not null
)
insert oldCars values('ford', 'capri')
create table newCars
(
id int identity primary key,
manufacturer nvarchar(20) not null,
[type] nvarchar(20) not null
)
insert newCars values('ford', 'focus')
And I want to represent both of these with a single type called Car in a single entity set called Cars.
where the isOld value is something that I've added to the entity but doesn't really exist in the database. I can then map this to my two underlying tables. I'm not sure if I can do it in the designer or not so I did it in XML.
Conceptual looks like this;
<EntityContainer Name="demoEntities">
<EntitySet Name="Cars"
EntityType="demoModel.Car" />
</EntityContainer>
<EntityType Name="Car">
<Key>
<PropertyRef Name="id" />
</Key>
<Property Name="id"
Type="Int32"
Nullable="false" />
<Property Name="make"
Type="String"
Nullable="false"
MaxLength="20" />
<Property Name="model"
Type="String"
Nullable="false"
MaxLength="20" />
<Property Name="isOld"
Type="Boolean"
Nullable="false" />
</EntityType>
And mapping looks like this;
<EntitySetMapping Name="Cars"
TypeName="demoModel.Car">
<MappingFragment StoreEntitySet="oldCars">
<ScalarProperty Name="id"
ColumnName="id" />
<ScalarProperty Name="make"
ColumnName="make" />
<ScalarProperty Name="model"
ColumnName="model" />
<Condition Name="isOld" Value="true" />
</MappingFragment>
<MappingFragment StoreEntitySet="newCars">
<ScalarProperty Name="model"
ColumnName="type" />
<ScalarProperty Name="make"
ColumnName="manufacturer" />
<ScalarProperty Name="id"
ColumnName="id" />
<Condition Name="isOld" Value="false"/>
</MappingFragment>
</EntitySetMapping>
This seems to work pretty well in that I can write code such as;
using (demoEntities ent = new demoEntities())
{
foreach (Car c in ent.Cars)
{
Console.WriteLine(
"{0},{1},{2},{3}", c.id, c.make, c.model, c.isOld);
}
Car c = new Car()
{
isOld = false,
make = "vauxhall",
model = "vectra"
};
ent.AddToCars(c);
ent.SaveChanges();
}
The framework nicely populates the isOld flag for me depending on which underlying table the data came from and, similarly, performs my insert against the right table based on the value of the isOld flag when I create a new entry.
This is really just a variation on what's documented up here.
Update: Matthieu points out that my tables here could cause me to have two Car entities with the same private key - he's right and uniqueidentifiers might have been a better choice for me here. He opened a thread over here to discuss.
9) Single store table, multiple entity types or entity sets
For me, this one is a bit more complex. I may have one table in my database called (e.g.) Customers and I might want to split this up into;
- Multiple entity types within the same entity set. That is, an entity set called Customers that contains PlainCustomers, FancyCustomers and so on. As far as I know, an entity set contains entities of a particular type and/or subtypes so that means that if I want to go down this route I need to have inheritance between my types.
- One entity type within multiple entity sets. That is, I might want to have 2 entity sets called UkCustomers and WorldCustomers but they are both the same type of entity (Customer), they just live in different sets.
So, the first one of these is really just "Table Per Hierarchy" inheritance and that's documented here and I also played with it a little bit here.
The second one is different. I want to have two entity sets surfacing the same type and so essentially a conditional mapping that says "If this condition is true then you're in Set1, otherwise Set2". Now, as far as I know this isn't something that you can "just do" when it comes to building your EDM and I asked around a little and the answer that came back was that you could do this with QueryViews. This is along the same lines as what I posted here in that I can go ahead and define my conceptual model as;
<Schema Namespace="NorthwindModel"
Alias="Self"
xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
<EntityContainer Name="NorthwindEntities">
<EntitySet Name="UkCustomers"
EntityType="NorthwindModel.Customers" />
<EntitySet Name="WorldCustomers"
EntityType="NorthwindModel.Customers" />
</EntityContainer>
<EntityType Name="Customers">
<Key>
<PropertyRef Name="CustomerID" />
</Key>
<Property Name="CustomerID"
Type="String"
Nullable="false"
MaxLength="5"
FixedLength="true" />
<Property Name="CompanyName"
Type="String"
Nullable="false"
MaxLength="40" />
<Property Name="ContactName"
Type="String"
MaxLength="30" />
<Property Name="ContactTitle"
Type="String"
MaxLength="30" />
<Property Name="Address"
Type="String"
MaxLength="60" />
<Property Name="City"
Type="String"
MaxLength="15" />
<Property Name="Region"
Type="String"
MaxLength="15" />
<Property Name="PostalCode"
Type="String"
MaxLength="10" />
<Property Name="Country"
Type="String"
MaxLength="15" />
<Property Name="Phone"
Type="String"
MaxLength="24" />
<Property Name="Fax"
Type="String"
MaxLength="24" />
</EntityType>
</Schema>
and my store schema as (with incomplete functions for insert, update, delete);
<Schema Namespace="NorthwindModel.Store"
Alias="Self"
ProviderManifestToken="09.00.3054"
xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
<EntityContainer Name="dbo">
<EntitySet Name="Customers"
EntityType="NorthwindModel.Store.Customers" />
</EntityContainer>
<EntityType Name="Customers">
<Key>
<PropertyRef Name="CustomerID" />
</Key>
<Property Name="CustomerID"
Type="nchar"
Nullable="false"
MaxLength="5" />
<Property Name="CompanyName"
Type="nvarchar"
Nullable="false"
MaxLength="40" />
<Property Name="ContactName"
Type="nvarchar"
MaxLength="30" />
<Property Name="ContactTitle"
Type="nvarchar"
MaxLength="30" />
<Property Name="Address"
Type="nvarchar"
MaxLength="60" />
<Property Name="City"
Type="nvarchar"
MaxLength="15" />
<Property Name="Region"
Type="nvarchar"
MaxLength="15" />
<Property Name="PostalCode"
Type="nvarchar"
MaxLength="10" />
<Property Name="Country"
Type="nvarchar"
MaxLength="15" />
<Property Name="Phone"
Type="nvarchar"
MaxLength="24" />
<Property Name="Fax"
Type="nvarchar"
MaxLength="24" />
</EntityType>
<Function Name="InsCustomer"
IsComposable="false">
<CommandText>insert customers(CustomerId,CompanyName) values(@Id, @Company)</CommandText>
<Parameter Name="Id"
Mode="In"
Type="nvarchar"/>
<Parameter Name="Company"
Mode="In"
Type="nvarchar" />
</Function>
<Function Name="DelCustomer"
IsComposable="false">
<CommandText>delete customers where customerid = @id</CommandText>
<Parameter Name="Id"
Mode="In"
Type="nvarchar"/>
</Function>
<Function Name="UpdCustomer"
IsComposable="false">
<CommandText>
/* TODO */
</CommandText>
</Function>
</Schema>
and my mapping between them as;
<Mapping Space="C-S"
xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
<EntityContainerMapping StorageEntityContainer="dbo"
CdmEntityContainer="NorthwindEntities">
<EntitySetMapping Name="UkCustomers">
<QueryView>
using NorthwindModel;
select value NorthwindModel.Customers(
c.CustomerID, c.CompanyName, c.ContactName, c.ContactTitle,
c.Address, c.City, c.Region, c.PostalCode, c.Country,
c.Phone, c.Fax)
from dbo.Customers as c
where c.Country = 'UK'
</QueryView>
<EntityTypeMapping TypeName="NorthwindModel.Customers">
<ModificationFunctionMapping>
<InsertFunction FunctionName="NorthwindModel.Store.InsCustomer">
<ScalarProperty Name="CustomerID"
ParameterName="Id"
Version="Current"/>
<ScalarProperty Name="CompanyName"
ParameterName="Company"
Version="Current"/>
</InsertFunction>
<DeleteFunction FunctionName="NorthwindModel.Store.DelCustomer">
<ScalarProperty Name="CustomerID"
ParameterName="Id"
Version="Original"/>
</DeleteFunction>
<UpdateFunction FunctionName="NorthwindModel.Store.UpdCustomer"/>
</ModificationFunctionMapping>
</EntityTypeMapping>
</EntitySetMapping>
<EntitySetMapping Name="WorldCustomers">
<QueryView>
using NorthwindModel;
select value NorthwindModel.Customers(
c.CustomerID, c.CompanyName, c.ContactName, c.ContactTitle,
c.Address, c.City, c.Region, c.PostalCode, c.Country,
c.Phone, c.Fax)
from dbo.Customers as c
where c.Country <> 'UK'
</QueryView>
<EntityTypeMapping TypeName="NorthwindModel.Customers">
<ModificationFunctionMapping>
<InsertFunction FunctionName="NorthwindModel.Store.InsCustomer">
<ScalarProperty Name="CustomerID"
ParameterName="Id"
Version="Current"/>
<ScalarProperty Name="CompanyName"
ParameterName="Company"
Version="Current"/>
</InsertFunction>
<DeleteFunction FunctionName="NorthwindModel.Store.DelCustomer">
<ScalarProperty Name="CustomerID"
ParameterName="Id"
Version="Original"/>
</DeleteFunction>
<UpdateFunction FunctionName="NorthwindModel.Store.UpdCustomer"/>
</ModificationFunctionMapping>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping>
and then I can go and write code such as;
using (NorthwindEntities e = new NorthwindEntities())
{
Console.WriteLine(e.WorldCustomers.Count());
Console.WriteLine(e.UkCustomers.Count());
}
Ok, this post is getting long and I've had it in my drafts folder for weeks so I'll publish it here and add to it in subsequent posts.
Alternatively, if someone thinks it's a good idea to enumerate the scenarios here then it'd be great if they picked this up and ran with it :-)