Efcore: Update-Database fails to login on a LocalDB

[nicolaspierre1990]

Hey guys,

I'm trying to build an app that is based on Code First database Model. So after I added the migrations I tried to update the database with the Update-Database command in the Package Manager of Visual Studio 2017, because the dotnet ef command seems not to work with me from a console. (which I don't know why aswell) But I'm always getting the error Login failed for user ''. Now I searched the internet but I didn't get any wiser out of it.

Exception message: Login failed for user ''
Stack trace: `PM> Update-Database
System.Data.SqlClient.SqlException: Login failed for user ''.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass11_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, TState state)
   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 contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:f2cb84ec-4720-41b2-97d2-40b1c7445385
Error Number:18456,State:1,Class:14`

Steps to reproduce

• Open Package Manager Console
• Execute Update-Database Command
• Error

Link to repo (Domain classes are under src\Flexure.Domain)
https://github.com/flexure-invoicing/flexure-api

c#

Further technical details

EF Core version: 1.1.1.
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 Enterprise N
IDE:

[smitpatel]

I tried reproducing this.
Changes required to run EF commands,

1. Delete global.json from root directory. It is picking up older version of dotnet cli which does not work with new csproj.
2. In your startup you don't need line services.AddEntityFrameworkSqlServer().AddDbContext<FlexureContext>();. AddDbContext will do all work for you.

PMC commands:
If you are not able to get ef commands in PMC then probably the init script did not run (nuget bug). Restarting VS is one solution. If that does not work then uninstall & reinstall package that will run init script.
Invoke Update-Database from Flexure.Domain project with passing Flexure as startup project, migrations applied successfully.

dotnet ef command line tools:
To use dotnet ef on command line the package you need is Microsoft.EntityFrameworkCore.Tools.Dotnet It is dotnet CLI tool so you need to manually edit csproj file to install it. Adding following code in Flexure.Domain.csproj will give you dotnet ef

<ItemGroup>
   <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.Dotnet" Version="1.0.0" />
</ItemGroup>

This gives you dotnet ef in Flexure.Domain project directory. Invoke `dotnet ef database update" from that directory with passing startup project Flexure updates the database.

At first I got error that log in failed. Though it was because the SQL instance being used in connection string was not available in my machine & I had to change the instance name.

Check your connection string if it is correct. Try ADO.NET provider or visual studio to check if you can actually connect to database.

[nicolaspierre1990]

Trying your solution gives me this.

EDIT:
Verbose output

E:\Nicolas\flexure\flexure-api\srcFlexure.Domain (master)
λ dotnet ef database update --verbose
Using project 'E:\Nicolas\flexure\flexure-api\srcFlexure.DomainFlexure.Domain.csproj'.
Using startup project 'E:\Nicolas\flexure\flexure-api\srcFlexure.DomainFlexure.Domain.csproj'.
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\Nicolas\AppData\Local\Temp\tmpA1B9.tmp /verbosity:quiet /nologo E:\Nicolas\flexure\flexure-api\srcFlexure.DomainFlexure.Domain.csproj
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\Nicolas\AppData\Local\Temp\tmpA38E.tmp /verbosity:quiet /nologo E:\Nicolas\flexure\flexure-api\srcFlexure.DomainFlexure.Domain.csproj
dotnet build E:\Nicolas\flexure\flexure-api\srcFlexure.DomainFlexure.Domain.csproj /verbosity:quiet /nologo

Build succeeded.
0 Warning(s)
0 Error(s)

Time Elapsed 00:00:02.14
dotnet exec --depsfile E:\Nicolas\flexure\flexure-api\srcFlexure.Domainbin\Debug\netcoreapp1.1Flexure.Domain.deps.json --additionalprobingpath C:\Users\Nicolas.nugetpackages C:\Users\Nicolas.nugetpackages\microsoft.entityframeworkcore.tools.dotnet\1.0.0\tools\netcoreapp1.0\ef.dll database update --assembly E:\Nicolas\flexure\flexure-api\srcFlexure.Domainbin\Debug\netcoreapp1.1Flexure.Domain.dll --startup-assembly E:\Nicolas\flexure\flexure-api\srcFlexure.Domainbin\Debug\netcoreapp1.1Flexure.Domain.dll --project-dir E:\Nicolas\flexure\flexure-api\srcFlexure.Domain\ --content-root E:\Nicolas\flexure\flexure-api\srcFlexure.Domain\ --data-dir E:\Nicolas\flexure\flexure-api\srcFlexure.Domainbin\Debug\netcoreapp1.1\ --verbose --root-namespace Flexure.Domain
Using assembly 'Flexure.Domain'.
Using startup assembly 'Flexure.Domain'.
Using application base 'E:\Nicolas\flexure\flexure-api\srcFlexure.Domainbin\Debug\netcoreapp1.1'.
Using content root 'E:\Nicolas\flexure\flexure-api\srcFlexure.Domain\'.
Using root namespace 'Flexure.Domain'.
Using project directory 'E:\Nicolas\flexure\flexure-api\srcFlexure.Domain\'.
Using data directory 'E:\Nicolas\flexure\flexure-api\srcFlexure.Domainbin\Debug\netcoreapp1.1\'.
Finding DbContext classes...
Using context 'FlexureContext'.
System.InvalidOperationException: No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider. If AddDbContext is used, then also ensure that your DbContext type accepts a DbContextOptions object in its constructor and passes it to the base constructor for DbContext.
at Microsoft.EntityFrameworkCore.Internal.DatabaseProviderSelector.SelectServices()
at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.<>c__DisplayClass16_0.b__0(ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredServiceT
at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.Build(DbContext context)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider. If AddDbContext is used, then also ensure that your DbContext type accepts a DbContextOptions object in its constructor and passes it to the base constructor for DbContext.

[smitpatel]

dotnet ef database update --verbose this is where the error is. You are configuring UseSqlServer in Startup.cs in Flexure project. You need to pass it as startup project while running dotnet ef so that EF can initialize services.
Command to be used
dotnet ef database update --startup-project Flexure

[nicolaspierre1990]

Still the same problem.. I am doing it in the right folder right ?

EDIT: constructor was commented out in Context file.. But now i'm back at the start

[nicolaspierre1990]

Something is wrong with me SQLExpress instance. You can close this. Works on (localdb)\MSSQLLocalDB now

[smitpatel]

Something is wrong with me SQLExpress instance. You can close this. Works on (localdb)\MSSQLLocalDB now

The same happened to me. Worked on (localdb)\MSSQLLocalDB, It gave me above error for SQLExpress and I don't have SQLExpress instance on my machine. There may be a different reason too. But most probably it is connection string issue rather than EF issue.