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?

Answer 1

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

1. Add new Market entity and attach it to Promotion entity without dropping the existing column
2. EF will generate you a migration - CREATE TABLE + ADD FOREIGN KEY statements
3. Make your code to update/insert/select new values from both Market table and Market column preferring Market table
4. You deploy this. Now, you've got both old column with data and new table out there, being in sync for the new data.
5. 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
6. Update your code to stop using old Market column. Deploy the change
7. 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