In this post I’m going to show how to use EF 6.1 Code First to model polymorphic associations between a base class and two derived classes. In EF this is called “table-per-type” (TPT) inheritance. Microsoft has a walkthrough on using EF to map TPT inheritance in a domain model. Unfortunately it was written before EF Code First and is now dated. A search turned up some information here and there but it too was dated. It took me the better part of an afternoon to get it working in EF Code First so I thought I should post the solution. More after the jump…
Before I begin it’s useful to define the problem. Let’s say I have a factory with several machines. Each machine has a fitting for a single widget. I have two types of widgets (call them sprockets and flywheels) that can fit a machine. Sprockets have teeth while flywheels have a rotational speed value measured as cycles per second. My first naive attempt at modeling this in the database might look like this:
Don’t laugh. I see this all the time. The problem here of course is that the foreign keys to our two types of widgets must be nullable. Either a machine has been fitted with a sprocket or it has been fitted with a flywheel. So only one of SprocketID or FlywheelID will have a value. This causes huge headaches in the domain model. We have to check for null. We have to know what kind of widget we want at design time. And what if we want a fitting for a third type of widget in the future? Are we going to add a third nullable foreign key to the Machine table?
So let’s step back from the database and go to the code where most of us are more comfortable. We know how to model such polymorphic associations in our domain. This is the old Gang of Four Bridge Pattern:
We’ve all known for a long time that the database management system with its set of normalized tables conflicts with the patterns we use in an object-oriented domain model. This is the impedance mismatch problem. So now the question is how best to model this in the database? EF 6.1 Code First supports polymorphic associations via a feature called table-per-type (TPT) inheritance. There are two others called table-per-concrete (TPC) and table-per-hierarchy (TPH) which I do not plan to discuss here.
Now let’s go about configuring EF Code First to create the tables for us from our domain model code above. First create a simple console app and import the latest Entity Framework nuget package. Then create a Models folder and put these classes into it:
public abstract class Widget
{
public int ID { get; set; }
public abstract string Run();
public virtual ICollection<Machine> Machines { get; set; }
}
public class Flywheel : Widget
{
public int RotationalSpeed { get; set; }
public override string Run()
{
return string.Format("Flywheel spinning at {0} cycles per second!", RotationalSpeed);
}
}
public class Sprocket : Widget
{
public int NumberOfTeeth { get; set; }
public override string Run()
{
return string.Format("Sprocket with {0} teeth engaging!", NumberOfTeeth);
}
}
public class Machine
{
public int ID { get; set; }
public int WidgetID { get; set; }
public string Name { get; set; }
public virtual Widget Widget { get; set; }
public string Run()
{
return Widget.Run();
}
}
Within the Models folder create a new folder called Mappings. We’re going to need to create four EF map classes, one for each class in the domain model:
public class WidgetMap : EntityTypeConfiguration<Widget>
{
public WidgetMap()
{
HasKey(i => i.ID);
ToTable("Widget");
Property(i => i.ID)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}
}
public class FlywheelMap : EntityTypeConfiguration<Flywheel>
{
public FlywheelMap()
{
HasKey(i => i.ID);
ToTable("Flywheel");
Property(i => i.RotationalSpeed);
}
}
public class SprocketMap : EntityTypeConfiguration<Sprocket>
{
public SprocketMap()
{
HasKey(i => i.ID);
ToTable("Sprocket");
Property(i => i.NumberOfTeeth);
}
}
public class MachineMap : EntityTypeConfiguration<Machine>
{
public MachineMap()
{
HasKey(i => i.ID);
ToTable("Machine");
Property(i => i.ID)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(i => i.WidgetID);
Property(i => i.Name);
// navigation property to tie machine to its widget
HasRequired(i => i.Widget)
.WithMany(i => i.Machines)
.HasForeignKey(i => i.WidgetID);
}
}
Let’s new up a couple of machines just to smoke test this implementation. In Program Main method add this code:
static void Main(string[] args)
{
var machine1 = new Machine()
{
ID = 1,
Name = "Machine 1",
Widget = new Sprocket()
{
NumberOfTeeth = 24
}
};
var machine2 = new Machine()
{
ID = 1,
Name = "Machine 2",
Widget = new Flywheel()
{
RotationalSpeed = 5
}
};
Console.WriteLine(machine1.Run());
Console.WriteLine(machine2.Run());
Console.ReadLine();
}
Run it and you should see the expected output:
Now we want to use EF Code First to create the database tables for us. Add your connection string element to the App.config file:
<connectionStrings>
<add name="WidgetContext"
connectionString="ConnectionStringGoesHere"
providerName="System.Data.SqlClient" />
</connectionStrings>
In the Models folder create an instance of a DbContext pointing to your database:
public class WidgetContext : DbContext
{
static WidgetContext()
{
Database.SetInitializer(new DropCreateDatabaseAlways<WidgetContext>());
}
public DbSet<Machine> Machines { get; set; }
public DbSet<Widget> Widgets { get; set; }
public DbSet<Sprocket> Sprockets { get; set; }
public DbSet<Flywheel> Flywheels { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new MachineMap());
modelBuilder.Configurations.Add(new WidgetMap());
modelBuilder.Configurations.Add(new SprocketMap());
modelBuilder.Configurations.Add(new FlywheelMap());
}
}
Now it’s just a matter of using the regular EF SaveChanges method to let EF do its part. So go back into Program.cs and in addition to displaying the output to the screen, save the two machines to the database:
static void Main(string[] args)
{
var machine1 = new Machine()
{
ID = 1,
Name = "Machine 1",
Widget = new Sprocket()
{
NumberOfTeeth = 24
}
};
var machine2 = new Machine()
{
ID = 1,
Name = "Machine 2",
Widget = new Flywheel()
{
RotationalSpeed = 5
}
};
// create database and tables and save both machines
var context = new WidgetContext();
context.Entry(machine1).State = EntityState.Added;
context.Entry(machine2).State = EntityState.Added;
context.SaveChanges();
Console.WriteLine(machine1.Run());
Console.WriteLine(machine2.Run());
Console.ReadLine();
}
If you have your connection string configured correctly and it is named “WidgetContext” then the database with its tables should have been created. Run this query to see the rows created among the four tables:
select * from machine m
inner join widget w on m.WidgetID = w.ID
left outer join sprocket s on w.ID = s.ID
left outer join flywheel f on w.ID = f.ID
And here are the results from the query:
If you look at the keys created by EF, the Widget table is set with an auto-incrementing ID just like we configured it in its map. The Sprocket and Flywheel tables use the same ID key with one difference. ID is both the primary key and the foreign key back to the Widget table. This is the table structure to support table-per-type inheritance in EF.
Very cool. Now let’s modify our program to fetch and display machines from the system. First go into your WidgetContext class and change the initializer so that it no longer drops and creates the database:
static WidgetContext()
{
//Database.SetInitializer(new DropCreateDatabaseAlways<WidgetContext>());
Database.SetInitializer<WidgetContext>(null);
}
Then modify the program itself to fetch and run the two machines:
static void Main(string[] args)
{
// fetch machine 1 from the database
var context = new WidgetContext();
var machine1 = context.Machines.Find(1);
var machine2 = context.Machines.Find(2);
Console.WriteLine(machine1.Run());
Console.WriteLine(machine2.Run());
Console.ReadLine();
}
If you run the console app again you’ll see the same two messages output from the Run methods that were called.