Entity Framework initial migration failed with SqlException

Question

I want to create SQL Server DB/tables using EF Model's code first approach. Here is my simple model class

public class MyApplication
{
    [Key]
    public string AppID { get; set; }
    public string AppName { get; set; }
}

and my DBContext class is shown here

public class PolicyDBContext : DbContext
{
    public PolicyDBContext(DbContextOptions options) : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder OptionaBuilder)
    {
        if (OptionaBuilder.IsConfigured)
        {
            OptionaBuilder.UseSqlServer(builder => builder.EnableRetryOnFailure());
        }
    }
    DbSet<MyApplication> AppRoles { get; set; }
}

Here is my config code in Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<PolicyDBContext>(item => item.UseSqlServer("Server=MyServer;Database=MyDB;user id=testuid;Password=testpwd;");
}

Then I was able to run the "add-migration initialmigration" command successfully

but when I run the "update-database" command, it always fail with the following error

Error Number:64,State:0,Class:20
A transient exception occurred during execution. The operation will be retried after 15024ms.
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

After executing "update-database" however, I notice that the new database always get created on my SQL Server, but with no tables in it. I think that indicates that at least my connection string is good.

As you can see from code above, I did EnableRetryOnFailure, as suggested by people in responding to issues similar to mine. But it doesn't solve my problem except that it will retry a few times (6) before finally gives up.

What am I missing here?

Answer 1

Not sure why it works this way but I now know how to fix it. It appears that I need to run update-database command twice. The first time will fail but, as I mentioned in my post, the new empty DB will be created. Now, leave the empty DB there - DO NOT DELETE IT! - and run the update-database command again. This time it will succeed and the tables will be created.