Databases
StackOverflow2013
Postsdbo
POEF Code First migrations: Table Per Hierarchy Bug
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEF Code First migrations: Table Per Hierarchy Bug
    text
    copied!<ul> <li>Often we might need to use Entity Framework Code First with an existing database.<br> <ul> <li>The existing database may have a structure the allows "Table Per Hierarchy" inheritance.</li> </ul></li> <li>Or we might start with an object model that looks like:</li> </ul> <hr> <pre><code>public partial class Person { public int Id { get; set; } public string Discriminator { get; set; } public string Name { get; set; } public Nullable&lt;int&gt; StudentTypeId { get; set; } public virtual StudentType StudentType { get; set; } } public partial class StudentType { public StudentType() { this.People = new List&lt;Person&gt;(); } public int Id { get; set; } public string Name { get; set; } public virtual ICollection&lt;Person&gt; People { get; set; } } </code></pre> <p>We create the initial migration: </p> <pre><code>enable-migrations add-migration Initial </code></pre> <p>The migration looks like:</p> <pre><code>public override void Up() { CreateTable( "dbo.Person", c =&gt; new { Id = c.Int(nullable: false, identity: true), Discriminator = c.String(maxLength: 4000), Name = c.String(maxLength: 4000), StudentTypeId = c.Int(), }) .PrimaryKey(t =&gt; t.Id) .ForeignKey("dbo.StudentType", t =&gt; t.StudentTypeId) .Index(t =&gt; t.StudentTypeId); CreateTable( "dbo.StudentType", c =&gt; new { Id = c.Int(nullable: false, identity: true), Name = c.String(maxLength: 4000), }) .PrimaryKey(t =&gt; t.Id); } </code></pre> <p>To generate this database we:</p> <pre><code>update-database </code></pre> <p>This results in a database that we could have generated like this.</p> <pre><code>create table Person( Id int Identity(1,1) Primary key, Discriminator nvarchar(4000) null, StudentTypeId int null, ) create table StudentType( Id int Identity(1,1) Primary key, Name nvarchar(4000) not null ) alter table Person add constraint StudentType_Person foreign key (StudentTypeId) references StudentType(Id) </code></pre> <p>We use this database in production for a while...</p> <p>Now we want to add the concept of students that are different from just regular people.</p> <p>Entity Framework provides three approaches for representing inheritance. In this case we choose the "Table Per Hierarchy" approach.</p> <p>To implement this approach we modify our POCOs as follows:</p> <pre><code>public class Person { public int Id { Get; set; } public string Name { get; set } } public class Student : Person { public virtual StudentType StudentType { get; set; } public int? StudentTypeId { get; set; } } public class StudentType { public StudentType() { Students = new List&lt;Student&gt;(); } public int Id { get; set; } public string Name { get; set; } public virtual ICollection&lt;Student&gt; Students { get; set; } } </code></pre> <p>Note:</p> <ul> <li>Only Students have access to the <code>StudentType</code> property.</li> <li>We don't specify the <code>Discriminator</code> property in our <code>Person</code> class. EF Code First sees that <code>Student</code> inherits from <code>Person</code> and will add a <code>Discriminator</code> column to the Person table for us.</li> </ul> <p>Now we run:</p> <pre><code>add-migration Person_TPH </code></pre> <p>And we get this unexpected output.</p> <pre><code>public override void Up() { AddColumn("dbo.Person", "StudentType_Id", c =&gt; c.Int()); AlterColumn("dbo.Person", "Discriminator", c =&gt; c.String(nullable: false, maxLength: 128)); AddForeignKey("dbo.Person", "StudentType_Id", "dbo.StudentType", "Id"); CreateIndex("dbo.Person", "StudentType_Id"); } </code></pre> <p>It should not be adding the <code>StudentType_Id</code> column or index.</p> <p>We can be explicit by adding the 'StudentMap' class:</p> <pre><code>public class StudentMap : EntityTypeConfiguration&lt;Student&gt; { public StudentMap() { this.HasOptional(x =&gt; x.StudentType) .WithMany() .HasForeignKey(x =&gt; x.StudentTypeId); } } </code></pre> <p>But no joy..</p> <p>Indeed, if we delete the database and all the migrations. Then run <code>add-migration Initial</code> against our new model we get:</p> <pre><code>public override void Up() { CreateTable( "dbo.Person", c =&gt; new { Id = c.Int(nullable: false, identity: true), Name = c.String(maxLength: 4000), StudentTypeId = c.Int(), Discriminator = c.String(nullable: false, maxLength: 128), }) .PrimaryKey(t =&gt; t.Id) .ForeignKey("dbo.StudentType", t =&gt; t.StudentTypeId) .Index(t =&gt; t.StudentTypeId); CreateTable( "dbo.StudentType", c =&gt; new { Id = c.Int(nullable: false, identity: true), Name = c.String(nullable: false, maxLength: 100), }) .PrimaryKey(t =&gt; t.Id); } </code></pre> <p>In this "correct" version we see that EF Code First migrations uses the <code>StudentTypeId</code> column as expected.</p> <p><strong>Question</strong></p> <p>Given that the database already exists, is there a way to tell EF Code First migrations to use the existing <code>StudentTypeId</code> column.</p> <p>The GitHub repo that demonstrates the problem is here: </p> <pre><code>https://github.com/paulyk/ef_code_first_proof_of_tph_bug.git Git tags 1_add_migration_Initial 2_add_migration_person_TPH 3_add_studentMap </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload