Entity Framework Core: Scaffolding to Avoid Data Loss after to change Model/Entity

Question

I had the following Model:

public class Promotion : BaseModel
{
    [Required]
    public string Description { get; set; }

    [Required]
    public string Market { get; set; }

    [Required]
    public double Price { get; set; }
}

It was created a Migration that was updated to the database. And some promotions was inserted on the database. But I needed to change the Promotion Model to this:

public class Promotion : BaseModel
{
    [Required]
    public string Description { get; set; }

    [Required]
    public Market Market { get; set; }

    [Required]
    public double Price { get; set; }
}

public class Market : BaseModel
{
    [Required]
    public string Name { get; set; }

    [Required]
    public string Adress { get; set; }
}

When I added a new Migration, I got the alert: "An operation was scaffolding that may result in the loss of data. Please review the migrations for accuracy."

That's the auto generated Up method of the new Migration:

protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "Market",
            table: "Promotions");

        migrationBuilder.AddColumn<Guid>(
            name: "MarketId",
            table: "Promotions",
            nullable: false,
            defaultValue: new Guid("00000000-0000-0000-0000-000000000000"));

        migrationBuilder.CreateTable(
            name: "Markets",
            columns: table => new
            {
                Id = table.Column<Guid>(nullable: false),
                Adress = table.Column<string>(nullable: false),
                CreatedAt = table.Column<DateTime>(nullable: false),
                DeletedAt = table.Column<DateTime>(nullable: false),
                Name = table.Column<string>(nullable: false),
                UpdatedAt = table.Column<DateTime>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Markets", x => x.Id);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Promotions_MarketId",
            table: "Promotions",
            column: "MarketId");

        migrationBuilder.AddForeignKey(
            name: "FK_Promotions_Markets_MarketId",
            table: "Promotions",
            column: "MarketId",
            principalTable: "Markets",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    }

How can I update the database without lose data?

You have introduced a new entity (or owned entity) for the market. EF is not able to automatically resolve this as part of a migration. You will have to edit the migration manually to move the existing data to the new database layout. — poke, Jul 01 '18 at 22:01
I suspected that! But I don't know how to do it. I searched a lot, but I got no resolution. — Sérgio Damasceno, Jul 01 '18 at 23:05

score 18 · Accepted Answer · answered Jul 02 '18 at 20:15

The safe way to upgrade production DB is to break it up in multiple steps:

Add new Market entity and attach it to Promotion entity without dropping the existing column
EF will generate you a migration - CREATE TABLE + ADD FOREIGN KEY statements
Make your code to update/insert/select new values from both Market table and Market column preferring Market table
You deploy this. Now, you've got both old column with data and new table out there, being in sync for the new data.
Write a data migration, which will copy old values from Market column to Market table. Run it. Now you've got your data moved to the new Market table and new data sitting in Market table
Update your code to stop using old Market column. Deploy the change
Remove Market column from you entity. EF will generate migration where column will be dropped. Deploy this. You now have your data and schema migrated

Where is the documentation on this? this is very interesting I didn't know — Alexandra, May 13 '22 at 15:25
@I'mnewbie this is best practice not guide. This is best safe how to do that. — daremachine, May 13 '22 at 21:56

Entity Framework Core: Scaffolding to Avoid Data Loss after to change Model/Entity

1 Answers1