CODEWITHSUNDEEP
.net-coreentity-framework-coreazure-pipelinesentity-framework-core-migrations
Tor Haugen
Tor Haugen

Reputation: 19637

How to avoid deploy errors with EF Core Migrations and Custom Migration Operations (custom SQL)

I'm using EF Core Migrations in my .NET Core projects, and deploy using DevOps pipelines.

In my build pipeline, I build a migration SQL script using a Command Line task to execute a dotnet ef migrations script command (using the --idempotent option), then execute it in the release pipeline using an Azure SQL Database deployment task. All fairly standard, to judge from a simple google search (eg. here), which is how I learned of the approach in the first place.

This is my problem: To achieve certain desired results, in some of my migrations I use Custom Migration Operations (migrationBuilder.Sql("...")) to execute some hand-crafted SQL as part of a migration.

However, as the project develops, and my DB schema changes over time, older such migrations inevitably contain SQL which no longer fits the schema. One would think this isn't a problem, as any migration is meant to be applied to a database with some specific schema version only. However, as it turns out, the dotnet ef migrations script command builds a SQL script with a bunch of conditional SQL block like this:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190123160628_SomeMigrationId')
BEGIN
    /* Generated or custom SQL here */
END;

Note: every migration is included in the script, and the IF NOT EXIST-clauses ensure that only the not-yet-applied ones are executed in the database.

However, the custom migration task SQL statements are included as-is in the script, and if they are outdated, they longer compile. Then the DB deployment task fails, as does the whole deployment.

Has anyone else had and solved this problem, or know another way to deploy migrations which doesn't have this issue? It seems to me this seriously affects the utility of the MigrationBuilder.Sql() command, which is the only way to arbitrarily "massage" data as part of a migration.

Upvotes: 2

Views: 677

Answers (1)

wiggy id
wiggy id

Reputation: 11

Very late, I know. Found this post because of the same issue, and this worked for my situation. (for versions of donet-ef 6.0.8 and 7.0.9)

Try adding EXEC() around your sql statement:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190123160628_SomeMigrationId')
BEGIN
    EXEC('/* Generated or custom SQL here */')
END;

This is the recommended practice by Microsoft:

EXEC is used when a statement must be the first or only one in a SQL batch. It can also be used to work around parser errors in idempotent migration scripts that can occur when referenced columns don't currently exist on a table.

https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli#arbitrary-changes-via-raw-sql

Upvotes: 1

Related Questions