I'm a huge fan of Entity Framework Migrations, using them for almost all client projects. In a given week, I will see dozens of changes deploy to production successfully across our portfolio of projects. However, there is always that "one task" that results in a failure and each time it happens it triggers an update to our internal training and review processes, and it has to do with repeatable scripting and the use of the "idempotent" flag. I thought I'd share our experiences with everyone, to help prevent issues for others.
The Issue
Our issue is simple, yet a bit hard to predict, if you are not laser focused during your development and code review processes. As part of our build/deploy process we script out Entity Framework migrations using the idempotent flag to create a script that should be truly re-runnable.
We then utilize the Azure-SQL GitHub Action to apply the changes to the target environment. The upside to this process is that we do not need to introduce additional steps, we can safely run this process after EVERY deployment, and we get consistent deployments. However, there are two situations where your script creations may seem to work, but fail as soon as you deploy them via idempotent scripts:
- Changes that require a column that is not yet added, OR, changes to a column that no longer exists
- Statements that must be the first, or only item in a batch.
Lets consider this very simple, update statement to modify some records.
migrationBuilder.Sql(@"UPDATE MyTable SET MyNewColumnAmount = MyOldColumnAmount WHERE MyOldColumnAmount IS NOT NULL");
This seems like an innocent script. If you apply a migration with this script via the command-line or via a bundle, it works perfectly. However, if you try to run an idempotent script it fails! Why? Because your column didn't exist when it tried to parse/evaluate the query.
The Fix
The workaround is pretty simple and it works for the example above, and for statements such as "CREATE PROCEDURE..." or "CREATE VIEW...." which require themselves to be the only statements on a line. Simply wrap your change within an EXEC statement, so the above example would be updated to the following:
migrationBuilder.Sql(@"EXEC ('UPDATE MyTable
SET MyNewColumnAmount = MyOldColumnAmount
WHERE MyOldColumnAmount IS NOT NULL;')");
By simply doing this, the generated idempotent script will successfully execute in all situations, and the existing behaviors for "If not exists" will prevent the execution in the future.
I hope this quick tip is helpful for you! Feel free to share similar "gotcha" moments you have experienced.