SQL Server Migrations: Idempotent scripts fails with 'invalid column name' (needs EXEC) #12911

christianholsen · 2018-08-07T06:34:20Z

I have encountered a problem with idempotent migration scripts that make them fail in our continous integration system. The reason is that some migration scripts are parsed even though they are not going to be executed.

This issue is much like issue #10717. This issue can be reproduced by manipulating EF types i a sequence of migrations.

In example I have these two migrations, the first is adding a unique index for a nullable column, the second is removing the column:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180519095519_AddIndexToMyTable')
BEGIN
        CREATE UNIQUE INDEX [NameIndex] ON [MyTable] ([Name]) WHERE [Name] IS NOT NULL;
END;

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180720070812_RemoveNameFromMyTable')
BEGIN
    DROP INDEX [NameIndex] ON [MyTable];
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180720070812_RemoveNameFromMyTable')
BEGIN
    DECLARE @var19 sysname;
    SELECT @var19 = [d].[name]
    FROM [sys].[default_constraints] [d]
    INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
    WHERE ([d].[parent_object_id] = OBJECT_ID(N'[MyTable]') AND [c].[name] = N'Name');
    IF @var19 IS NOT NULL EXEC(N'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @var19 + '];');
    ALTER TABLE [MyTable] DROP COLUMN [Name];
END;
GO

This will work fine in the first execution, just as #10717

The second time this in run in our CI system, MyTable will no longer have the column "Name" and will fail in the execution of

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180519095519_AddIndexToMyTable')
BEGIN
        CREATE UNIQUE INDEX [NameIndex] ON [MyTable] ([Name]) WHERE [Name] IS NOT NULL;
END;

The SQL error message is

Invalid column name 'Name'.

This happens even though the migration "AddIndexToMyTable" has been installed and the "If not exists.." statement should avoid execution of the script, but as i happens it is parsed anyways, making it fail!

Steps to reproduce

Create a entity type with a nullable field with a unique index using fluent API

protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<MyTable>().HasIndex(c => c.Name).IsUnique();
        }

Make a new migration
Remove the field Name from MyTable and remove the unique index in OnModelCreating
Make a new migration
Create an idempotent migration script with

dotnet ef migrations script -o migrationscript.sql --startup-project MyProject.csproj --configuration release --idempotent

execute migrationscript.sql twice on the database making it fail

Proposal for a solution

This problem only occurs because the script section is parsed in the sql server even though it is not going to be executed. If this could be avoided the problem would not occur. It could be solved by using dynamic a sql script, as:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180519095519_AddIndexToMyTable')
BEGIN
        EXEC('CREATE UNIQUE INDEX [NameIndex] ON [MyTable] ([Name]) WHERE [Name] IS NOT NULL;')
END;

As it is i'm adding the exec to the migration script with powershell in CI, but I believe that everyone would be happier if we could rely on the script produced by EFCore :-)

Further technical details

EF Core version: 2.1.1

The text was updated successfully, but these errors were encountered:

ajcvickers · 2018-08-10T19:46:17Z

Note for triage: I was able to reproduce this both in SQL Management Studio and when running the commands directly from ADO.NET:

Migrating once:
Migrating twice:

Unhandled Exception: System.Data.SqlClient.SqlException: Invalid column name 'Name'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Program.ApplyMigrations(DbConnection connection) in C:\Stuff\TwoOneCore\TwoOneCore\Program.cs:line 210
   at Program.Main() in C:\Stuff\TwoOneCore\TwoOneCore\Program.cs:line 167

ADO.NET repro code:

public class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
}

