Tuesday, November 25, 2014

Execute SQL query in entity framework database migration

I had to add a new column via entity framework for my existing database. The new column should have the same value from another column in the same table. Before I read this stackoverflow post, I had done SQL query, by hand, after I upgraded my database schema. However, it is very easy to add a line in DbMigration, to execute some SQL. My DbMigration end up looking like this:

public partial class _201410291951 : DbMigration
{
  public override void Up()
  {
    AddColumn("dbo.Controls", "ControlResponsibleId", c => c.Int(nullable:true));
    CreateIndex("dbo.Controls", "ControlResponsibleId");
    AddForeignKey("dbo.Controls", "ControlResponsibleId", "dbo.TenantUsers", "Id");
    Sql("Update Controls Set ControlResponsibleId = OwnerId");
  }
  public override void Down()
  {
    DropForeignKey("dbo.Controls", "ControlResponsibleId", "dbo.TenantUsers");
    DropIndex("dbo.Controls", new[] { "ControlResponsibleId" });
    DropColumn("dbo.Controls", "ControlResponsibleId");
  }
}

stackoverflow:
http://stackoverflow.com/questions/13650257/adding-a-foreign-key-with-code-first-migration