Search code examples
c#asp.net-mvcasp.net-mvc-4database-migrationentity-framework-migrations

ASP.Net MVC migration: access field you're deleting


During a code-first migration, is there any way to access a field that is guaranteed to still exist in the database but may not exist in your model anymore?

Context: I am trying to switch from a one-to-many-relation (a one-way reference) to a many-to-many-relation (a mapping table). Of course I would like to retain my data by inserting one entry for each pre-existing reference. And the migration seems the right place to do it. If I had a guarantee that the migration is always run before the source field is removed from the model (presumably by checking out a version control commit), I could e.g. include the following in my migration (assuming the referencing model is named Foo and the referenced one is named Bar):

Models.MyApplicationContext db = new Models.MyApplicationContext();
foreach(Models.Foo foo in db.Foos)
{
    foo.Bars.Add(foo.Bar);
}

But unfortunately, on other developers' computers as well as in the production environment, the foo.Bar field will be gone from the model by the time this migration is run, so I will need a different way to access this field (which is still in the database). Do I have any options besides writing plain SQL?

Edit 1: Minimal Working Example reduced model classes:

Before the migration:

public class Foo
{
    public int Id { get; set; }
    public Bar Bar { get; set; }
    // or public int? BarId { get; set; }
    // (presumably only relevant for implementation details
    // unrelated to my question which I can figure out myself)
}

public class Bar
{
    public int Id { get; set; }
}

After the migration:

public class Foo
{
    public int Id { get; set; }
    public List<Bar> Bars { get; set; }
}

public class Bar
{
    public int Id { get; set; }
    public List<Foo> Foos { get; set; }
}

Solution

  • alter the generated migration to execute an sql statement that fills your many to many table before the column gets deleted.

    public partial class OneToMany : DbMigration
    {
        public override void Up()
        {
            DropForeignKey("dbo.Foos", "Bar_Id", "dbo.Bars");
            DropIndex("dbo.Foos", new[] { "Bar_Id" });
            CreateTable(
                "dbo.FooBars",
                c => new
                    {
                        Foo_Id = c.Int(nullable: false),
                        Bar_Id = c.Int(nullable: false),
                    })
                .PrimaryKey(t => new { t.Foo_Id, t.Bar_Id })
                .ForeignKey("dbo.Foos", t => t.Foo_Id, cascadeDelete: true)
                .ForeignKey("dbo.Bars", t => t.Bar_Id, cascadeDelete: true)
                .Index(t => t.Foo_Id)
                .Index(t => t.Bar_Id);
    
            // HERE:
            this.Sql("insert into FooBars select Id, Bar_Id from Foos");
    
            DropColumn("dbo.Foos", "Bar_Id");
        }
        ...
     }
    

    This has a few advantages that you get for free:

    • the statement runs inside the transaction so if something goes wrong the DB stays intact
    • you can fill your mapping table w/o loading the old entities - will perform good with a large Foos table