Can't initialize local SQL Server database with EFCore commandline tools

-1

I've been trying to follow several different tutorials with EFCore and .net core and I've been totally blocked at the point where I try and create a local database.

I've used both the powershell tools and the commandline tools to try and create an initial migration (or do anything, really).

I consistently get the error:

System.InvalidOperationException: 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.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

The database does not currently exist on the system, though local SQL Server appears to be up and running.

Here is the c# code for adding the context:

services.AddDbContextPool<TestDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("TestDb")
    )
);

This is the connection string code: "TestDb": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=TestDb"

I get similar errors whether I run add-migration, dotnet ef migration add, or dotnet ef dbcontext info. (note: with the dotnet calls I am using the -s ..\{webproject}\{webproject}.csproj property

I've also messed with the connection string by adding various combinations of Trusted_Connection=True; MultipleActiveResultSets=True;, and Integrated Security=true.

I've gone into SSMS and ensured the Server authentication is SQL Server and Windows Authentication Mode and that Maximum Connections is set to 0 (unlimited). I've also gone to logins and tried adding the user to pretty much all the server roles.

So, yeah, I'm pretty confused. I've worked with EF for years, though this is my first experience with EFCore and I'm definitely more of a developer than a SQL Admin. This is also my first time trying to use the local db on this particular computer.

Edit: Looking at error.log in AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\mssqllocaldb I see this error:

2020-01-28 10:15:03.50 Logon       Error: 18456, Severity: 14, State: 38.
2020-01-28 10:15:03.50 Logon       Login failed for user 'LAPTOP-NC6HQ4TB\ripli'. Reason: Failed to open the explicitly specified database 'TestDb'. [CLIENT: <named pipe>]

Which is confusing. Of course I can't open the specified database. The entire point is I want to create a DB that doesn't yet exist.

c#
sql-server
entity-framework
.net-core
entity-framework-core
asked on Stack Overflow Jan 28, 2020 by Riplikash • edited Jan 28, 2020 by Riplikash

1 Answer

0

Found the answer. Sorry to everyone who tried to help, as you wouldn't have had enough information to solve it.

In the DbContext I had tried to add some code to the constructor to try and populate some data to the database as part of a test. This caused several problems. If the Database hadn't yet been created it tried to connect to the DB before it had been created, which caused the problems I described.

Furthermore, if I had created the db manually it would try to access the DbSets (which had not yet been created), and then complain that the set name was invalid (which, at this point it was.

This all might have been fine if the DB had been created in advance, but since I was using the DbContext to construct the database, it understandably caused problems.

And all of this headache would have been avoided had I not violated SRP and not tried to (even temporarily) hijack a context constructor to hack in some test data.

The takeaway here? Don't pollute your constructors with unrelated hacks. Bleh.

answered on Stack Overflow Jan 28, 2020 by Riplikash

User contributions licensed under CC BY-SA 3.0