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

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>();

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("'", "''")}'");

Has there been any progress on this? This is causing the idempotent script to fail on simple column rename operations.

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.

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.

If it helps anyone, this seems to cover most/all the cases described above for anyone who is using the command builder hack from earlier in this issue:

class DynamicSqlRelationalCommandBuilder : RelationalCommandBuilder
{
    private readonly string _execRequiringStatements = @"(SET IDENTITY_INSERT|^(UPDATE|(CREATE|ALTER) PROCEDURE|ALTER TABLE|CREATE (UNIQUE )?INDEX|(CREATE|DROP) VIEW))";
    public DynamicSqlRelationalCommandBuilder(RelationalCommandBuilderDependencies dependencies)
        : base(dependencies)
    {
    }

    public override IRelationalCommand Build()
    {
        var commandText = ToString();

        if (Regex.IsMatch(commandText, _execRequiringStatements, RegexOptions.IgnoreCase))
            commandText = "EXECUTE ('" + commandText.Replace("'", "''") + "')";

        return new RelationalCommand(Dependencies, commandText, Parameters);
    }
}

Follow up to @CezaryKMakingWaves great workaround, I’ve updated the code to work with EF Core 3.0

public class DynamicSqlRelationalCommandBuilder : RelationalCommandBuilder
    {
        public DynamicSqlRelationalCommandBuilder(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
        {
        }

        public override IRelationalCommand Build()
        {
            var newCommandText = "EXECUTE ('" + base.ToString().Replace("'", "''") + "')";

            return new RelationalCommand(base.Dependencies, newCommandText, base.Parameters);
        }
    }

    public class DynamicSqlRelationalCommandBuilderFactory : RelationalCommandBuilderFactory
    {
        public DynamicSqlRelationalCommandBuilderFactory(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
        {
        }

        public override IRelationalCommandBuilder Create()
        {
            return new DynamicSqlRelationalCommandBuilder(base.Dependencies);
        }
    }

Along with the design time options replacement

builder.ReplaceService<IRelationalCommandBuilderFactory, DynamicSqlRelationalCommandBuilderFactory>();

The workaround proposed by @Inzanit no longer works with EF Core 3.x because the interface has been changed. I rewrote the workaround to work with EF Core 3.x.

public class DynamicSqlRelationalCommandBuilderFactory : RelationalCommandBuilderFactory
{
    public DynamicSqlRelationalCommandBuilderFactory(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
    {
    }

    public override IRelationalCommandBuilder Create()
    {
        return new DynamicSqlRelationalCommandBuilder(Dependencies);
    }
}

public class DynamicSqlRelationalCommandBuilder : RelationalCommandBuilder
{
    public DynamicSqlRelationalCommandBuilder(RelationalCommandBuilderDependencies dependencies) : base(dependencies)
    {
    }
    
    public override IRelationalCommand Build()
    {
        string commandText = ToString();
        commandText = "EXECUTE ('" + commandText.Replace("'", "''") + "')";

        return new RelationalCommand(Dependencies, commandText, Parameters);
    }
}

Along with the design time options replacement

builder.ReplaceService<IRelationalCommandBuilderFactory, DynamicSqlRelationalCommandBuilderFactory>();

Please be aware that this might not work with Stored Procedures as mentioned above.

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)

@gabrielionita If you implement IDesignTimeDbContextFactory<ApplicationContext> then the tooling will use that configuration when generating migrations instead of the configuration in Startup.cs.

https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dbcontext-creation#from-a-design-time-factory

Is there any indication from the team when we might expect to see a fix for this?

This issue is currently in the 5.0.0 milestone. 5.0 is the next non-patch release of EF Core scheduled for November 2020. I’ll do my best to get a fix in by then. Until then, keep post-processing the SQL file or use a non-idempotent script (e.g. dotnet ef migrations script %CURRENT_PRODUCTION_MIGRATION%)

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)

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

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.

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.

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

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

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!

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>();

This blew up all of my entity framework calls. I am now getting syntactical errors in the ef generated sql scripts that get built from my ef queries. Here is an example error Must declare the scalar @__id_Value_0

It fixed my pipeline issues but it causes other problems on my apps utilizing this context. I was able to verify the issue was the custom code by commenting it out locally and verifying the issue was gone

EDIT

It’s an ugly hack but I ended up throwing a simple conditional in the code that will only wrap the commands in “EXEC” if the command contains “CREATE PROCEDURE” or “ALTER PROCEDURE”. These commands were the ones giving me problems

  protected override IRelationalCommand BuildCore(IDiagnosticsLogger<DbLoggerCategory.Database.Command> logger, string commandText, IReadOnlyList<IRelationalParameter> parameters)
        {
            if (commandText.Contains("CREATE PROCEDURE") || commandText.Contains("ALTER PROCEDURE"))
                commandText = "EXECUTE ('" + commandText.Replace("'", "''") + "')";

            var d = base.BuildCore(logger, commandText, parameters);
            return d;
        }

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]).

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