Change or rename a column name without losing data with Entity Framework Core 2.0

Question

I realised that I had spelt one of my column headers incorrectly so I changed it in the model and created a new migration to update it into the database. All worked perfectly until I realised that what actually appeared to happen was a new column replaced the existing column and erased all the data. As it happens, as this was a tutorial database, it was no big deal and a work of a few minutes to put the data back.

How/what do I do to update/rename a column without losing the data in it?

Not sure how this didn't come up in my search but here is a directly related post: Rename table field without losing data, using automatic migrations

Brucebrucellosis

What is happening is ef is dropping the column then creating a new one. You will need to run a dml statement against the database that will rename the column. If the column is a part of a constraint or index then you will most likely have to drop it. It that case create a new column set the new column = to the old column then drop the old column. You will need to have access to the database and elevated permissions. Kind of a nebular suggestion but I don't know what type of database you're talking to... – Grit

@Grit you mean we need to drop the index before a column rename? – Illusory

@NevilleNazerane no you shouldn't have to drop the index.I had ran into a situation where sql server wouldn't let me rename a column and I was forced to drop it. I know that if the table is published for replication you can't rename the column. However in most situations it doesn't seem to matter how the column is used or what constraint it has. I was able to rename with sp_rename...but again that's SQL Server. EF might have a way but I don't use it that often. – Grit

well, in that case, I guess it is always safer to drop and recreate the index. – Illusory

@Grit sorry, I assumed the procedure would be db agnostic. In this particular case I am working with SQL Server. I don't believe that this column is part of a constraint. – Brucebrucellosis

well ef for sql server sets up indices on its own for some columns such as fk – Illusory

Answer 1

EF Core creates its migrations by comparing your models to the current database snapshot (a c# class). It then uses this to create a migration file you can review. However, EF Core cannot always know if you replaced this column or created a new column. When you check your migration file, make sure there are no column drops, index drops (related to this column) etc. You can replace all these with something like this:

migrationBuilder.RenameColumn(
    name: "ColumnA",
    table: "MyTable",
    newName: "ColumnB");

Illusory

How does EF know a column was renamed? for all it knows, a field disappeared, and another one appeared. Unless it does some smart comparison (which would be super complicated in my opinion. e.g. changing UserId to Id is easy for a human, not AI. – Tetrarch

EF doesn't exactly "know". That is what this discussion is about. EF would try to guess and create a "migration file". We can then update the migration file manually with what exactly we need. – Illusory

I just renamed the property in the C# entity (using VS rename feature), ran add-migration "rename", and it generated exactly that, the RenameColumn function call. So it works on its own now! – Entity

Answer 2

migrationBuilder.RenameColumn usually works fine but sometimes you have to handle indexes as well.

migrationBuilder.RenameColumn(name: "Identifier", table: "Questions", newName: "ChangedIdentifier", schema: "dbo");

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: "IX_Questions_Identifier",
    table: "Questions");

migrationBuilder.RenameColumn(name: "Identifier", table: "Questions", newName: "ChangedIdentifier", schema: "dbo");

migrationBuilder.CreateIndex(
    name: "IX_Questions_ChangedIdentifier",
    table: "Questions",
    column: "ChangedIdentifier",
    unique: true,
    filter: "[ChangedIdentifier] IS NOT NULL");

Implant

Answer 3

I have renamed the column name for one of my model class.

public class Technology
{
    public Guid Id { get; set; }
    public string Tech(Changed this name to TechName){ get; set; }
    public bool? Active { get; set; }
    public int Total_number_of_sets { get; set; }
    public DateTime CreatedDateTime { get; set; }
    public DateTime? UpdatedDateTime { get; set; }
}

For Entity Framework Core do the below steps.

(1) add-migration "rename" after running this command <some_date_vale>_rename.cs will be created. which looks like below.

 public partial class rename : Migration
 {
     /// <inheritdoc />
     protected override void Up(MigrationBuilder migrationBuilder)
     {
         migrationBuilder.RenameColumn(
             name: "Tech",
             table: "Technologies",
             newName: "TechName");
     }

     /// <inheritdoc />
     protected override void Down(MigrationBuilder migrationBuilder)
     {
         migrationBuilder.RenameColumn(
             name: "TechName",
             table: "Technologies",
             newName: "Tech");
     }
 }

(2) Update-Database.

Gorrono