I'm going to jump right into the Entity Framework here, with a simple how-to. If you want a gentle introduction into this technology, you can probably read some future posts, but you will be far better off reading the ADO.Net teamblog or better still, read all of the entries of Danny Simmons.
An OR-mapper needs to be flexible in mapping your domain classes, so you can describe your domain as truthfully as possible. The entity framework positions itself to be more than an OR-Mapper: it sees itself as a translation technology between models. One of those models is defined by your database, the other by your domain (your conceptual model). It's interesting to see what types of inheritance are possible. In this post I will give a sample of the Table Per Type (TPT) inheritance, where you define an inheritance in your conceptual model and use different tables to persist the data.
The way to implement this is fairly straightforward and documented, however, I certainly had some problems getting it to work. As did Scott Allen, so I thought I would create a small walk through for this scenario.
As you can see, my database (SqlExpress) has 3 tables. The 'Person' table, a customer table and an employee table. There are foreign key relations between them, with the Person table being the Primary/Unique Key Base Table and the Customer and Employee tables being the Foreign Key Base Tables. It is important to note that the PersonID column is set up to automatically generate ID's, and the CustomerID and EmployeeID columns are not: we want to create a situation where an object like Customer is persisted to these two tables (customer and person) and the Person table is the one supplying a new ID. The Customer table will just use that PersonID as it's own CustomerID.
That concludes the database schema, next up is the conceptual model, ugh, I mean domain model:
This is how I want to my conceptual model to look like.
When you have just generated the model from the database, the foreign key relations are visible. Remove them! Then, also remove the CustomerID and EmployeeID properties that were generated. Add the two inheritance links.
Since Customer already maps to Person (through the inheritance) you do not have to map these again. You do have to add a mapping to the customer table. The key point here is that the CustomerID column needs to be mapped to the PersonID property. Same goes for Employee.
Now, this is all there is to it. Before we dive into the XML generated, let me just point this out: when you update from the database again, the designer will fail. This is a major problem, where the synchronization just isn't good enough. It will encounter problems with the foreign key associations that it has recreated. I hope this gets fixed in the following CTP.
Now, although deceptively easy through the designer, I have a gut-feeling that it's going to be best to actually understand all the XML mapping that is generated. Just like the WPF and WF designers immediately made me turn to learning Xaml and Xoml, I feel that this designer is going to force me to learn CSDL and CS mapping pretty quickly.
When opening the edmx file with the XML editor, quickly use ctrl-E-F to format everything. The designer creates very long lines.
The SSDL content is unimportant, since it represents the database and should not be tinkered with here.
The CSDL is as follows:
<edmx:ConceptualModels>
<Schema Namespace="EntityFrameworkTestModel1" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
<EntityContainer Name="EntityFrameworkTestEntities4">
<EntitySet Name="Person" EntityType="EntityFrameworkTestModel1.Person" />
</EntityContainer>
<EntityType Name="Customer" BaseType="EntityFrameworkTestModel1.Person">
<Property Name="CustomerDiscount" Type="Int32" Nullable="false" />
</EntityType>
<EntityType Name="Employee" BaseType="EntityFrameworkTestModel1.Person">
<Property Name="FunctionName" Type="String" Nullable="false" MaxLength="50" />
</EntityType>
<EntityType Name="Person">
<Key>
<PropertyRef Name="PersonID" />
</Key>
<Property Name="PersonID" Type="Int32" Nullable="false" />
<Property Name="Firstname" Type="String" Nullable="false" MaxLength="50" />
<Property Name="Lastname" Type="String" Nullable="false" MaxLength="50" />
</EntityType>
</Schema>
</edmx:ConceptualModels>
Note a container with only one set: the Person set. Then the three types are defined. Only the Person type has a key identified, the Customer and the Employee do not!
The C-S mapping then, glues this model to the SSDL:
<Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
<EntityContainerMapping StorageEntityContainer="dbo" CdmEntityContainer="EntityFrameworkTestEntities4">
<EntitySetMapping Name="Person">
<EntityTypeMapping TypeName="IsTypeOf(EntityFrameworkTestModel1.Person)">
<MappingFragment StoreEntitySet="Person">
<ScalarProperty Name="PersonID" ColumnName="PersonID" />
<ScalarProperty Name="Firstname" ColumnName="Firstname" />
<ScalarProperty Name="Lastname" ColumnName="Lastname" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="IsTypeOf(EntityFrameworkTestModel1.Customer)">
<MappingFragment StoreEntitySet="Customer">
<ScalarProperty Name="PersonID" ColumnName="CustomerID" />
<ScalarProperty Name="CustomerDiscount" ColumnName="CustomerDiscount" />
</MappingFragment>
</EntityTypeMapping>
<EntityTypeMapping TypeName="IsTypeOf(EntityFrameworkTestModel1.Employee)">
<MappingFragment StoreEntitySet="Employee">
<ScalarProperty Name="PersonID" ColumnName="EmployeeID" />
<ScalarProperty Name="FunctionName" ColumnName="FunctionName" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping>
Here we see the PersonID being mapped to either the CustomerID column or the EmployeeID column. Interesting!
In code, this allows us to do the following:
EntityFrameworkTestEntities4 context = new EntityFrameworkTestEntities4();
// inserting some
Person p = new Person();
p.Firstname = "Ruurd";
p.Lastname = "Boeke";
context.AddToPerson(p);
Customer c = new Customer();
c.Firstname = "Silvia";
c.Lastname = "Banana";
c.CustomerDiscount = 10;
context.AddToPerson(c);
Employee e = new Employee();
e.Firstname = "Ian";
e.Lastname = "Mort";
e.FunctionName = "Developer";
context.AddToPerson(e);
context.SaveChanges();
IQueryable<Person> persons =
from person in context.Person
select person;
foreach (Person person in persons)
{
Console.WriteLine("{0}, {1} is a {2}",
person.Firstname, person.Lastname, person.GetType().Name);
}
Console.ReadLine();
Did you notice that the context was first saved, before the Linq query was executed? It's easy to forget, but the query is executed on the database, not on a Union of objects in the database and in the context.
The output is as expected:
Ruurd, Boeke is a Person
Silvia, Banana is a Customer
Ian, Mort is a Employee
What I really like about this mapping strategy, is the fact that no discriminator column was needed. The system knows that an employee is an employee because of the existence of the record in the employee table. Let's quickly look at the generated query
SELECT
CASE WHEN (( NOT (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL))) AND ( NOT (([UnionAll1].[C3] = 1) AND ([UnionAll1].[C3] IS NOT NULL)))) THEN '0X' WHEN (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL)) THEN '0X0X' ELSE '0X1X' END AS [C1],
[Extent1].[PersonID] AS [PersonID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname],
CASE WHEN (( NOT (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL))) AND ( NOT (([UnionAll1].[C3] = 1) AND ([UnionAll1].[C3] IS NOT NULL)))) THEN CAST(NULL AS int) WHEN (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL)) THEN [UnionAll1].[CustomerDiscount] END AS [C2],
CASE WHEN (( NOT (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL))) AND ( NOT (([UnionAll1].[C3] = 1) AND ([UnionAll1].[C3] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(50)) WHEN (([UnionAll1].[C2] = 1) AND ([UnionAll1].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(50)) ELSE CAST( [UnionAll1].[C1] AS nvarchar(50)) END AS [C3]
FROM [dbo].[Person] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[CustomerID] AS [CustomerID],
[Extent2].[CustomerDiscount] AS [CustomerDiscount],
CAST(NULL AS nvarchar(max)) AS [C1],
cast(1 as bit) AS [C2],
cast(0 as bit) AS [C3]
FROM [dbo].[Customer] AS [Extent2]
UNION ALL
SELECT
[Extent3].[EmployeeID] AS [EmployeeID],
CAST(NULL AS int) AS [C1],
[Extent3].[FunctionName] AS [FunctionName],
cast(0 as bit) AS [C2],
cast(1 as bit) AS [C3]
FROM [dbo].[Employee] AS [Extent3]) AS [UnionAll1] ON [Extent1].[PersonID] = [UnionAll1].[CustomerID]
A left outer join was done with a union of customer and employee. Also note the CASE statements in the main select statement.
I leave it up to you to decide if such SQL statements are okay in your environment. I see no problem with them but I can see them growing pretty fast.
Let's take a look at what happens if you only want your customers. The following Linq expression can be used:
IQueryable<Customer> customers =
from customer in context.Person.OfType<Customer>()
select customer;
When executed, the following SQL is executed:
SELECT
'0X0X' AS [C1],
[Extent1].[CustomerID] AS [CustomerID],
[Extent2].[Firstname] AS [Firstname],
[Extent2].[Lastname] AS [Lastname],
[Extent1].[CustomerDiscount] AS [CustomerDiscount]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[PersonID]
All in all very straightforward.