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?

Tough task. Do you have other columns in DriverWork and WorkPage tables? — Ivan Stoev
– Ivan Stoev, Commented Aug 28, 2018 at 9:34
@IvanStoev Yes, 3 other columns in WorkPage and 4 other columns in DriverWork. I did not state them in the question. — Avestura
– Avestura, Commented Aug 28, 2018 at 9:35
Can you add them? Because I'm thinking in rename table + create new table + insert some data direction. — Ivan Stoev
– Ivan Stoev, Commented Aug 28, 2018 at 9:41

Ivan Stoev · Accepted Answer · 2018-08-28 10:59:58Z

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");
}

I've generated the first migration and it is like yours. But after update-database it gives the same error: 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'.
Looks like you didn't make the WorkPageId column initially nullable as suggested? Check this line AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());, it shouldn't contain nullable: false

Collectives™ on Stack Overflow

Changing structure of database and moving data in Entity Framework

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related