Entity Framework Code First - Table Per Type Inheritance - Insertion Issue?

Question

I'm having an issue inserting an instance of a subclass that inherits from a base class.

Consider the following code snippets from these POCOs:

public abstract class EntityBase<T> 
{   
    private T _id;       
    [Key]
    public T ID    
    {       
         // get and set details ommitted.   
    }
}
public abstract class PersonBase<T> : EntityBase<T>
{   
    // Details ommited.  
}

public class Applicant : PersonBase<int>
{   
    // Details ommitted for brevity.  
}

public class Employee : Applicant {}

Pretty standard inheritance right now. In our system, when an applicant finally becomes an employee, we collect extra data. If not hired, they remain an applicant with a limited set of information.

Now consider the fluent API that sets up the table-per-type inheritance model:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     base.OnModelCreating(modelBuilder);                
     // Set up table per type object mapping for the Visitor Hierarchy.       
     modelBuilder.Entity<Employee>().ToTable("Employees");
}

So far, so good...

If I look at the database this creates, I have a table named Applicants with an Id column of type int, auto-incrementing ID and I have an Employees table with an ID field as the primary key (non auto incrementing).

Basically, the ID field in the Employees table is a foreign key to the Applicants table.

This is what I want. I don't want a record into the Employees table corresponding to the Applicants table until they actually become an Employee.

The problem comes when I try to insert an Employee which comes down to this code:

public void PersistCreationOf(T entity)
{     
    DataContextFactory.GetDataContext().Set(typeof(T)).Add(entity);
}

The problem: It inserts a brand new applicant and Employee. I hooked it up to the Sql Profiler and looked at both insert queries that come down.

I want to just insert the Employee record with the ID it already has (the foreign key from the Visitors table).

I understand by default it needs to this: Obviously if you create a subclass and insert it, it needs to insert into both tables.

My question is is possible to tell the Framework - the base table already has information - just insert into the child table?

Thanks in advance!

did u try splitting the Employee entity in to the two tables? — Eranga

Slauma Slauma · Accepted Answer · 2011-06-15T14:41:08

Aside from sending raw SQL commands to insert the Employee minus Applicant properties fragment into the Employees table I believe it's impossible. You can either update or insert an entity. What you want is basically to update the base part of the Employee (or do nothing if nothing changed) and insert the derived part which is not possible.

Imagine what an ORM does: It maps key identities in the database to object identities in memory. Even in memory you couldn't achieve what you want: If you have an object in memory which is a Applicant, it is always an applicant. You cannot magically "upgrade" it to an Employee. You would have to create a new object of type Employee, copy the properties of the Applicant into the base properties of your new Employee and then delete the Applicant. The result is a new object with a new object identity.

I think you have to follow the same procedure in EF. Your Employee will be a new entity with new rows in both Applicant and Employee table and you need to delete the old Applicant. If you have autogenerated keys it will be a new identity with a new ID. (If you hadn't autogenerated keys you could supply the old ID again after deleting the old Applicant, thus "faking" an unchanged identity.) This will of course create big potential trouble if you have references to the old applicant with FK constraints.

Perhaps inheritance is not optimal for this scenario to "upgrade" an applicant into an employee. An optional navigation property (1-to-0...1 relationship) inside of the Applicant which refers to another entity containing the additional properties which make the applicant an employee would solve the problem. This navigation property could be set or not, letting you distinguish between an applicant and applicant which is also an employee. And you would not need to delete and change the ID of the applicant when you make it an employee.

(As said, "I believe". Maybe there is a hidden way, I didn't see.)

Entity Framework Code First - Table Per Type Inheritance - Insertion Issue?

1 Answers