Moving items to list of items in an Entity Framework migration

Question

I'm using Asp.Net MVC 5 with Entity Framework 6. I've got a table like this:

public class Recipe
{
    [Required]
    public virtual Food Food { get; set; }

    //...rest
}

Now I want to move the Food to a list of foods. Like this:

public class Recipe
{
    public virtual IList<Food> Foods { get; set; }

    //... rest
}

If I try this and add a migration, I'll lose the data related to Food Ids. And won't know which recipe is for which food.

I tried keeping the Food and add the list like this:

public class Recipe
{
    [Required]
    public virtual Food Food { get; set; }

    public virtual IList<Food> Foods { get; set; }

    //... rest
}

But the migration adds a second foreign key and fails to update. Here's the migration code that fails:

    public override void Up()
    {
        DropForeignKey("dbo.Recipes", "Food_ID", "dbo.Foods");
        DropIndex("dbo.Recipes", new[] { "Food_ID" });
        AddColumn("dbo.Foods", "Recipe_Id", c => c.Int());
        AddColumn("dbo.Foods", "Recipe_Id1", c => c.Int());
        AlterColumn("dbo.Recipes", "Food_ID", c => c.Int());
        CreateIndex("dbo.Foods", "Recipe_Id");
        CreateIndex("dbo.Foods", "Recipe_Id1");
        CreateIndex("dbo.Recipes", "Food_ID");
        AddForeignKey("dbo.Foods", "Recipe_Id", "dbo.Recipes", "Id");
        AddForeignKey("dbo.Foods", "Recipe_Id1", "dbo.Recipes", "Id");
        AddForeignKey("dbo.Recipes", "Food_ID", "dbo.Foods", "ID");
    }

How can I move the single Food to a list of Foods without losing the current data in the table?

Answer 1

You'll need to customize your migration after creating it.

1. Modify your entity class

   public class Recipe
   {
       public virtual IList<Food> Foods { get; set; }
   
       //... rest
   }
   

2. Use Add-Migration to scaffold the migration

3. Modify the generated migration to fill the new column with data. You'll want to fill the new Recipe_Id column in Foods with the IDs of the previously related rows.

   public override void Up()
   {
       DropForeignKey("dbo.Recipes", "Food_ID", "dbo.Foods");
       DropIndex("dbo.Recipes", new[] { "Food_ID" });
       AddColumn("dbo.Foods", "Recipe_Id", c => c.Int());
   
       // Update values from existing data, not sure if the syntax is perfect
       Sql(@"UPDATE dbo.Foods SET Recipe_Id = r.Id
             FROM (SELECT Id, Food_ID FROM dbo.Recipes) AS r
             WHERE Foods.ID = r.Food_ID");
   
       DropColumn("dbo.Recipes", "Food_ID");
       CreateIndex("dbo.Foods", "Recipe_Id");
       AddForeignKey("dbo.Foods", "Recipe_Id", "dbo.Recipes", "Id");
   }