Entity Framework Migrations renames tables and columns

Question

I renamed a couple of objects and their navigation properties and created a new migration to EF 5. As usual, with renaming in EF transitions, by default he was going to delete objects and recreate them. This is not what I wanted, so I pretty much had to create a migration file from scratch.

public override void Up() { DropForeignKey("dbo.ReportSectionGroups", "Report_Id", "dbo.Reports"); DropForeignKey("dbo.ReportSections", "Group_Id", "dbo.ReportSectionGroups"); DropForeignKey("dbo.Editables", "Section_Id", "dbo.ReportSections"); DropIndex("dbo.ReportSectionGroups", new[] { "Report_Id" }); DropIndex("dbo.ReportSections", new[] { "Group_Id" }); DropIndex("dbo.Editables", new[] { "Section_Id" }); RenameTable("dbo.ReportSections", "dbo.ReportPages"); RenameTable("dbo.ReportSectionGroups", "dbo.ReportSections"); RenameColumn("dbo.ReportPages", "Group_Id", "Section_Id"); AddForeignKey("dbo.ReportSections", "Report_Id", "dbo.Reports", "Id"); AddForeignKey("dbo.ReportPages", "Section_Id", "dbo.ReportSections", "Id"); AddForeignKey("dbo.Editables", "Page_Id", "dbo.ReportPages", "Id"); CreateIndex("dbo.ReportSections", "Report_Id"); CreateIndex("dbo.ReportPages", "Section_Id"); CreateIndex("dbo.Editables", "Page_Id"); } public override void Down() { DropIndex("dbo.Editables", "Page_Id"); DropIndex("dbo.ReportPages", "Section_Id"); DropIndex("dbo.ReportSections", "Report_Id"); DropForeignKey("dbo.Editables", "Page_Id", "dbo.ReportPages"); DropForeignKey("dbo.ReportPages", "Section_Id", "dbo.ReportSections"); DropForeignKey("dbo.ReportSections", "Report_Id", "dbo.Reports"); RenameColumn("dbo.ReportPages", "Section_Id", "Group_Id"); RenameTable("dbo.ReportSections", "dbo.ReportSectionGroups"); RenameTable("dbo.ReportPages", "dbo.ReportSections"); CreateIndex("dbo.Editables", "Section_Id"); CreateIndex("dbo.ReportSections", "Group_Id"); CreateIndex("dbo.ReportSectionGroups", "Report_Id"); AddForeignKey("dbo.Editables", "Section_Id", "dbo.ReportSections", "Id"); AddForeignKey("dbo.ReportSections", "Group_Id", "dbo.ReportSectionGroups", "Id"); AddForeignKey("dbo.ReportSectionGroups", "Report_Id", "dbo.Reports", "Id"); } 

All I'm trying to do is rename dbo.ReportSections to dbo.ReportPages and then dbo.ReportSectionGroups to dbo.ReportSections . Then I need to rename the foreign key column to dbo.ReportPages from Group_Id to Section_Id .

I delete the foreign keys and indexes linking the tables together, then I rename the tables and the foreign key column, then add the indexes and foreign keys again. I assumed this would work, but I am getting an SQL error.

Msg 15248, level 11, state 1, sp_rename procedure, line 215 Either the @objname parameter is ambiguous, or the declared @objtype code (COLUMN) is incorrect. Msg 4902, Level 16, State 1, Line 10 The object "dbo.ReportSections" cannot be found because it does not exist or you do not have permissions.

It’s not easy for me to figure out what’s wrong here. Any insight would be extremely helpful.

Answer 1

Nevermind. I made this path more difficult than it really should have been.

That was all I needed. Renaming methods simply generate a call to the sp_rename system stored procedure, and I think that everything has been taken care of, including foreign keys with a new column name.

 public override void Up() { RenameTable("ReportSections", "ReportPages"); RenameTable("ReportSectionGroups", "ReportSections"); RenameColumn("ReportPages", "Group_Id", "Section_Id"); } public override void Down() { RenameColumn("ReportPages", "Section_Id", "Group_Id"); RenameTable("ReportSections", "ReportSectionGroups"); RenameTable("ReportPages", "ReportSections"); } 

Answer 2

If you do not like to write / modify the required code in the Migration class manually, you can use the two-step approach, which is automatically RenameColumn code RenameColumn :

