Specified LocalDB instance name is invalid: Able to create migrations but not run them

Question

I created a new XAF Blazor solution and was able to run it to create the database. I then added a database migration as per this question However when I go to run the migration from within package manager I get

Microsoft.Data.SqlClient.SqlException (0x80131904): 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: SNI_PN11, error: 50 - Local Database Runtime error occurred. Specified LocalDB instance name is invalid.
)
 ---> System.ComponentModel.Win32Exception (0x89C5011B): Unknown error (0x89c5011b)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:-1983577829,State:0,Class:20
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: SNI_PN11, error: 50 - Local Database Runtime error occurred. Specified LocalDB instance name is invalid.
)

This question is not a duplicate of this question about connecting to SQL Server since the program could access the database before I created the migration.

[Update]

The code is

    using DevExpress.ExpressApp.EFCore.Updating;
    using Microsoft.EntityFrameworkCore;
    using DevExpress.Persistent.BaseImpl.EF.PermissionPolicy;
    using DevExpress.Persistent.BaseImpl.EF;
    using DevExpress.ExpressApp.Design;
    using DevExpress.ExpressApp.EFCore.DesignTime;
    using DevExpress.Persistent.Base;
    using Microsoft.EntityFrameworkCore.Design;
    namespace ExamBuddy.Module.BusinessObjects {
        public class ExamBuddyContextInitializer : DbContextTypesInfoInitializerBase {
            protected override DbContext CreateDbContext() {
                var builder = new DbContextOptionsBuilder<ExamBuddyEFCoreDbContext>()
                    .UseSqlServer(@";");
                return new ExamBuddyEFCoreDbContext(builder.Options);
            }
        }
        public class ExamBuddyContextFactory : IDesignTimeDbContextFactory<ExamBuddyEFCoreDbContext>
        {
            public ExamBuddyEFCoreDbContext CreateDbContext(string[] args)
            {
                var optionsBuilder = new DbContextOptionsBuilder<ExamBuddyEFCoreDbContext>();
                optionsBuilder.UseSqlServer(@"Integrated Security=SSPI;MultipleActiveResultSets=true;Pooling=false;Data Source=(localdb)\\mssqllocaldb;Initial Catalog=ExamBuddy;ConnectRetryCount=0;");
                return new ExamBuddyEFCoreDbContext(optionsBuilder.Options);
            }
        }
        [TypesInfoInitializer(typeof(ExamBuddyContextInitializer))]
        public class ExamBuddyEFCoreDbContext : DbContext {
            public ExamBuddyEFCoreDbContext(DbContextOptions<ExamBuddyEFCoreDbContext> options) : base(options) {
                 
            }
            public DbSet<ModuleInfo> ModulesInfo { get; set; }
            public DbSet<ModelDifference> ModelDifferences { get; set; }
            public DbSet<ModelDifferenceAspect> ModelDifferenceAspects { get; set; }
            public DbSet<PermissionPolicyRole> Roles { get; set; }
            public DbSet<PermissionPolicyRoleBase> RolesBase { get; set; }
            public DbSet<PermissionPolicyUser> Users { get; set; }
        }

[Update]

I tried commenting out the Dev Express TypesInfoInitializer attribute I tried adding in the following but it does not execute ( The error does not throw ) Whereas if I put an error in the IDesignTimeDbContextFactory method it will throw

public class MigrationsContextFactory : IDbContextFactory<ExamBuddyEFCoreDbContext>
{
    public ExamBuddyEFCoreDbContext CreateDbContext()
    {
        throw new Exception("In Migration Context");
        var optionsBuilder = new DbContextOptionsBuilder<ExamBuddyEFCoreDbContext>();
        optionsBuilder.UseSqlServer(@"Integrated Security=SSPI;MultipleActiveResultSets=true;Pooling=false;Data Source=(localdb)\\mssqllocaldb;Initial Catalog=ExamBuddy;ConnectRetryCount=0;");
        return new ExamBuddyEFCoreDbContext(optionsBuilder.Options);
    }
}

The ExamBuddyContextFactory method does execute

[Update]

When I edit the connection string to use only 1 backslash I see the following messages in Visual Studio 2019

So the migration will generate with the double backslash in the connecion string but it will not run.

score 17 · Accepted Answer · answered Dec 03 '20 at 12:30

17

The exception message is telling you the problem

Specified LocalDB instance name is invalid

The invalid part is double backslash in (localdb)\\mssqllocaldb inside the C# verbatim string literal

optionsBuilder.UseSqlServer(
    @"Data Source=(localdb)\\mssqllocaldb;Integrated Security=SSPI;MultipleActiveResultSets=true;Pooling=false;Initial Catalog=ExamBuddy;ConnectRetryCount=0;");
//  ^                       ^

The actual name is (localdb)\mssqllocaldb (single backslash), so either correct that, or remove verbatim string prefix @.

All these are just standard C# string rules, there is nothing special to be discussed. Then why

Able to create migrations but not run them?

It's because Add-Migration command does not try to connect to the database, and database connection is lazily initialized only when needed, hence the invalid connection string is not detected. The command still needs to create DbContext instance in order to access the model, and the context must be configured for a specific database provider type (because EF Core creates separate model for each database provider type), but other than that it has nothing to do with the actual database.

However Update-Database command apparently needs database connection, as well as the Remove-Migration command which needs to check whether the migration is already applied. That's why these commands fail while Add-Migration succeeds.

You can easily verify that by providing fake invalid connection string like

optionsBuilder.UseSqlServer("Blah")'

and observe the exact same behaviors as in the OP.

answered Dec 03 '20 at 12:30

Ivan Stoev

195,425
15
312
343

I updated the question to show my problems when I change the connection string to just use 1 backslash. – Kirsten Dec 03 '20 at 13:26
Where do you see advice to change something in your appsettings.json? You've shown a code for using the approach of creating context [From a design-time factory](https://learn.microsoft.com/en-us/ef/core/cli/dbcontext-creation?tabs=dotnet-core-cli) - the class `ExamBuddyContextFactory` which contains the problem mentioned in the answer. All you were supposed to do was to fix that code. Updates/other problems you have are irrelevant. Btw, make sure you have only 1 class implementing `IDbContextFactory`, otherwise you'll get different errors. – Ivan Stoev Dec 03 '20 at 13:48
The key point of using design time factory is that EFC won't try to run the code from your startup, thus should have no relations to settings and other stuff you use to configure the context at runtime. – Ivan Stoev Dec 03 '20 at 13:52
Do you know why VS complains about the single backslash? – Kirsten Dec 03 '20 at 13:57
1

I'm using VS2019 (latest version) as well. And a lot of statements like this `optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=MyDb;Trusted_Connection=True;"`. And not getting errors/warnings - after all, it is just a string. – Ivan Stoev Dec 03 '20 at 17:56
I disabled Resharper and the warning disappeared. – Kirsten Dec 04 '20 at 20:43

Specified LocalDB instance name is invalid: Able to create migrations but not run them

1 Answers1

Linked