public class Program
{
    private static readonly string[] migrations =
    {
        @"IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
    CREATE TABLE [Blog] (
        [Id] int NOT NULL IDENTITY,
        CONSTRAINT [PK_Blog] PRIMARY KEY ([Id])
    );
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
    CREATE TABLE [Post] (
        [Id] int NOT NULL IDENTITY,
        [BlogId] int NULL,
        CONSTRAINT [PK_Post] PRIMARY KEY ([Id]),
        CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([Id]) ON DELETE NO ACTION
    );
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
    CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192355_Initial')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20180810192355_Initial', N'2.1.1-rtm-30846');
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192452_AddName')
BEGIN
    ALTER TABLE [Blog] ADD [Name] nvarchar(max) NULL;
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192452_AddName')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20180810192452_AddName', N'2.1.1-rtm-30846');
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192552_AddIndex')
BEGIN
    DECLARE @var0 sysname;
    SELECT @var0 = [d].[name]
    FROM [sys].[default_constraints] [d]
    INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
    WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Blog]') AND [c].[name] = N'Name');
    IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Blog] DROP CONSTRAINT [' + @var0 + '];');
    ALTER TABLE [Blog] ALTER COLUMN [Name] nvarchar(450) NULL;
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192552_AddIndex')
BEGIN
    CREATE UNIQUE INDEX [IX_Blog_Name] ON [Blog] ([Name]) WHERE [Name] IS NOT NULL;
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192552_AddIndex')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20180810192552_AddIndex', N'2.1.1-rtm-30846');
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192708_RemoveName')
BEGIN
    DROP INDEX [IX_Blog_Name] ON [Blog];
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192708_RemoveName')
BEGIN
    DECLARE @var1 sysname;
    SELECT @var1 = [d].[name]
    FROM [sys].[default_constraints] [d]
    INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
    WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Blog]') AND [c].[name] = N'Name');
    IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Blog] DROP CONSTRAINT [' + @var1 + '];');
    ALTER TABLE [Blog] DROP COLUMN [Name];
END;
",
        @"IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20180810192708_RemoveName')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20180810192708_RemoveName', N'2.1.1-rtm-30846');
END;
"
    };

    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var connection = context.Database.GetDbConnection();

            Console.WriteLine("Migrating once:");
            ApplyMigrations(connection);
            
            Console.WriteLine("Migrating twice:");
            ApplyMigrations(connection);
        }
   }

    private static void ApplyMigrations(DbConnection connection)
    {
        connection.Open();
        foreach (var migration in migrations)
        {
            var command = connection.CreateCommand();
            command.CommandText = migration;
            command.ExecuteNonQuery();
        }

        connection.Close();
    }
}

ajcvickers · 2018-08-28T18:51:32Z

Triage: for 3.0, we will investigate the minimal set of places where we have to wrap in an exec call to avoid the SQL Server parsing issue. Worst case, we will have to do it everywhere. Note that this should only be done when generating idempotent scripts.

tonven · 2018-09-21T18:19:34Z

@christianholsen can you paste a gist with a powershell script that you use to prepare migration file?
Thanks in advance.

christianholsen · 2018-10-04T06:26:54Z

Yep. As a wrote above the only problem I found was the specific scenario specific where I have created an index for a nullable column and later remove it. The problem arises when the CREATE UNIQUE INDEX statement is evaluated even though it is not executed. So I have chosen to wrap all CREATE UNIQUE INDEX statements in the SQL in EXEC blocks. This has the effect that the script is dynamic and will only be evaluated when it actually is executed - fixing the immediate problem.

I use Team City as my build tools. You should be able to use this approach with any build tool.

I have a build step where I create the idempotent SQL file as mentioned in my first post. This step is executed as a command line step using the dotnet cli : dotnet ef migration script...etc (see my first post) This creates the idempotent script 'migrationscript.sql'

Next I have a powershell script step where I replace CREATE UNIQUE INDEX using regular expressions.
The code is here:

<#

Replace all 
CREATE UNIQUE INDEX [...]
with 
EXEC('CREATE UNIQUE INDEX [...]')
in migrationscript.sql

#>
$regexA = '\s*(CREATE UNIQUE INDEX.+)'
$encoding = New-Object System.Text.UTF8Encoding
$invocation = (Get-Variable MyInvocation).Value
Get-ChildItem  "migrationscript.sql" | % {
  $c = (Get-Content $_.FullName) -replace $regexA,'EXEC(''$0'')' -join "`r`n"
  [IO.File]::WriteAllText("$((Get-Item -Path ".\").FullName)\\migrationscript.sql", $c, $encoding)
}
Write-Host ("migrationscript.sql has been fixed")

