Unable to Update Database for an EF Core Migration

Question

I added a very minor migration to my project. It just changes the data type of a couple of columns to decimal(5,2). The Add Migration command generates the migration class. However, the Update-Database command throws an exception.

Also, the Remove-Migration throws the same exception:

An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy.

I am using a local SQL Server 2014 database. My connection string for development purposes is very simple:

"DbConnectionString": "Server=.;Database=DBNAME;Trusted_Connection=True;MultipleActiveResultSets=true"

That is stored in a config.json file. I am using the very latest version of Visual Studio, which is VS 2017 15.8.5.

I updated the web project to .NET Core 2.1 and then in Nuget I upgraded to the latest stable 2.1.4. That caused a change in the error message which is now:

An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding EnableRetryOnFailure() to the UseSqlServer call.

I went to my startup class and I did add that EnableRetryOnFailure property even though I doubt this is the root of the problem.

services.AddDbContextPool<AppDbContext>(options => options.UseSqlServer(_configuration.GetConnectionString("DbConnectionString"), builder =>
        {
            builder.EnableRetryOnFailure(5, TimeSpan.FromSeconds(10), null);
        }));

The generated migration class:

public partial class v50 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<decimal>(
            name: "LnRrSncsm",
            table: "Jobs",
            type: "decimal(5, 2)",
            nullable: false,
            oldClrType: typeof(float));

        migrationBuilder.AlterColumn<decimal>(
            name: "LnRrQsnqcsm",
            table: "Jobs",
            type: "decimal(5, 2)",
            nullable: false,
            oldClrType: typeof(float));
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<float>(
            name: "LnRrSncsm",
            table: "Jobs",
            nullable: false,
            oldClrType: typeof(decimal),
            oldType: "decimal(5, 2)");

        migrationBuilder.AlterColumn<float>(
            name: "LnRrQsnqcsm",
            table: "Jobs",
            nullable: false,
            oldClrType: typeof(decimal),
            oldType: "decimal(5, 2)");
    }
}

Cant see something wrong with the migration file. Maybe u will find something useful on this related stack overflow question: [https://stackoverflow.com/questions/29840282/error-when-connect-database-continuously](https://stackoverflow.com/questions/29840282/error-when-connect-database-continuously) — i regular, Sep 23 '18 at 15:49
I am not using AzureSQL but, I had come across that thread before I wrote up this question and I already added the EnableRetryOnFailure in my Startup class, so I think I covered off that possibility. Also, it's not a firewall issue. There is nothing preventing the web app from communicating with the database. The issue is with this migration (or any migration as of this point). — Caffiene Monkey, Sep 24 '18 at 16:29
I switched from Trusted_Security=true in my connection string and now I am getting a different error. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified). — Caffiene Monkey, Sep 24 '18 at 16:52
But, the application runs and connects to the database just fine, so I know the connection string is right. What reasons can cause this Package Manager Console to not be able to connect with the conn string when the web app can connect?? — Caffiene Monkey, Sep 24 '18 at 16:53

score 1 · Accepted Answer · answered Sep 24 '18 at 19:01

Okay, the problem was a connection string hard coded into an AppDbContextFactory. The resolution is to instead use the configuration.

public AppDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        optionsBuilder.UseSqlServer(Configuration.GetConnectionString("DbConnectionString"), opts => opts.CommandTimeout((int)TimeSpan.FromMinutes(10).TotalSeconds));

        return new AppDbContext(optionsBuilder.Options);
    }

Unable to Update Database for an EF Core Migration

1 Answers1