How can I update the underlying data during this Entity Framework 6.x migration?

Question

Overview

I'm trying to update an existing application from the original design (simplified for clarity):

// Current design

class Audit {
  int Number { get; set; }
  Person Supervisor { get; set; }
}

class Person {
  int ID { get; set; }
  string Name { get; set; }
}

To the following design that uses a separate Supervisor class:

// Improved design

class Audit {
  int Number { get; set; }
  Supervisor Supervisor { get; set; }
}

class Supervisor {
  int ID { get; set; }
  Person Person { get; set; }
  // ...other supervisor properties
}

class Person {
  int ID { get; set; }
  string Name { get; set; }
}

The goal is to keep Supervisor related properties separate from the Person class and corresponding database table.

Question

How can I update the underlying data so that

• a new Supervisor row is created for each existing unique Supervisor_ID in the Audit table,
• the new Supervisor's Person_ID points to the correct person,
• the Audit's Supervisor_ID is updated to the ID of the new Supervisor.

Is this something that can be done in the EF migration's Up() function (or perhaps elsewhere) during the migration, or do I need to tackle this separately using handcrafted SQL scripts?

Answer 1

As soon as I realized it didn't have to be exclusively (1) DbContext or (2) standalone SQL script, I came up with this solution that runs the SQL during the migration:

public override void Up()
{
    CreateTable(
        "dbo.Supervisor",
        c => new
        {
            ID = c.Int(nullable: false, identity: true),
            Active = c.Boolean(nullable: false),
            Person_ID = c.Int(nullable: false),
        })
        .PrimaryKey(t => t.ID)
        .ForeignKey("dbo.Person", t => t.Person_ID, cascadeDelete: true)
        .Index(t => t.Person_ID);

    // for each unique Supervisor_ID in audits create a new supervisor with that Person_ID
    Sql("INSERT INTO Supervisor(Person_ID, Active) SELECT DISTINCT Supervisor_ID, 1 FROM Audit;");

    DropForeignKey("dbo.Audit", "Supervisor_ID", "dbo.Person");

    // for each audit update Supervisor_ID to the ID of the supervisor with that Person_ID
    Sql("UPDATE Audit SET Audit.Supervisor_ID = Supervisor.ID FROM Audit JOIN Supervisor ON Audit.Supervisor_ID = Supervisor.Person_ID;");

    AddForeignKey("dbo.Audit", "Supervisor_ID", "dbo.Supervisor");
}

I'd still be interested to know if there are any other ways besides this approach.

UPDATE 2021-04-26

It looks like the migration does not detect the change in FK so this answer is incomplete. Updated answer to include manual change of foreign keys.