dotnet
Add docs for renaming tables/columns
I'd like to rename a table in Entity Framework Core, code first. I do not want to drop and create the table, as this would lose all my data. As I search for information on this, I see that the vast majority of it is outdated.
This best source I found is by Mitchel Sellers. But this also seems outdated. I'm not sure how much difference it makes but he declares his table as:
public DbSet Widgets { get; set; }
When the modern construct seems to use generics:
public DbSet<Widget> Widgets { get; set; }
I also found several articles that recommend entering the migration code manually. But I don't think this is ideal. I know if it's done correctly, all the associated constraint names are renamed as well. I would prefer not to enter the migration manually.
Why doesn't Microsoft publish the current and correct steps to do this?
Have you seen this section in the EF docs? It shows column renaming but table renaming works the same way.
@roji I actually renamed a table once before and it wasn't necessary to manually edit the migration. Somehow I specified the new name, I automatically created a migration and then renamed everything in code to match that name. But I can't recall the exact steps.
When I did, it create a migration that automatically renamed all the table constraint objects that referenced that table. It would take a lot of effort to try and do that manually.
So I'm convinced it's possible, and can't understand why Microsoft doesn't document steps that would work.
EF can detect renames only when the name change is very small and it's extremely clear that the operation is a rename; but distinguishing between a rename and a drop/create is inherently a heuristic. There is no additional undocumented feature that you're missing: if the name change is very small, EF detects it as a rename, otherwise it detects it as a drop/create, and it's up to you to edit the migrations.
@roji I've gathered some more information. It looks like you can use the Table attribute to specify that the name has changed. Once that migration is built, the attribute can be removed and everything in the code can be renamed normally.
This seems like how I did it some time ago. And I will try that today.
Since I did it once, I'm convinced it can be done. But I believe the steps should be better documented.
@SoftCircuits that isn't the case. You keep saying you're convinced it can be done and that it should be better documented - can you actually show the behavior you're referring to?
@roji Yeah, i'm trying to piece it together. But it was similar to what's in the Mitchel Sellers article I linked to in my original question. Also similar to the first answer at https://stackoverflow.com/questions/49799627/rename-a-foreign-key-in-entity-framework-core-without-dropping-data.
Ah, I see now - the idea is to keep the .NET property (or class) the same, and to only change the name of the table (or column) to which it is mapped in the database. I'm guessing when EF sees the same .NET property/class with only the store name changing, it infers a rename... Once that's done the attribute (or fluent API) can be removed.
I was indeed not aware of this - I agree it should be documented.
@roji Right. The only thing I couldn't understand is how renaming the entity with the Table attribute changes the table name. In the following DbContext code:
public DbSet<Widget> Widgets { get; set; }
I thought EF takes the name of the property name (Widgets) for the table name and not the name of the entity.
Anyway, I gave the exact steps I ended up using to rename my table (without renaming any columns) in this Stackoverflow answer.
The only thing I couldn't understand is how renaming the entity with the Table attribute changes the table name.
It's true that by default EF infers the table name from the DbSet property. However, the moment you explicitly set an entity type's table name (either via the [Table] attribute or via the ToTable() fluent API), that overrides that.
In any case, I'm making a note to add docs around this behavior.
@roji please make sure you explain the implications as well. This operation will break any views, sprocs or code depending on the previous name. Rebuilding an index is expensive and takes time. So does dropping and rebuilding FK constraints. Both can allow invalid data to be written to the table and all that protects the data is the concurrency semantics of each database, eg whether INSERT is permitted while another operation has a schema lock on a table.
People will shoot themselves in the foot and blame the database. At the very least mention some safe rename refactoring options, like using a view as an alias for the old table name while other code gets migrated.
PS: the operation would be a lot cheaper if the database's rename functionality was used instead of rebuilding indexes. sp_rename in SQL Server should be able to handle everything. In PostgreSQL it's probably slightly trickier, with ALTER ... RENAME TO ... to rename tables and FKs
the operation would be a lot cheaper if the database's rename functionality was used instead of rebuilding indexes. sp_rename in SQL Server should be able to handle everything.
That is exactly what EF already does; when the RenameTable() operation is used in migrations, on SQL Server this generates a call to sp_rename which, as you say, should take care of everything. Are we talking about the same thing here?