EFCoreSafelyDeleteColumnExample

Shows one way to safely delete a column. Safely in this context means that it will be backwards compatible and allow 0 downtime releases.

The problem

When running a live application it's complicated to delete properties/columns in EF Core. This is because you want to make sure that you can deploy new code with zero downtime but also roll back to a previous version of the code in case you new version have some critical bugs.

The problem is that EF core (and old EF) is always being explicit about the columns in queries. This is not bad in general, just a problem in this situation.

Running this code

using (var db = new Context())
{
    var persons = db.Persons.ToList();
}

will generate this SQL

SELECT [p].[Id], [p].[Name], [p].[PropToBeDeleted]
FROM [Persons] AS [p]

Now imagine we are deploying new version that removes PropToBeDeleted from the database.

When we apply the migration, which is something that ideally should be done before actually deploying the new code, we will now be a in a state where all code that run that query will try to include a column that no longer exist. We will get a SqlException and the application is broken.

The same goes if we would actually be able to deploy the new code and there is a critical bug. For your sanity you want to be able to redeploy the previous version and figure out how to solve the bug in a calm and controlled way. If the column was deleted you can not do that.

The solution

We need to remove columns in a multi step process instead. To do this we will "hack" the migrations to separate the two steps.

PS. Projections could solve removing the column from queries. But you would then have to update this everywhere plus it would still break for updates.

Step #1: Leave the column in the database but stop all code from using it.

See commit for details: https://github.com/MikaelEliasson/EFCoreSafelyDeleteColumnExample/commit/bcad2af7d5631515936b0dd799fcaa0789820ea2

We remove the property from the entity, run Add-Migration, but comment out the code in the migration to delete this column. The reason for commenting the code out instead of just deleting it is that we will use exactly that code in the next step.

After this code is live the SQL query should now be:

SELECT [p].[Id], [p].[Name]
FROM [Persons] AS [p]

Step #2: Delete the column from the database.

See commit for details: https://github.com/MikaelEliasson/EFCoreSafelyDeleteColumnExample/commit/77afcd9a80c575eb637b9fa202cf5ccdd81f4e54

IMPORTANT: This step must be done after the changes in Step #1 is live on all production servers and you feel confident that you wouldn't want to roll back past Step #1. In our case we would probable let a least 2-3 releases happen between these two steps.

Here we just create a new migration (that will be empty) and copy the migration code that we commented out in Step #1 to this one. Now this can be deployed safely and the column will be gone.

The SQL generated by this code is the same as in Step #1. Which is the whole point.