Generating idempotent SQL migration script in Entity Framework Core when it contains stored procedures throws error when running on database

Question

I have a few stored procedures that I'd like to be created during a migration step in Entity Framework Core.

I added a new migration, and in the Up() method I put this simple script:

var sp = @"CREATE PROCEDURE [dbo].[MyStoredProcedureFromMigration]
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT * FROM Students WHERE FirstName LIKE '%dummy%'
    END";

migrationBuilder.Sql(sp);

In my Azure Devops pipeline, I generate the idempotent migration script with a DotNetCoreCli task:

  - task: DotNetCoreCLI@2
    displayName: 'Create EntityFramework migration script'
    inputs:
      command: custom
      custom: ef
      arguments: 'migrations script --idempotent --project $(Build.SourcesDirectory)/src/[MigrationsProject].csproj --startup-project $(Build.SourcesDirectory)/src/[MigrationsProject].csproj --output $(System.ArtifactsDirectory)/script.sql'
      workingDirectory: $(Build.SourcesDirectory)

But Entity Framework Core then generates this script:

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20240827132758_NameOfMigration'
)
BEGIN
    CREATE PROCEDURE [dbo].[MyStoredProcedureFromMigration]
       AS
       BEGIN
          SET NOCOUNT ON;
          SELECT * FROM Students WHERE FirstName LIKE '%dummy%'
       END
END;
GO

which returns an error when running from a SqlAzureDacpacDeployment task in the pipeline:

Incorrect syntax near the keyword 'PROCEDURE'.

If I remove the --idempotent argument, the script is valid but then I get errors because some of the tables already exist, as the generated script is no longer idempotent.

How can I generate a valid new migration script that runs without errors when it contains the creation of stored procedures?

possible duplicate of Creating a stored procedure if it does not already exist — GSerg
– GSerg, Commented Aug 28, 2024 at 12:11
@GSerg, entity framework is generating the script for me, so I have little control over the outcome. — Reinder Wit
– Reinder Wit, Commented Aug 28, 2024 at 12:17
But you have control over what you put in that var sp. stackoverflow.com/questions/20715292/…? — GSerg
– GSerg, Commented Aug 28, 2024 at 12:25
You can do something like: EXEC('CREATE OR ALTER ...') but there should probably be a better way — siggemannen
– siggemannen, Commented Aug 28, 2024 at 21:50

Gabriel Rainha · Accepted Answer · 2025-09-02 19:44:05Z

0

Answered here: https://stackoverflow.com/a/76690011/4905310

Summary:

If you plan to use idempotent scripts, you need to wrap every statement that must be the only one on the batch into an EXEC command. That includes creating SPs, triggers, views, among others.

So your code should look something like:

var sp = @"CREATE PROCEDURE [dbo].[MyStoredProcedureFromMigration]
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT * FROM Students WHERE FirstName LIKE '%dummy%'
    END";

migrationBuilder.Sql($"EXEC('{sp}')");

PS: code not tested for error. Double-check it before copy-pasting.

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

answered Sep 2 at 19:44

Gabriel Rainha

1,7431 gold badge21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generating idempotent SQL migration script in Entity Framework Core when it contains stored procedures throws error when running on database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related