As I understand it this problem will be fixed at some point in EF CORE, so this is working fine for me for now. :-)

Hope this helps,
/Christian

tonven · 2018-10-04T08:46:26Z

@christianholsen Thanks for response. I found different solution. I am producing all migrations separately and in Octopus Deploy run only those migrations, that were not applied. So I am waiting as well when this will be fixed, so I can switch back to migrations.sql. :)

Kukkimonsuta · 2018-10-10T08:16:30Z

We are experiencing the same issue with hand written updates after column was removed/renamed: migrationBuilder.Sql(@"UPDATE [Foo] SET [Bar] = 15 WHERE [Bar] = 10"); - it would be great if this was also wrapped when generating a script.

mitchelsellers · 2018-11-06T16:28:34Z

We are also experiencing this issue. The workaround that @christianholsen used seems to have gotten us past this, as we are using Azure DevOps for deployments we were able to patch the script before we release.

acarrau · 2018-11-09T13:39:48Z

The same is occurring to us with Idempotent Script Generation. In our case it is not happening with an INDEX creation, but with an IDENTITY INSERT.

Shtong · 2019-02-27T13:11:21Z

Another project where this problem occurs (without involving indexes) is the current version of the Contoso University sample app (available here https://github.com/aspnet/Docs/tree/0ee6b101d9d4b4022add3583ed25f0d89674b87b/aspnetcore/data/ef-mvc/intro/samples/cu-final). The Inheritance migration creates a temporary column (OldID) that is created and immediately removed in the same migration ; and after the first run, the database rejects the itempotent migration script because the column does not exist.

Unfortunately in this case, wrapping the instructions in an EXEC call with a simple regex replace won't be enough, as we would need to escape the quotes inside the EXEC argument, like in that case:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20170816195930_Inheritance')
BEGIN
    UPDATE dbo.Enrollment SET StudentId = (SELECT ID FROM dbo.Person WHERE OldId = Enrollment.StudentId AND Discriminator = 'Student')
END;

adria3a3 · 2019-05-13T08:22:18Z

I'm having the same problem with the migrations. When the idempotent flag is set not all statements are wrapped with a EXEC statement and the migration fails.

It there an ETA know for this issue to be resolved?

bricelam · 2019-05-13T19:05:47Z

We still hope to address this issue before the final 3.0.0 release (this September).

jacqueskang · 2019-05-17T13:57:06Z

We are having this issue too and it would be nice to have it fixed for 2.x too

mitchelsellers · 2019-05-17T14:02:42Z

For those using Azure DevOps, we did release a set of .NET Core Pipeline tasks. A temporary workaround for this issue is included as an option in our tasks that script the migrations (https://marketplace.visualstudio.com/items?itemName=iowacomputergurus.dotnetcore-pipeline-tasks)

Just a workaround until this can be truly fixed

ghost · 2019-06-05T09:53:14Z

Hi,

Another workaround is to globally wrap SQL scripts with EXECUTE() statement. In case of someone looking for such an approach here is very dumb (roughly tested) implementation:

    public class DynamicSqlRelationalCommandBuilder : RelationalCommandBuilder
    {
        public DynamicSqlRelationalCommandBuilder(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger, IRelationalTypeMappingSource typeMappingSource) : base(logger, typeMappingSource)
        {
        }

        protected override IRelationalCommand BuildCore(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger, string commandText, IReadOnlyList<IRelationalParameter> parameters)
        {
            commandText = "EXECUTE ('" + commandText.Replace("'", "''") + "')";
            return base.BuildCore(logger, commandText, parameters);
        }
    }

    public class DynamicSqlRelationalCommandBuilderFactory : RelationalCommandBuilderFactory
    {
        public DynamicSqlRelationalCommandBuilderFactory(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger, IRelationalTypeMappingSource typeMappingSource) : base(logger, typeMappingSource)
        {
        }

        protected override IRelationalCommandBuilder CreateCore(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger,
            IRelationalTypeMappingSource relationalTypeMappingSource)
        {
            return new DynamicSqlRelationalCommandBuilder(logger, relationalTypeMappingSource);
        }
    }

Then somewhere in your IDesignTimeDbContextFactory implementation:
options.ReplaceService<IRelationalCommandBuilderFactory, DynamicSqlRelationalCommandBuilderFactory>();

oising · 2019-06-25T17:15:25Z

I came here looking for issues around creating VIEWs and TRIGGERs with an --idempotent flavoured migration script, and I get a different error, albeit should probably be thrown into the same bucket as this one. The script generated by dotnet ef migrations script ... won't parse at all because of the way it groups CREATE VIEW and CREATE TRIGGER statements.

According to the SQL laws laid down by Microsoft, CREATE VIEW needs to be the first statement in the query batch, but it isn't, because there's an IF NOT EXISTS statement there first. Here's an example from my idempotent script that was generated:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190617133214_Add_Crossing_Coordinate_View')
BEGIN
    CREATE VIEW [dbo].[crossing_coordinate_view] 
    									AS
    									SELECT Crossing.Id,

