For an e-learning project that I coach, the team is using Entity Framework 5 in conjunction with SQL Server. During the data model design, we were facing an interesting situation where we had to combine between both EF inheritance strategies: the table per type (TPT) strategy and the table per hierarchy strategy (TPH).
In this article, I will share the technique that will allow us to use both techniques using fluent mapping.
The data model consists of five classes (I am using a reduced version for simplicity and confidentiality reasons) : the resource which is a learning resource such as a question or a questionnaire. There are two types of questions : class A questions and class B questions. The data model is illustrated in the class diagram below :
To persist the entities, we needed three tables : “Resource”, “Question” and “Questionnaire”. The first table contains the common fields shared by all the resources. In addition to that, the tables “Question” and “Questionnaires” will contain the specific data related to questions and questionnaires.
A minimalistic version of the database is shown in the diagram below :
Every questionnaire will have its fields split between the table “Questionnaire” and “Resource”. Every question will have its fields split between the table “Question” and “Resource”. Knowing that the class Question is abstract (so is the class Resource), the two tables (Question and Resource) will persist instances of the two classes “QuestionClassA” and “QuestionClassB”.
To achieve all this, we needed to combine the TPT and the TPH strategies in our mappings and as you could guess, it’s possible with Entity Framework.
The first step is to write the mappings for the class “Resource” :
public class ResourceMapping : EntityTypeConfiguration<Resource>
{
public ResourceMapping()
{
ToTable("Resource");
HasKey(e => e.Id);
Property(e => e.Id)
.HasColumnName("ResourceId")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(e => e.Text)
.HasColumnName("ResourceText")
.IsRequired();
}
}
This is a very conventional mapping where we map an auto-generated int key and a string field. After the resource mapping, we will map the entity “Questionnaire” knowing that there is a “1….0..1” relationship between the tables “Resource” and “Questionnaire”. In the questionnaire entity, there is an additional property called “QuestionnaireDate” mapped to a column with the same name. Below is the fluent mapping for the questionnaire :
public class QuestionnaireMapping : EntityTypeConfiguration<Questionnaire>
{
public QuestionnaireMapping()
{
ToTable("Questionnaire");
Property(e => e.QuestionnaireDate);
}
}
Notice the “ToTable” method that indicates that the fields are mapped to columns belonging to another table. That’s the key to the TPT strategy.
Finally, and that’s the interesting part, the mapping of the entity“Question”. The mapping below not only indicates that the entity is mapped to another table, but indicates also that the two descendant types are mapped to this table using the “Map” method :
public class QuestionMapping : EntityTypeConfiguration<Question>
{
public QuestionMapping()
{
ToTable("Question");
Property(e => e.AnswerId);
Map<QuestionClassA>(e => e.Requires("QuestionType").HasValue<byte>(1)).ToTable("Question");
Map<QuestionClassB>(e => e.Requires("QuestionType").HasValue<byte>(2)).ToTable("Question");
}
}
The TPH is achieved using a descriminator column that is called “QuestionType”. A value of 1 of the descriminator correspond to a class A question while a value of 2 corresponds to a class B one.
To test our mapping, I simply created a console application that creates sample data and lists it using the “Prepare” and “AddResource” methods :
private static void Prepare(MyContext context)
{
AddResource<QuestionClassA>(context, "question A", e => e.AnswerId = 1);
AddResource<QuestionClassB>(context, "question B", e => e.AnswerId = 5);
AddResource<Questionnaire>(context, "questionnaire", e => e.QuestionnaireDate = DateTime.Now);
context.SaveChanges();
}
private static void AddResource<T>(MyContext context, string text, Action<T> initMethod) where T : Resource, new()
{
T entity = new T()
{
Text = text
};
if (initMethod != null)
initMethod(entity);
context.Resources.Add(entity);
}
Enjoy. You can find attached with this blog the source code and the database script :
Download the code here
And the db script here