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

-1

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

unreachable code detected

typo in string

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

visual-studio
entity-framework-core
resharper
entity-framework-migrations
xaf
asked on Stack Overflow Nov 30, 2020 by Kirsten Greed • edited Dec 3, 2020 by Kirsten Greed

1 Answer

2

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 on Stack Overflow Dec 3, 2020 by Ivan Stoev

User contributions licensed under CC BY-SA 3.0