This incurs Incorrect syntax near the keyword 'VIEW'. when attempting to parse it.

More info here:

https://dba.stackexchange.com/questions/215699/if-not-exists-using-object-id-doesnt-work-for-views-and-triggers-why

This ultimately makes dotnet ef migrations script unusable for release automation, arguably it's principal purpose. Please fix (one solution is to emit a DROP VIEW guarded by the IF, and always recreate the VIEW [or trigger]).

oising · 2019-06-25T19:51:41Z

Ok, I've worked around this by using a powershell task in my release pipeline to munge the migration script in the artifact drop folder before executing it. I'm using regex to wrap all CREATE VIEW and CREATE TRIGGER blocks with EXEC('...'). Here's my inline script:

$sql = get-content .\migrate.sql -raw
[regex]::replace($sql, "BEGIN\s+(CREATE (?:VIEW|TRIGGER).+?)END", "BEGIN`nEXEC('`$1');`nEND", "ignorecase,singleline") > migrate.sql

Make sure you tick the box to use PowerShell Core (for the -raw parameter support)

koshkarov · 2019-07-24T02:22:31Z

For those who uses idempotent script to apply the migrations. This approach splits the script on separate queries by "GO" statements an checks if this query is a part of the migration that was already applied.
_Note: the other way of fixing this issue is to generate 'per migration' idempotent scripts (which EF Core CLI allows to do), but this method is very slow.
I use PowerShell to apply them.

Read migrations idempotent script file to a list of strings - $queries:

$sqlData = Get-Content $SQL_FILE_PATH -Raw
$queries = $sqlData -split "GO\r\n"

Then select migrations that were already applied to the database.

$existingMigrations = Invoke-Some-Magic-And-Return-List-Of-Migrations-As-Strings

NOTE: When you make this query, make sure to check if migration table exists first.

Then iterate trough $queries and extract migration name from it using RegEx:

foreach($query in $queries)
{
    $migrationFromQuery = $null
    if ($query -match "(?<=\')(\d{14}.+)(?=\')") {
        $migrationFromQuery =  $matches[0]
    }
    ....

And check if it is in the list of applied migrations and apply if it is not there:

    ...
    if ($existingMigrations -notcontains $migrationFromQuery) {
        Invoke-Some-Magic-And-Apply-Query-As-Transaction -QUERY $query
    }
}

Good things about this solution:

it will not modify migrations code;
It will only filter migrations, so you do not need to specifically 'fix some of them';
it will not brake your migrations deployment when the fix will come from MS;
you do not need to modify your code or build pipeline, only deployment script.

Note: it will not filter the very first SQL query:

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

But this query is completely safe and will not cause any problems like discussed in this issue.

kovyfive · 2019-09-26T08:55:42Z

Also including
:on error ignore
in the beginning of the script will ignore all errors.
This should help to run it, but will not show any error during deployment, potentially not showing possible deployment issues.

ChristopherHaws · 2020-08-06T06:20:29Z

We also wrap all of our migrationBuilder.Sql("...") calls with EXEC to prevent future failures if schema's change. :)

