Changing structure of database and moving data in Entity Framework

Question

My Database has entities Driver and DriverWork like this:

Edited:

public class Driver
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

And contains many rows in those entities. Now I've added another Entity with name WorkPage, which changes relation from this:

DriverWork --> Driver

To this:

DriverWork --> WorkPage --> Driver

Models after the change (Driver is still the old one):

public class Driver
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }
}

public class WorkPage
{
    [Key]
    public int Id { get; set; }

    public byte CommissionPercentage { get; set; }

    public bool IsClosed { get; set; }

    public DateTime? DateClosed { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int WorkPageId { get; set; }

    [ForeignKey(nameof(WorkPageId))]
    public WorkPage WorkPage { get; set; }
}

After adding a new migration, I knew that I should make changes in the Up method because some drivers currently don't have any WorkPages and I should at least add one WorkPage for them, also current DriverWork items must change their DriverId value to a valid WorkPageId that is associated with that Driver. But I don't know how should I do this migration in EF6?

Without making any changes in Up method, EF gives this error after running update-database command:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.DriverWorks_dbo.WorkPages_WorkPageId". The conflict occurred in database "{dblocation}\KHORSHIDDATA.MDF", table "dbo.WorkPages", column 'Id'.

Any help?

Answer 1

Sine it's a tough transformation, I would suggest performing it with two passes.

First, keep the DriverId FK in the DriverWork and make DriverId in WorkPage optional (nullable):

public class WorkPage
{
    [Key]
    public int Id { get; set; }

    public byte CommissionPercentage { get; set; }

    public bool IsClosed { get; set; }

    public DateTime? DateClosed { get; set; }

    public int? DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }

    public int WorkPageId { get; set; }

    [ForeignKey(nameof(WorkPageId))]
    public WorkPage WorkPage { get; set; }
}

Generate new migration. It should be something like this:

public override void Up()
{
    CreateTable(
        "dbo.WorkPage",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                CommissionPercentage = c.Byte(nullable: false),
                IsClosed = c.Boolean(nullable: false),
                DateClosed = c.DateTime(),
                DriverId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
        .Index(t => t.DriverId);

    AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");
}

Execute the migration. Then restore the desired entity model by removing the Driver from DriverWork and making DriverId in WorkPage required (non null):

public class WorkPage
{
    [Key]
    public int Id { get; set; }

    public byte CommissionPercentage { get; set; }

    public bool IsClosed { get; set; }

    public DateTime? DateClosed { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int WorkPageId { get; set; }

    [ForeignKey(nameof(WorkPageId))]
    public WorkPage WorkPage { get; set; }
}

Generate a second new migration. It should look something like this:

public override void Up()
{
    DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
    DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
    DropIndex("dbo.DriverWork", new[] { "DriverId" });
    DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
    AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
    DropColumn("dbo.DriverWork", "DriverId");
}

Use Sql methods to populate the WorkPage table data and update the WorkTableId FK before making it required. For instance, insert the following at the beginning of the Up method:

Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");

Execute the migration and you are done.

Actually the Sql calls which transform the data can be at the end of the first migration Up method.

Knowing all that in advance, you can keep your new model as is (skip the first step) and simply replace the generated migration Up method with the union of the above two, e.g.

public override void Up()
{
    CreateTable(
        "dbo.WorkPage",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                CommissionPercentage = c.Byte(nullable: false),
                IsClosed = c.Boolean(nullable: false),
                DateClosed = c.DateTime(),
                DriverId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
        .Index(t => t.DriverId);

    AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");

    Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
    Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");

    DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
    DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
    DropIndex("dbo.DriverWork", new[] { "DriverId" });
    DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
    AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
    DropColumn("dbo.DriverWork", "DriverId");
}