实体框架迁移重命名表和列。

时间:2022-08-16 06:10:53

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.

我重新命名了一对实体及其导航属性,并在EF 5中生成了新的迁移。与EF迁移中的重命名一样,默认情况下它将删除对象并重新创建它们。这不是我想要的,所以我不得不从头开始构建迁移文件。

    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.

我所要做的就是重命名dbo。ReportSections dbo。ReportPages然后dbo。ReportSectionGroups dbo.ReportSections。然后我需要重命名dbo上的外键列。从Group_Id到Section_Id的reportpage。

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.

我将删除连接表的外键和索引,然后重命名表和外键列,然后再次添加索引和外键。我以为这行得通,但我得到了一个SQL错误。

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.

Msg 15248,级别11,状态1,过程sp_rename,第215行参数@objname是不明确的,或者声明的@objtype(列)是错误的。Msg 4902, 16级,状态1,第10行找不到对象“dbo”。因为它不存在或者你没有权限。

I'm not having an easy time figuring out what is wrong here. Any insight would be tremendously helpful.

我不是很容易就能搞清楚这里出了什么问题。任何见解都将非常有用。

6 个解决方案

#1


103  

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.

这就是我所需要的。重命名方法只生成对sp_rename系统存储过程的调用,我猜这将处理一切,包括带有新列名的外键。

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");
}

#2


17  

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:

如果您不喜欢手工编写/更改迁移类中所需的代码,可以采用两步方法,自动生成所需的RenameColumn代码:

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

第一步使用ColumnAttribute来引入新的列名,然后进行add-migration(例如,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) in the Package Manager Console

第2步更改属性名,并再次应用于包管理器控制台中的相同迁移(例如,已更改的Add-Migration ColumnChanged)

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.

如果查看迁移类,可以看到自动生成的代码是RenameColumn。

#3


3  

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.

要扩展Hossein Narimani Rad的答案,您可以使用System.ComponentModel.DataAnnotations.Schema重命名表和列。TableAttribute System.ComponentModel.DataAnnotations.Schema。ColumnAttribute分别。

This has a couple benefits:

这有几个好处:

  1. Not only will this create the the name migrations automatically, but
  2. 这不仅会自动创建名称迁移,而且还会自动创建。
  3. 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.
  4. 它还可以很好地删除任何外键,并根据新的表和列名重新创建它们,并给出外键并指定适当的名称。
  5. All this without losing any table data
  6. 所有这些都不会丢失任何表数据

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);
    }

#4


2  

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.

我刚刚在EF6(代码重命名实体)中尝试了相同的方法。我简单地重命名了这个类,并使用包管理器控制台添加了一个迁移,然后自动为我生成了一个使用RenameTable(…)的迁移。我必须承认,我确保对实体的唯一更改是重命名它,这样就不会有新的列或重命名的列,所以我不能确定这是EF6,还是EF(总是)能够检测到如此简单的迁移。

#5


1  

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

在EF Core(2.0)中,我使用以下语句来重命名表和列:

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: "Orders", newName: "OldColumnName", schema: "dbo");
    }

#6


0  

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.

可以将表名和列名指定为DbContext映射的一部分。那么在迁移中就不需要这样做了。

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");
            });
     }
}

#1


103  

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.

这就是我所需要的。重命名方法只生成对sp_rename系统存储过程的调用,我猜这将处理一切,包括带有新列名的外键。

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");
}

#2


17  

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:

如果您不喜欢手工编写/更改迁移类中所需的代码,可以采用两步方法,自动生成所需的RenameColumn代码:

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

第一步使用ColumnAttribute来引入新的列名,然后进行add-migration(例如,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) in the Package Manager Console

第2步更改属性名,并再次应用于包管理器控制台中的相同迁移(例如,已更改的Add-Migration ColumnChanged)

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.

如果查看迁移类,可以看到自动生成的代码是RenameColumn。

#3


3  

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.

要扩展Hossein Narimani Rad的答案,您可以使用System.ComponentModel.DataAnnotations.Schema重命名表和列。TableAttribute System.ComponentModel.DataAnnotations.Schema。ColumnAttribute分别。

This has a couple benefits:

这有几个好处:

  1. Not only will this create the the name migrations automatically, but
  2. 这不仅会自动创建名称迁移,而且还会自动创建。
  3. 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.
  4. 它还可以很好地删除任何外键,并根据新的表和列名重新创建它们,并给出外键并指定适当的名称。
  5. All this without losing any table data
  6. 所有这些都不会丢失任何表数据

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);
    }

#4


2  

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.

我刚刚在EF6(代码重命名实体)中尝试了相同的方法。我简单地重命名了这个类,并使用包管理器控制台添加了一个迁移,然后自动为我生成了一个使用RenameTable(…)的迁移。我必须承认,我确保对实体的唯一更改是重命名它,这样就不会有新的列或重命名的列,所以我不能确定这是EF6,还是EF(总是)能够检测到如此简单的迁移。

#5


1  

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

在EF Core(2.0)中,我使用以下语句来重命名表和列:

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: "Orders", newName: "OldColumnName", schema: "dbo");
    }

#6


0  

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.

可以将表名和列名指定为DbContext映射的一部分。那么在迁移中就不需要这样做了。

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");
            });
     }
}