Converting One-To-One to a One-To-Many relationship Issues

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).

The original database schema called for a one-to-one relation between these classes (unrelated properties have been removed):

public class Job
    {
        [Key]
        public int JobIb { get; set; }

        public virtual JobSite Location { get; set; }
    }

public class JobSite
    {
        [Key]
        [ForeignKey("Job")]
        public int JobSiteID { get; set; }

        [Required]
        public virtual Job Job { get; set; }
    }

Subsequently the requirement changed for it to be an one-to-many (a JobSite could have multiple Jobs)

public class Job
    {
        [Key]
        public int JobIb { get; set; }

        public virtual JobSite Location { get; set; }
    }

public class JobSite
    {
        [Key]
        public int JobSiteID { get; set; }

        public virtual ICollection<Job> Jobs { get; set; }
    }

This matches Convention #3 on EntityFrameworkTutorial and Microsoft's navigation properties example.

First approach moved further down for archiving. Long story short there, you can't make an existing column an identity column

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:

The focus here is on why the subsequent update doesn't trigger EF to think the model is in alignment with the database...

Step1

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 => new
                    {
                        JobSiteID = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.JobSiteID);

            CreateIndex("dbo.JobSites", "JobSiteID");
            AddForeignKey("dbo.JobSites", "JobSiteID", "dbo.Jobs", "JobIb");
        }

Step 2

public override void Up()
        {
            CreateTable(
                "dbo.JobSites",
                c => new
                    {
                        JobSiteID = c.Int(nullable: false, identity: true),
                    })
                .PrimaryKey(t => t.JobSiteID);

            AddColumn("dbo.Jobs", "Location_JobSiteID", c => 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");
        }

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 System.InvalidOperationException: 'The model backing the 'ApplicationDbContext' context has changed since the database was created.

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.

Temp

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 => 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 => c.Int(nullable: false));
            AddPrimaryKey("dbo.JobSites", "JobSiteID");
            RenameColumn(table: "dbo.Jobs", name: "Location_JobSiteID", newName: "JobSiteID");
            CreateIndex("dbo.JobSites", "JobSiteID");
        }

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.

The only working solution I have, is to update-database -targetmigration:0 and then delete the existing migrations, and reset everything :(

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.

Other approaches I have tried: using FluentAPI to define the relationship. Same migration code is generated.

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:

201907271958270_UpdateJobSites_Step1
201907272000385_UpdateJobSites_Step2
201907272038156_AutomaticMigration

Is there anyway to "see" what it runs after the step1 and step2 migritions? -Verbose doesn't give me any hints.

archived bit that doesn't work due to the inability to make an existing column an identity column

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:

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 => 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 => c.Int(nullable: false));
            AddPrimaryKey("dbo.JobSites", "JobSiteID");
            RenameColumn(table: "dbo.Jobs", name: "Location_JobSiteID", newName: "JobSiteID");
            CreateIndex("dbo.JobSites", "JobSiteID");
        }

Running update-database -verbose fails with

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.

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:

CONSTRAINT [FK_dbo.JobSites_dbo.Jobs_JobSiteID] FOREIGN KEY ([JobSiteID]) REFERENCES [dbo].[Jobs] ([JobIb])

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):

public override void Up()
        {
            DropForeignKey("dbo.JobSites", "JobSiteID", "dbo.Jobs");
            DropIndex("dbo.JobSites", new[] { "JobSiteID" });
            AddColumn("dbo.Jobs", "Location_JobSiteID", c => c.Int(nullable: false));
            //RenameColumn(table: "dbo.Jobs", name: "JobSiteID", newName: "Location_JobSiteID");
            DropPrimaryKey("dbo.JobSites");
            AlterColumn("dbo.JobSites", "JobSiteID", c => 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 => 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");
        }

This migration is successful- or so it seems until I try to seed data or add new objects to the table. I receive a SqlException: Cannot insert the value NULL into column 'JobSiteID' 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..

So after rolling the DB back to pre-migration condition, I tried again after some searching, adding "[DatabaseGenerated(DatabaseGeneratedOption.Identity)]"

public class JobSite
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int JobSiteID { get; set; }

        public virtual ICollection<Job> Jobs { get; set; }
    }

But this results in the same migration code. So does using FluentAPI with .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

I finally found a solution. Keep in mind that this project is using Auto Migrations. Basically the Step1 and Step2 migration method was on track, however, there was a third Auto Migration being applied after Step1 and Step2. I wiped out the Step2 migration, essentially making that into an auto migration. When update-database is run, it applies just Step1, and then checks the model, and automatically creates the contents of Step2, and runs them, tricking the fellow developers machine into bringing the model and database back into alignment.

public partial class ResetJobSites : DbMigration
    {
        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 => new
                    {
                        JobSiteID = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.JobSiteID);

            CreateIndex("dbo.JobSites", "JobSiteID");
            AddForeignKey("dbo.JobSites", "JobSiteID", "dbo.Jobs", "JobIb");
        }
    }

PM> update-database
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201907291444500_ResetJobSites].
Applying explicit migration: 201907291444500_ResetJobSites.
Applying automatic migration: 201907291528423_AutomaticMigration.
PM> 

So to summarize: To convert a one-to-one to a one-to-many relationship when automatic migrations are turned on, and you need to preserve a production database (but willing to accept the loss of data on the table you're converting).

• Create an explicit migration to uncouple the two tables (I did this by commenting out the class, the nav property on the Job table, the DbSet in the DbContext, and whatever else it took to build the project).

• Make the changes model, add the nav property back to the Job table, an ICollection nav property, add the DbSet back to the DbContext, and let auto migrations run their course.

• Don't get yourself in this position in the first place.