bricelam · 2020-08-06T18:32:23Z

@ChristopherHaws Good strategy. We should mention this in the docs. Filed dotnet/EntityFramework.Docs#2561

derigel23 · 2020-08-09T12:32:08Z

@bricelam Any plans to backport fix to 3.1.x branch?

bricelam · 2020-08-09T20:31:37Z

No, sorry. This fix required too significant of changes for a patch release—especially on an LTS release.

binaryio · 2020-09-25T07:50:36Z

I've found another workaround to this issue by using a powershell script to edit the Idempotent script prior to executing it. It fetches the list of MigrationIds and comments out the relevant script blocks.

param ($server, $database, $username, $password, $script)

#
# Create the temporary file to contain the modified script
#
$newscript = Join-Path -Path (Split-Path -Path $script) -ChildPath ("fixed_" + (Split-Path -Path $script -Leaf));
Set-Content -Path $newscript -Value "" -Encoding 'utf8'

#
# Fetch the currently applied migrations
#
$migrationIds = ""
$qry = Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -Query "SELECT DISTINCT [MigrationId] FROM [__EFMigrationsHistory]" -ErrorAction SilentlyContinue
if ($qry -ne $null)
{
    $migrationIds = ($qry | Select-Object -ExpandProperty MigrationId) -Join "|"
}

#
# Match the chunks in the script with the list of applied migrations, and comment them out
#
if ($migrationIds -ne "")
{
    $regex = "(?ms)^IF NOT EXISTS\(SELECT \* FROM \[__EFMigrationsHistory\] WHERE \[MigrationId\] = N'(" + $migrationIds + ")'\).*?END;\s+GO"
    $c = (Get-Content $script -Raw) -replace $regex,"/*`r`n`$0`r`n*/";
    Set-Content -Path $newscript -Value $c -Encoding 'utf8'
} else {
    Copy-Item $script $newscript
}

#
# Execute the fixed Migrations script
#
Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -InputFile $newscript -Verbose

DmytroHeorhin · 2020-12-18T18:30:45Z

Here are the cases I found that require EXEC for the SQL EF generates:

AddColumn with ComputedColumnSql

AddCheckConstraint

CreateIndex with Filter

DeleteData

InsertData

UpdateData

It'd also be nice to have DROP INDEX and probably DROP any object in this list. I face an error like "Cannot drop the index 'my index name', because it does not exist or you do not have permission."

Thanks for the fix already made! It'll avoid the error in case of CREATE INDEX for me.

VidSankar · 2021-02-02T14:10:21Z

