Entity Framework Migrations renaming tables and columns

Question

I renamed a a couple entities and their navigation properties and generated a new Migration in EF 5  As is usual with renames in EF migrations  by default it was going to drop objects and recreate them  That isn t what I wanted so I pretty much had to build the 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 on dbo ReportPages from Group Id to Section Id   I am dropping the foreign keys and indexes linking the tables together  then I am renaming the tables and the foreign key column  then I m adding the indexes and foreign keys again  I assumed this was going to work but I am getting a SQL error      Msg 15248  Level 11  State 1  Procedure sp rename  Line 215   Either the parameter  objname is ambiguous or the claimed  objtype  COLUMN  is wrong    Msg 4902  Level 16  State 1  Line 10   Cannot find the object  dbo ReportSections  because it does not exist or you do not have permissions    I m not having an easy time figuring out what is wrong here  Any insight would be tremendously helpful

User · Answer

In EF Core  I use the following statements to rename tables and columns   As for 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            As for 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

User · Answer

In ef core  you can change the migration that was created after add migration  And then do update-database  A sample has given 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

User · Answer

Table names and column names can be specified as part of the mapping of DbContext  Then there is no need to do it in migrations   public class MyContext   DbContext       protected override void OnModelCreating DbModelBuilder modelBuilder                modelBuilder Entity lt Restaurant gt                 HasMany p   gt  p Cuisines               WithMany r   gt  r Restaurants               Map mc   gt                                mc MapLeftKey  RestaurantId                    mc MapRightKey  CuisineId                    mc ToTable  RestaurantCuisines

User · Answer

If you don t like writing changing the required code in the Migration class manually  you can follow a two-step approach which automatically make the RenameColumn code which is required   Step One Use the ColumnAttribute to introduce the new column name and then add-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 again apply to 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 can see the automatically code generated is RenameColumn

User · Answer

I just tried the same in EF6  code first entity rename   I simply renamed the class and added a migration using the package manager console and voila  a migration using RenameTable      was automatically generated for me  I have to admit that I made sure the only change to the entity was renaming it so no new columns or renamed columns so I cannot be certain if this is an EF6 thing or just that EF was  always  able to detect such simple migrations

User · Answer

Nevermind  I was making this way more complicated than it really needed to be   This was all that I needed  The rename methods just generate a call to the sp rename system stored procedure and I guess that took care of everything  including the foreign keys with the 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

User · Answer

To expand a bit on Hossein Narimani Rad s answer  you can rename both a table and columns using System ComponentModel DataAnnotations Schema TableAttribute and System ComponentModel DataAnnotations Schema ColumnAttribute respectively    This has a couple benefits    Not only will this create the the name migrations automatically  but  it will also deliciously delete any foreign keys and recreate them against the new table and column names  giving the foreign keys and constaints proper names  All this without losing any 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 the 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

User · Answer

For EF Core migrationBuilder RenameColumn usually works fine but sometimes you have to handle indexes as well  migrationBuilder RenameColumn name   quot Identifier quot   table   quot Questions quot   newName   quot ChangedIdentifier quot   schema   quot dbo quot     Example error message when updating database   Microsoft Data SqlClient SqlException  0x80131904   The index  IX Questions Identifier  is dependent on column  Identifier   The index  IX Questions Identifier  is dependent on column  Identifier   RENAME COLUMN Identifier failed because one or more objects access this column   In this case you have to do the rename like this  migrationBuilder DropIndex      name   quot IX Questions Identifier quot       table   quot Questions quot     migrationBuilder RenameColumn name   quot Identifier quot   table   quot Questions quot   newName   quot ChangedIdentifier quot   schema   quot dbo quot     migrationBuilder CreateIndex      name   quot IX Questions ChangedIdentifier quot       table   quot Questions quot       column   quot ChangedIdentifier quot       unique  true      filter   quot  ChangedIdentifier  IS NOT NULL quot

[c#] Entity Framework Migrations renaming tables and columns

Examples related to c#

Examples related to sql-server

Examples related to entity-framework

Examples related to entity-framework-5

Examples related to entity-framework-migrations