title: Converting One-To-One to a One-To-Many relationship Issues
body: <p>I'm experiencing some extreme difficulty in changing a one-to-one relationship, to a one-to-many relationship without totally wiping out the database and starting over (which I cannot reasonably do to a production database). Entity Framework 6 with auto migrations enabled (also cannot change this).</p>
  
  <p>The original database schema called for a one-to-one relation between these classes (unrelated properties have been removed):</p>
  
  <pre><code>public class Job
      {
          [Key]
          public int JobIb { get; set; }
  
          public virtual JobSite Location { get; set; }
      }
  </code></pre>
  
  <pre><code>public class JobSite
      {
          [Key]
          [ForeignKey("Job")]
          public int JobSiteID { get; set; }
  
          [Required]
          public virtual Job Job { get; set; }
      }
  </code></pre>
  
  <p>Subsequently the requirement changed for it to be an one-to-many (a JobSite could have multiple Jobs)</p>
  
  <pre><code>public class Job
      {
          [Key]
          public int JobIb { get; set; }
  
          public virtual JobSite Location { get; set; }
      }
  </code></pre>
  
  <pre><code>public class JobSite
      {
          [Key]
          public int JobSiteID { get; set; }
  
          public virtual ICollection&lt;Job&gt; Jobs { get; set; }
      }
  </code></pre>
  
  <p>This matches Convention #3 on <a href="https://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx" rel="nofollow noreferrer">EntityFrameworkTutorial</a> and <a href="https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application#the-courses-and-officeassignment-navigation-properties" rel="nofollow noreferrer">Microsoft's</a> navigation properties example.</p>
  
  <p><strong>First approach moved further down for archiving. Long story short there, you can't make an existing column an identity column</strong></p>
  
  <p>My next approach was to completely drop the JobSites table and the relation to the Job table in one migration, and then re-establish them in a second migration:</p>
  
  <p>The focus here is on why the subsequent update doesn't trigger EF to think the model is in alignment with the database...</p>
  
  <p><strong>Step1</strong></p>
  
  <pre><code>public override void Up()
          {
              DropForeignKey("dbo.JobSites", "JobSiteID", "dbo.Jobs");
              DropIndex("dbo.JobSites", new[] { "JobSiteID" });
              DropTable("dbo.JobSites");
          }
  
          public override void Down()
          {
              CreateTable(
                  "dbo.JobSites",
                  c =&gt; new
                      {
                          JobSiteID = c.Int(nullable: false),
                      })
                  .PrimaryKey(t =&gt; t.JobSiteID);
  
              CreateIndex("dbo.JobSites", "JobSiteID");
              AddForeignKey("dbo.JobSites", "JobSiteID", "dbo.Jobs", "JobIb");
          }
  </code></pre>
  
  <p><strong>Step 2</strong></p>
  
  <pre><code>public override void Up()
          {
              CreateTable(
                  "dbo.JobSites",
                  c =&gt; new
                      {
                          JobSiteID = c.Int(nullable: false, identity: true),
                      })
                  .PrimaryKey(t =&gt; t.JobSiteID);
  
              AddColumn("dbo.Jobs", "Location_JobSiteID", c =&gt; c.Int());
              CreateIndex("dbo.Jobs", "Location_JobSiteID");
              AddForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites", "JobSiteID");
          }
  
          public override void Down()
          {
              DropForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites");
              DropIndex("dbo.Jobs", new[] { "Location_JobSiteID" });
              DropColumn("dbo.Jobs", "Location_JobSiteID");
              DropTable("dbo.JobSites");
          }
  </code></pre>
  
  <p>Initially this works. However, if I delete the local database, and pull a fresh copy of the production database, and update-database running the step 1 and step 2 migrations I get an error of <strong>System.InvalidOperationException: 'The model backing the 'ApplicationDbContext' context has changed since the database was created.</strong></p>
  
  <p>So that's pretty frustrating. If I add-migration Temp, to see what it thinks has changed in the model, I get exactly the same scaffold I did originally, like it didn't register the step 1 and step 2 migrations.</p>
  
  <p><strong>Temp</strong></p>
  
  <pre><code>public override void Up()
          {
              DropIndex("dbo.JobSites", new[] { "JobSiteID" });
              RenameColumn(table: "dbo.Jobs", name: "JobSiteID", newName: "Location_JobSiteID");
              DropPrimaryKey("dbo.JobSites");
              AlterColumn("dbo.JobSites", "JobSiteID", c =&gt; c.Int(nullable: false, identity: true));
              AddPrimaryKey("dbo.JobSites", "JobSiteID");
              CreateIndex("dbo.Jobs", "Location_JobSiteID");
              AddForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites", "JobSiteID");
          }
  
          public override void Down()
          {
              DropForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites");
              DropIndex("dbo.Jobs", new[] { "Location_JobSiteID" });
              DropPrimaryKey("dbo.JobSites");
              AlterColumn("dbo.JobSites", "JobSiteID", c =&gt; c.Int(nullable: false));
              AddPrimaryKey("dbo.JobSites", "JobSiteID");
              RenameColumn(table: "dbo.Jobs", name: "Location_JobSiteID", newName: "JobSiteID");
              CreateIndex("dbo.JobSites", "JobSiteID");
          }
  </code></pre>
  
  <p>I've read every single post I can on this.. Some suggest to clean and rebuild the solution prior to running the updates to make sure it's not using an out of date build, and this doesn't have any effect on the outcome.</p>
  
  <p>The only working solution I have, is to update-database -targetmigration:0 and then delete the existing migrations, and reset everything :(</p>
  
  <p>Obviously the moral of the story is, make sure your relationships are well defined, but I'm really hoping an Entity Framework guru out there can help me set this straight without wiping the production database.</p>
  
  <p>Other approaches I have tried: using FluentAPI to define the relationship. Same migration code is generated.</p>
  
  <p>I also have seen some posts suggesting to delete the _MigrationHistory table, but that doesn't seem viable. But looking at the MigrationHistory table, there is an additional auto migration that is run after the step1 and step2 migrations:</p>
  
  <pre><code>201907271958270_UpdateJobSites_Step1
  201907272000385_UpdateJobSites_Step2
  201907272038156_AutomaticMigration
  </code></pre>
  
  <p>Is there anyway to "see" what it runs after the step1 and step2 migritions? -Verbose doesn't give me any hints.</p>
  
  <p><strong>archived bit that doesn't work due to the inability to make an existing column an identity column</strong></p>
  
  <p>So all good so far. I am required to use explicit migrations for this situation, so once I run add-migration after updating the Entity objects, here is the migration generated:</p>
  
  <pre><code>public override void Up()
          {
              DropIndex("dbo.JobSites", new[] { "JobSiteID" });
              RenameColumn(table: "dbo.Jobs", name: "JobSiteID", newName: "Location_JobSiteID");
              DropPrimaryKey("dbo.JobSites");
              AlterColumn("dbo.JobSites", "JobSiteID", c =&gt; c.Int(nullable: false, identity: true));
              AddPrimaryKey("dbo.JobSites", "JobSiteID");
              CreateIndex("dbo.Jobs", "Location_JobSiteID");
              AddForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites", "JobSiteID");
          }
  
          public override void Down()
          {
              DropForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites");
              DropIndex("dbo.Jobs", new[] { "Location_JobSiteID" });
              DropPrimaryKey("dbo.JobSites");
              AlterColumn("dbo.JobSites", "JobSiteID", c =&gt; c.Int(nullable: false));
              AddPrimaryKey("dbo.JobSites", "JobSiteID");
              RenameColumn(table: "dbo.Jobs", name: "Location_JobSiteID", newName: "JobSiteID");
              CreateIndex("dbo.JobSites", "JobSiteID");
          }
  </code></pre>
  
  <p>Running update-database -verbose fails with</p>
  
  <pre><code>IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_JobSiteID' AND object_id = object_id(N'[dbo].[JobSites]', N'U'))
      DROP INDEX [IX_JobSiteID] ON [dbo].[JobSites]
  EXECUTE sp_rename @objname = N'dbo.Jobs.JobSiteID', @newname = N'Location_JobSiteID', @objtype = N'COLUMN'
  System.Data.SqlClient.SqlException (0x80131904): Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
  </code></pre>
  
  <p>Inspecting the tables in SQL Server Object Explorer, shows there are no fields, constraints, or otherwise that exist on the Jobs table called JobSiteID. On the JobSites table there is constraint:</p>
  
  <pre><code>CONSTRAINT [FK_dbo.JobSites_dbo.Jobs_JobSiteID] FOREIGN KEY ([JobSiteID]) REFERENCES [dbo].[Jobs] ([JobIb])
  </code></pre>
  
  <p>So a few things going on- It looks like the scaffolded migration thinks there is a column on Jobs named JobSiteID, which there is not, and it's trying to rename it to Location_JobSiteID. So.. I edited the migration to DropForeignKey (and add it in the down) and AddColumn of Location_JobSiteID on the jobs table (and drop it in the down):</p>
  
  <pre><code>public override void Up()
          {
              DropForeignKey("dbo.JobSites", "JobSiteID", "dbo.Jobs");
              DropIndex("dbo.JobSites", new[] { "JobSiteID" });
              AddColumn("dbo.Jobs", "Location_JobSiteID", c =&gt; c.Int(nullable: false));
              //RenameColumn(table: "dbo.Jobs", name: "JobSiteID", newName: "Location_JobSiteID");
              DropPrimaryKey("dbo.JobSites");
              AlterColumn("dbo.JobSites", "JobSiteID", c =&gt; c.Int(nullable: false, identity: true));
              AddPrimaryKey("dbo.JobSites", "JobSiteID");
              CreateIndex("dbo.Jobs", "Location_JobSiteID");
              AddForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites", "JobSiteID");
          }
  
          public override void Down()
          {
              DropForeignKey("dbo.Jobs", "Location_JobSiteID", "dbo.JobSites");
              DropIndex("dbo.Jobs", new[] { "Location_JobSiteID" });
              DropPrimaryKey("dbo.JobSites");
              AlterColumn("dbo.JobSites", "JobSiteID", c =&gt; c.Int(nullable: false));
              AddPrimaryKey("dbo.JobSites", "JobSiteID");
              DropColumn("dbo.Jobs", "Location_JobSiteID");
              //RenameColumn(table: "dbo.Jobs", name: "Location_JobSiteID", newName: "JobSiteID");
              CreateIndex("dbo.JobSites", "JobSiteID");
              AddForeignKey("dbo.JobSites", "JobSiteID", "dbo.Jobs", "JobIb");
          }
  </code></pre>
  
  <p>This migration is successful- or so it seems until I try to seed data or add new objects to the table. I receive a <strong>SqlException: Cannot insert the value NULL into column 'JobSiteID'</strong> error. Even though the column is being altered with identity: true before being reset at the primary key, it's not picking up the IDENTITY flag in the SQL..</p>
  
  <p><a href="https://i.stack.imgur.com/xWHwW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xWHwW.png" alt="IDENTITY missing from SQL"></a></p>
  
  <p><a href="https://i.stack.imgur.com/hQ6f5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hQ6f5.png" alt="Job Table with correct IDENTITY flag"></a></p>
  
  <p>So after rolling the DB back to pre-migration condition, I tried again after some searching, adding "[DatabaseGenerated(DatabaseGeneratedOption.Identity)]"</p>
  
  <pre><code>public class JobSite
      {
          [Key]
          [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
          public int JobSiteID { get; set; }
  
          public virtual ICollection&lt;Job&gt; Jobs { get; set; }
      }
  </code></pre>
  
  <p>But this results in the same migration code. So does using FluentAPI with <strong>.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);</strong></p>