Rename a foreign key in Entity Framework Core without dropping data

Question

I have two model classes:

public class Survey
{
    public int SurveyId { get; set; }
    public string Name { get; set; }
}

public class User
{
    public int UserId { get; set; }

    public int SurveyId { get; set; }
    public Survey Survey { get; set; }
}

I want to rename Survey to StudentSurvey, so it will have StudentSurveyId. I update the class name and the properties in the models accordingly and add a migration.

However, I get:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_User_Surveys_SurveyId". The conflict occurred in database "AppName", table "dbo.Survey", column 'SurveyId'.

I think it is trying to drop the data and because it requires data in the column (cannot be null) I'm seeing that error. But I don't want to drop the data. How can I just rename it?

Solution 1

EF Core treats the entity class rename as deleting the old entity and adding a new entity, hence generates a migration to drop the original table and create a new one.

The workaround requires the following steps:

(1) Before renaming the entity, "rename" the table and the PK column by using either ToTable and HasColumnName fluent API or data annotations. Also do the same for FK columns referencing the entity.

For instance:

[Table("StudentSurveys")]
public class Survey
{
    [Column("StudentSurveyId")]
    public int SurveyId { get; set; }
    public string Name { get; set; }
}

public class User
{
    public int UserId { get; set; }
    [Column("StudentSurveyId")]
    public int SurveyId { get; set; }
    public Survey Survey { get; set; }
}

(2) Add new migration. It will correctly rename the table, PK column, FK columns and the associated constraints:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
        name: "FK_Users_Surveys_SurveyId",
        table: "Users");

    migrationBuilder.DropPrimaryKey(
        name: "PK_Surveys",
        table: "Surveys");

    migrationBuilder.RenameTable(
        name: "Surveys",
        newName: "StudentSurveys");

    migrationBuilder.RenameColumn(
        name: "SurveyId",
        table: "Users",
        newName: "StudentSurveyId");

    migrationBuilder.RenameIndex(
        name: "IX_Users_SurveyId",
        table: "Users",
        newName: "IX_Users_StudentSurveyId");

    migrationBuilder.RenameColumn(
        name: "SurveyId",
        table: "StudentSurveys",
        newName: "StudentSurveyId");

    migrationBuilder.AddPrimaryKey(
        name: "PK_StudentSurveys",
        table: "StudentSurveys",
        column: "StudentSurveyId");

    migrationBuilder.AddForeignKey(
        name: "FK_Users_StudentSurveys_StudentSurveyId",
        table: "Users",
        column: "StudentSurveyId",
        principalTable: "StudentSurveys",
        principalColumn: "StudentSurveyId",
        onDelete: ReferentialAction.Cascade);
}

(3) Remove the annotations / fluent configuration and do the actual class/property rename:

public class StudentSurvey
{
    public int StudentSurveyId { get; set; }
    public string Name { get; set; }
}

public class User
{
    public int SurveyUserId { get; set; }
    public int StudentSurveyId { get; set; }
    public StudentSurvey StudentSurvey { get; set; }
}

rename the corresponding DbSet if any:

public DbSet<StudentSurvey> StudentSurveys { get; set; }

and you are done.

You can verify that by adding a new migration - it will have empty Up and Down methods.

Solution 2

The documentation suggests using MigrationBuilder.RenameColumn(). There is also RenameIndex(), RenameTable() and a stored procedure 'sp_rename' (in SQL Server) for renaming foreign keys. E.g:

migrationBuilder.Sql($"EXEC sp_rename 'dbo.{oldName}', '{newName}'");

Use the database scripting tool in SQL Server Management Studio (rt-click on DB | Tasks | Generate Scripts...) to generate schema scripts, with and without EF migration customizations, that can be compared. Then you'll know the EF naming is preserved.

EDIT: Added 'EXEC ...' to support generation of standalone T-SQL scripts

Comments

[egmfrs]

It's as though I need to add [Column("StudentSurveyId")] above every model that has a SurveyId foreign key in. I will try that...

[egmfrs]

That worked. I then applied rename refactoring (resharper) to get Survey changed to StudentSurvey in my project. And then your step 3 didn't work; drop and create statements showed in my migration code. I had forgotten to change my context: public DbSet<StudentSurvey> Surveys { get; set; } needed to become public DbSet<StudentSurvey> StudentSurveys { get; set; } then I hit the same problem. Because StudentSurveys needed to be StudentSurvey. In hindsight I should have pluralised the the [Table(StudentSurvey)] in my first annotation. I'd recommend updating your answer to consider all this.

[Ivan Stoev]

Ok. At least it was a good starting point :) In my test, I didn't rename the FK column. And was assuming the DbSet rename is part of the "rename" operation. Anyway, the answer is updated now, did I miss something?

[Ivan Stoev]

@PateeGutee Unfortunately seems like this doesn't work for EF6.

[zolty13]

In EF core 3: If I do not generate second (empty) migration then I have still in ModelSnapshot: b.Property<string>("Old") .HasColumnName("New")