Another project where this problem occurs (without involving indexes) is the current version of the Contoso University sample app (available here https://github.com/aspnet/Docs/tree/0ee6b101d9d4b4022add3583ed25f0d89674b87b/aspnetcore/data/ef-mvc/intro/samples/cu-final). The Inheritance migration creates a temporary column (OldID) that is created and immediately removed in the same migration ; and after the first run, the database rejects the itempotent migration script because the column does not exist.

Unfortunately in this case, wrapping the instructions in an EXEC call with a simple regex replace won't be enough, as we would need to escape the quotes inside the EXEC argument, like in that case:
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20170816195930_Inheritance')
BEGIN
    UPDATE dbo.Enrollment SET StudentId = (SELECT ID FROM dbo.Person WHERE OldId = Enrollment.StudentId AND Discriminator = 'Student')
END;

I am having the same issue. Was there any fix or work around for this?

Though Idempotent script, sql seems to evaluate the query, though it will not get executed. And I am getting an error "Invalid column name". Any idea?

derigel23 · 2021-03-14T07:43:10Z

@bricelam what about net5.0? Still doesn't work.

VidSankar · 2021-03-14T08:09:12Z

Upgraded to 5.0 and it worked. Kind Regards, Vid

…

________________________________ From: Slava Trenogin ***@***.***> Sent: Sunday, March 14, 2021 7:43:25 AM To: dotnet/efcore ***@***.***> Cc: Vid Sankar ***@***.***>; Comment ***@***.***> Subject: Re: [dotnet/efcore] SQL Server Migrations: Idempotent scripts fails with 'invalid column name' (needs EXEC) (#12911) @bricelam<https://github.com/bricelam> what about net5.0? Still doesn't work. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#12911 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AG2FGENBB3JCS4ABVVU3OFDTDRSJ3ANCNFSM4FOGIKTA>.

bricelam · 2021-03-19T15:42:59Z

@derigel23 Can you create a new issue with more details about the issue you're encountering?

derigel23 · 2021-05-03T12:23:54Z

@bricelam I've found out that errors are rising from my custom SQL statements in migration builder.
Should I escape them manually in EXEC or is there some better way?

bricelam · 2021-05-03T18:30:53Z

Escape them manually. Users are responsible for ensuring their custom SQL statements work with the idempotent script. (Since we generally avoid trying to parse SQL in EF)

jasonbert · 2022-05-31T14:21:45Z

I've found another workaround to this issue by using a powershell script to edit the Idempotent script prior to executing it. It fetches the list of MigrationIds and comments out the relevant script blocks.

param ($server, $database, $username, $password, $script)

#
# Create the temporary file to contain the modified script
#
$newscript = Join-Path -Path (Split-Path -Path $script) -ChildPath ("fixed_" + (Split-Path -Path $script -Leaf));
Set-Content -Path $newscript -Value "" -Encoding 'utf8'

#
# Fetch the currently applied migrations
#
$migrationIds = ""
$qry = Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -Query "SELECT DISTINCT [MigrationId] FROM [__EFMigrationsHistory]" -ErrorAction SilentlyContinue
if ($qry -ne $null)
{
    $migrationIds = ($qry | Select-Object -ExpandProperty MigrationId) -Join "|"
}

#
# Match the chunks in the script with the list of applied migrations, and comment them out
#
if ($migrationIds -ne "")
{
    $regex = "(?ms)^IF NOT EXISTS\(SELECT \* FROM \[__EFMigrationsHistory\] WHERE \[MigrationId\] = N'(" + $migrationIds + ")'\).*?END;\s+GO"
    $c = (Get-Content $script -Raw) -replace $regex,"/*`r`n`$0`r`n*/";
    Set-Content -Path $newscript -Value $c -Encoding 'utf8'
} else {
    Copy-Item $script $newscript
}

#
# Execute the fixed Migrations script
#
Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -InputFile $newscript -Verbose

Super useful, great idea, we're building the idempotent script and publishing as an artifact. At the time of build we can't determine which migrations have been applied, just when we're running the release. Should resolve the problem described in this issue about the invalid column name, which we're experiencing 👍.

Worth noting that this only happens when we're running the release through Azure DevOps pipelines. It works fine If I take the script and run it through MSSMS.

ChristopherHaws · 2022-05-31T17:57:57Z

We have been using this extension method in place of migrationBuilder.Sql("...") for years now and it has worked great for us.

/// <summary>
/// Executes the SQL statement via sp_executesql which does not get validated until runtime.
/// </summary>
public static OperationBuilder<SqlOperation> ExecuteSql(this MigrationBuilder migrationBuilder, string sql) =>
    migrationBuilder.Sql($"EXEC sp_executesql N'{sql.Replace("'", "''")}'");

sgabler-solytic · 2022-09-06T12:12:12Z

@ChristopherHaws thanks for that, it solved our problem 👍

We had issues at first, because the generated SQL script didn't properly close the ' (single quotes) correctly. After some digging, we found out that our raw SQL scripts sometimes contained GO commands.

Example

UPDATE xyz SET foo = 'bar';
GO

UPDATE xyz SET baz = 'banana';
GO

Apparently EF Core splits the scripts based on GO commands. So the above example would create two steps in the generated SQL script, but the first one would have a missing ' (single quote).

The workaround for us was to extend your method a bit:

    /// <summary>
    /// Executes the SQL statement via sp_executesql which does not get validated until runtime. This avoids problems
    /// when generating an idempotent SQL script with the command `dotnet ef migrations script` (see
    /// https://github.com/dotnet/efcore/issues/12911#issuecomment-1142441149)
    ///
    /// NOTE: When this method is called on an SQL statement that contains `GO`, EF Core splits the statements at those
    /// and creates separate steps in the migrations. Unfortunately this will lead to a broken SQL script, as there are
    /// no closing ' (single quote) characters. So we comment out the GO statements dynamically.
    /// </summary>
    public static OperationBuilder<SqlOperation> ExecuteSqlSafely(this MigrationBuilder migrationBuilder, string sql)
    {
        const string pattern = "^\\s*GO;?\\s*$";
        const string replacement = $"-- GO (Commented out by {nameof(ExecuteSqlSafely)} method)";

        // First comment out all lines that contain only a `GO` statement (case insensitive, with optional semicolon and
        // whitespace). This requires the regex to be run in Multiline mode
        var modifiedSql = Regex.Replace(sql, pattern, replacement, RegexOptions.Multiline | RegexOptions.IgnoreCase);

        // Then escape all single quotes ('), which is necessary because of EXEC sp_executesql N''
        modifiedSql = modifiedSql.Replace("'", "''");

        return migrationBuilder.Sql($"EXEC sp_executesql N'{modifiedSql}'");
    }

alexandis · 2023-02-19T19:28:38Z

Isn't it possible to use DbCommandInterceptor to eliminate this issue? We use it for the site, but I am not sure if it's possible to use it for migrations, inside IDesignTimeDbContextFactory implementation

a-witkowski · 2023-06-13T15:41:18Z

Here are the cases I found that require EXEC for the SQL EF generates:

AddColumn with ComputedColumnSql

AddCheckConstraint

CreateIndex with Filter

DeleteData

InsertData

UpdateData

And CREATE SCHEMA for the temporal tables.

Thanks for the PR!

@bricelam, dotnet ef migrations script --idempotent version 7.0.7 still produces

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20230523085552_init')
BEGIN
/****** Object: Schema [reporting] Script Date: 2023-05-17 17:27:00 ******/
CREATE SCHEMA [reporting]
END;
GO

bricelam · 2023-06-14T15:17:09Z

@a-witkowski Can you file a new issue?

brentlyjdavid · 2023-08-17T03:08:14Z

We have been using this extension method in place of migrationBuilder.Sql("...") for years now and it has worked great for us.

/// <summary>
/// Executes the SQL statement via sp_executesql which does not get validated until runtime.
/// </summary>
public static OperationBuilder<SqlOperation> ExecuteSql(this MigrationBuilder migrationBuilder, string sql) =>
    migrationBuilder.Sql($"EXEC sp_executesql N'{sql.Replace("'", "''")}'");

I wonder if this could just be added to the framework. We are adding this to our projects and going to start exclusively using it too because of dropped columns in a similar situation. Getting frustrated it keeps randomly popping up in different circumstances.

Is there a security risk or something to not have the .Sql(...) command just automatically run it/create it like that by default? rather than have an extension method?

patrickklaeren · 2023-08-25T07:05:46Z

We have been using this extension method in place of migrationBuilder.Sql("...") for years now and it has worked great for us.
/// <summary>
/// Executes the SQL statement via sp_executesql which does not get validated until runtime.
/// </summary>
public static OperationBuilder<SqlOperation> ExecuteSql(this MigrationBuilder migrationBuilder, string sql) =>
    migrationBuilder.Sql($"EXEC sp_executesql N'{sql.Replace("'", "''")}'");
I wonder if this could just be added to the framework. We are adding this to our projects and going to start exclusively using it too because of dropped columns in a similar situation. Getting frustrated it keeps randomly popping up in different circumstances.

Is there a security risk or something to not have the .Sql(...) command just automatically run it/create it like that by default? rather than have an extension method?

The payload of that is specific to SQL Server. I don't think there's particularly high overhead to putting that into a project yourself, personally.

It's less of a security concern and more of a behavioural quirk, I'd say mainly because this is largely dependent on the engine you're targeting.

ajcvickers added the customer-reported label Aug 10, 2018

ajcvickers added this to the 3.0.0 milestone Aug 28, 2018

ajcvickers assigned bricelam Aug 28, 2018

ajcvickers added the type-bug label Aug 28, 2018

bricelam mentioned this issue Feb 6, 2019

Incorrect syntax in the script generated by command "migrations script --idempotent" #10726

Closed

bricelam added the propose-punt label Jun 20, 2019

divega modified the milestones: 3.0.0, Backlog Jun 20, 2019

divega added punted-for-3.0 and removed propose-punt labels Jun 24, 2019

bricelam mentioned this issue Sep 13, 2019

migrationBuilder.InsertData generates SQL with 'Invalid column name' in idempotent scripts #17826

Closed

bricelam closed this as completed in fde4ff1 Aug 5, 2020

bricelam closed this as completed in #21933 Aug 5, 2020

ajcvickers modified the milestones: 5.0.0, 5.0.0-rc1 Aug 14, 2020

ajcvickers modified the milestones: 5.0.0-rc1, 5.0.0 Nov 7, 2020

ajcvickers mentioned this issue Feb 2, 2021

EF Migration evaluating the script #24045

Closed

knalinne mentioned this issue Sep 15, 2021

idempotent script can't be executed once a table has been dropped oracle/dotnet-db-samples#200

Closed

ajcvickers mentioned this issue Dec 7, 2023

Add setting to make migration script creation use EXEC(string) for migrationBuilder.Sql(...) generation #32526

Closed

SQL Server Migrations: Idempotent scripts fails with 'invalid column name' (needs EXEC) #12911

SQL Server Migrations: Idempotent scripts fails with 'invalid column name' (needs EXEC) #12911

Comments

christianholsen commented Aug 7, 2018

Steps to reproduce

Proposal for a solution

Further technical details

ajcvickers commented Aug 10, 2018

ajcvickers commented Aug 28, 2018

tonven commented Sep 21, 2018

christianholsen commented Oct 4, 2018

tonven commented Oct 4, 2018

Kukkimonsuta commented Oct 10, 2018

mitchelsellers commented Nov 6, 2018

acarrau commented Nov 9, 2018 • edited Loading

Shtong commented Feb 27, 2019

adria3a3 commented May 13, 2019

bricelam commented May 13, 2019

jacqueskang commented May 17, 2019

mitchelsellers commented May 17, 2019

ghost commented Jun 5, 2019

oising commented Jun 25, 2019 • edited Loading

oising commented Jun 25, 2019

koshkarov commented Jul 24, 2019 • edited Loading

kovyfive commented Sep 26, 2019

ChristopherHaws commented Aug 6, 2020

bricelam commented Aug 6, 2020 • edited Loading

derigel23 commented Aug 9, 2020

bricelam commented Aug 9, 2020

binaryio commented Sep 25, 2020

DmytroHeorhin commented Dec 18, 2020 • edited Loading

VidSankar commented Feb 2, 2021 • edited Loading

derigel23 commented Mar 14, 2021

VidSankar commented Mar 14, 2021 via email

bricelam commented Mar 19, 2021

derigel23 commented May 3, 2021

bricelam commented May 3, 2021

jasonbert commented May 31, 2022 • edited Loading

ChristopherHaws commented May 31, 2022

sgabler-solytic commented Sep 6, 2022

alexandis commented Feb 19, 2023 • edited Loading

a-witkowski commented Jun 13, 2023

bricelam commented Jun 14, 2023

brentlyjdavid commented Aug 17, 2023 • edited Loading

patrickklaeren commented Aug 25, 2023

acarrau commented Nov 9, 2018 •

edited

Loading

oising commented Jun 25, 2019 •

edited

Loading

koshkarov commented Jul 24, 2019 •

edited

Loading

bricelam commented Aug 6, 2020 •

edited

Loading

DmytroHeorhin commented Dec 18, 2020 •

edited

Loading

VidSankar commented Feb 2, 2021 •

edited

Loading

jasonbert commented May 31, 2022 •

edited

Loading

alexandis commented Feb 19, 2023 •

edited

Loading

brentlyjdavid commented Aug 17, 2023 •

edited

Loading