Code First, EF 4.1: What is the best way to map the following classes with a database...?

Question

dear community of professional developers!

I'm trying to figure out how to make a mapping correctly using Code First.

Assume that there are a few classes. One container-class, one base-class and several descendant classes.

The base-class:

public class Base {

 public int Id { get; set; }

}

The descendant classes:

public class FirstDescendant {

 public string FirstProperty { get; set; }

}

public class SecondDescendant {

 public int SecondProperty { get; set; }

}

public class ThirdDescendant {

 public int ThirdProperty { get; set; }

}

The container-class:

public class Container {

 public int Id { get; set; }

 public ICollection<FirstDescendant> FirstDescendants { get; set; }

 public ICollection<SecondDescendant> SecondDescendants { get; set; }

 public ICollection<ThirdDescendant> ThirdDescendants { get; set; }

}

By default, after creating the DbContext and running my code I get two tables with the following structure:

The Container table

Id

The Base table

Id

FirstProperty

Container_Id1

SecondProperty

Container_Id2

ThirdProperty

Container_Id3

So I get a table named Base, which contains the idential three fields that have the idential role of the foreign key.

Now the questions:

1. I do not like that kind of redundancy. How to evaluate this situation professional community, is this normal?

2. Can I change this? Can I get a more clearn structure of the database?

3. What is the best way to map this classes?

I have already published a similar question but I still care about this issue.

Thank you for your answers and attention.

Answer 1

The table structure in the database you've got by default is normal and the result of Table-Per-Hierarchy (TPH) mapping. It is the default strategy to map a class hierarchy to relational database tables. In this strategy the properties of all derived classes are merged into a single table and the types are only distinguished by a discriminator column in the table.

If you want a separate table per class in your hierarchy you can use Table-Per-Type (TPT) strategy. But that's not the default and you have to specify this explicitely either by data annotations ...

[Table("FirstDescendants")]
public class FirstDescendant : Base
{
    public string FirstProperty { get; set; }
}
// the same with the other derived classes

... or in Fluent code:

modelBuilder.Entity<FirstDescendant>().ToTable("FirstDescendants");
// the same with the other derived classes

This way you'll get a table for your Base class which only contains the properties of this class (only Id in your example) and separate tables FirstDescendants and so on which have the properties of the derived classes. The tables are linked by a foreign key relationship and EF manages to load the required columns from the two tables to materialize an object of the type you are querying.

You can find an introduction to the several strategies how EF can map class hierarchies here:

Inheritance with EF Code First

Edit

In my opinion TPT is the cleaner approach to map derived classes into relational tables. TPH requires you to have nullable columns in the database just for the mapping where you possibly want to have the properties required in your model. But on the other hand TPH is likely to be more performant because queries don't need to join different tables.

A possible rule of thumb to decide could be:

• If the majority of properties is in the base class and the derived classes only add a few additional properties you can use TPH.

• If the base class contains none or only a few properties and most properties are in the derived classes then use TPT.

But the Pro and Contra of the approaches - also of the third one Table-Per-Concrete-Class (TPC) - is much better and in detail explained in the linked article series above.