Step One Use ColumnAttribute to enter a new column name and then add a migration (e.g. Add-Migration ColumnChanged )

 public class ReportPages { [Column("Section_Id")] //Section_Id public int Group_Id{get;set} } 

Step two: change the property name and apply it again to the same migration (e.g. Add-Migration ColumnChanged -force ) in the package manager console

 public class ReportPages { [Column("Section_Id")] //Section_Id public int Section_Id{get;set} } 

If you look at the Migration class, you will see that the automatically generated code is called RenameColumn .

Answer 3

To deploy a bit to Hossein Narimani Rad, you can rename the table and columns using System.ComponentModel.DataAnnotations.Schema.TableAttribute and System.ComponentModel.DataAnnotations.Schema.ColumnAttribute respectively.

This has several advantages:

• This will not only automatically create name migration, but
• it will also fine delete any foreign keys and recreate them against new table and column names, providing foreign keys and proper constants.
• All this without loss of table data.

For example, adding [Table("Staffs")] :

 [Table("Staffs")] public class AccountUser { public long Id { get; set; } public long AccountId { get; set; } public string ApplicationUserId { get; set; } public virtual Account Account { get; set; } public virtual ApplicationUser User { get; set; } } 

Will generate a migration:

  protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.DropForeignKey( name: "FK_AccountUsers_Accounts_AccountId", table: "AccountUsers"); migrationBuilder.DropForeignKey( name: "FK_AccountUsers_AspNetUsers_ApplicationUserId", table: "AccountUsers"); migrationBuilder.DropPrimaryKey( name: "PK_AccountUsers", table: "AccountUsers"); migrationBuilder.RenameTable( name: "AccountUsers", newName: "Staffs"); migrationBuilder.RenameIndex( name: "IX_AccountUsers_ApplicationUserId", table: "Staffs", newName: "IX_Staffs_ApplicationUserId"); migrationBuilder.RenameIndex( name: "IX_AccountUsers_AccountId", table: "Staffs", newName: "IX_Staffs_AccountId"); migrationBuilder.AddPrimaryKey( name: "PK_Staffs", table: "Staffs", column: "Id"); migrationBuilder.AddForeignKey( name: "FK_Staffs_Accounts_AccountId", table: "Staffs", column: "AccountId", principalTable: "Accounts", principalColumn: "Id", onDelete: ReferentialAction.Cascade); migrationBuilder.AddForeignKey( name: "FK_Staffs_AspNetUsers_ApplicationUserId", table: "Staffs", column: "ApplicationUserId", principalTable: "AspNetUsers", principalColumn: "Id", onDelete: ReferentialAction.Restrict); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.DropForeignKey( name: "FK_Staffs_Accounts_AccountId", table: "Staffs"); migrationBuilder.DropForeignKey( name: "FK_Staffs_AspNetUsers_ApplicationUserId", table: "Staffs"); migrationBuilder.DropPrimaryKey( name: "PK_Staffs", table: "Staffs"); migrationBuilder.RenameTable( name: "Staffs", newName: "AccountUsers"); migrationBuilder.RenameIndex( name: "IX_Staffs_ApplicationUserId", table: "AccountUsers", newName: "IX_AccountUsers_ApplicationUserId"); migrationBuilder.RenameIndex( name: "IX_Staffs_AccountId", table: "AccountUsers", newName: "IX_AccountUsers_AccountId"); migrationBuilder.AddPrimaryKey( name: "PK_AccountUsers", table: "AccountUsers", column: "Id"); migrationBuilder.AddForeignKey( name: "FK_AccountUsers_Accounts_AccountId", table: "AccountUsers", column: "AccountId", principalTable: "Accounts", principalColumn: "Id", onDelete: ReferentialAction.Cascade); migrationBuilder.AddForeignKey( name: "FK_AccountUsers_AspNetUsers_ApplicationUserId", table: "AccountUsers", column: "ApplicationUserId", principalTable: "AspNetUsers", principalColumn: "Id", onDelete: ReferentialAction.Restrict); } 

Answer 4

In EF Core (2.0), I use the following operators to rename tables and columns:

Regarding renaming tables:

  protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.RenameTable(name: "OldTableName", schema: "dbo", newName: "NewTableName", newSchema: "dbo"); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.RenameTable(name: "NewTableName", schema: "dbo", newName: "OldTableName", newSchema: "dbo"); } 

Regarding renaming columns:

  protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.RenameColumn(name: "OldColumnName", table: "TableName", newName: "NewColumnName", schema: "dbo"); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.RenameColumn(name: "NewColumnName", table: "TableName", newName: "OldColumnName", schema: "dbo"); } 

Answer 5

I just tried the same thing in EF6 (rename the name of the object first). I just renamed the class and added porting using the package manager console and voila, a migration using RenameTable (...) was automatically created for me. I must admit that I was convinced that the only change in essence was to rename it so that there were no new columns or renamed columns, so I can’t be sure if this is an EF6 thing or just EF (always) is able to detect such simple migrations.

Answer 6

Table and column names can be specified as part of the DbContext display. Then there is no need to do this in the context of migration.

 public class MyContext : DbContext { protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Restaurant>() .HasMany(p => p.Cuisines) .WithMany(r => r.Restaurants) .Map(mc => { mc.MapLeftKey("RestaurantId"); mc.MapRightKey("CuisineId"); mc.ToTable("RestaurantCuisines"); }); } } 

Answer 7

In ef core, you can change the migration created after adding the migration. And then do a database update. An example gave below:

 protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.RenameColumn(name: "Type", table: "Users", newName: "Discriminator", schema: "dbo"); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.RenameColumn(name: "Discriminator", table: "Users", newName: "Type", schema: "dbo"); }