The Entity Framework DbContext class uses a convention over configuration approach to development. When everything is working correctly, you can generate and populate a database just by writing a little bit of code and running “enable-migrations” and “update-database” from the Package Manager Console window in Visual Studio. No XML mapping or configuration files are required, see EF Code-Based Migrations Walkthrough for more details.
When things do not work, the conventions are frustrating because they form an impenetrable fog of mystery. Of course, having everything explicitly configured isn’t always clearer or easier to troubleshoot, but here are some common problems I’ve observed with EF 4.1 – EF 5.0, and some steps you can take to avoid the problems.
A couple popular errors you might run across include:
“System.Data.ProviderIncompatibleException: An error occurred while getting provider information from the database”
And the timeless message:
“System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server.
These messages can happen at any stage, from enable-migrations, to update-database, to running the application. The key to getting past one of these messages is figuring out which database the framework is trying to reach, and then making that database available (or pointing the framework somewhere else).
The first step I would recommend is trying to determine what connection string the framework is using, because the exception doesn’t tell you the connection string, and the connection string can be controlled by a variety of conventions, configurations, and code.
To find out the connection string, I’d add some logging to a default constructor in my DbContext derived class.
public class DepartmentDb : DbContext
{
public DepartmentDb()
{
Debug.Write(Database.Connection.ConnectionString);
}
public DbSet<Person> People { get; set; }
}
Run the application with the debugger and watch the Visual Studio Output window. Or, set a breakpoint and observe the ConnectionString property as you go somewhere in the application that tries to make a database connection.
Chances are you’ll see something like the following:
Data Source=.\SQLEXPRESS;Initial Catalog=SomeNamespace.DepartmentDb;Integrated Security=True;
When there is no other configuration or code in place, the Entity Framework will try to connect to the local SQL Server Express database (.\SQLEXPRESS). Visual Studio 2010 will install SQL Server Express by default, so if you are not establishing a connection you might have customized the installation, shut down SQL Server Express, or did one of a thousand other things you might possibly do to make the database unavailable (like change the network protocols). One way to see what SQL services are available on your machine is to go to the Package Manager Console in Visual Studio and execute the following command (also showing the output below):
PM> Get-Service | Where-Object {$_.Name -like '*SQL*'}Status Name DisplayName
------ ---- -----------
Stopped MSSQLFDLauncher SQL Full-text Filter Daemon Launche...
Stopped MSSQLSERVER SQL Server (MSSQLSERVER)
Stopped SQLBrowser SQL Server Browser
Stopped SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
Running SQLWriter SQL Server VSS Writer
In the above output you can see I do not have a default SQLEXPRESS instance available (it would list itself as MSSQL$SQLEXPRESS), but I do have a default SQL Server instance installed (MSSQLSERVER – but it is not running). I’d have to start the service and give the Entity Framework an explicit connection string for this scenario to work (see Controlling Connections below).
Visual Studio 2012 installs SQL Server LocalDb by default. LocalDb is the new SQL Express with some notable differences from SQL Express 2008. A LocalDb instance runs as a user process, requires a different connection string, and stores the system databases under your hidden AppData directory.
Since LocalDb is a user process and not a service, you won’t see it listed in the output of the Get-Service command above. You can, however, run SqlLocalDb.exe from the package manager console (or the command line) to see if LocalDb is installed.
PM> SqlLocalDb info v11.0
If the executable isn’t found, chances are you do not have LocalDb installed. In the above output, I can see I have LocalDb v11.0 installed. EF 5 will use LocalDb if it doesn’t detect SQL Express running, so when you look at the Database.Connection.ConnectionString property in the constructor, like we did earlier, you might see the following instead:
Data Source=(localdb)\v11.0;Initial Catalog=SomeNamespace.DepartmentDb;Integrated Security=True;
(localdb)\v11.0 is the LocalDb connection string, and if you are not connecting to LocalDb then you might need to reinstall (here is a link that will eventually take you to the MSI file).
How does the framework know to use LocalDb instead of Express? It’s done through configuration. If you open your application’s config file, you should see the following inside:
<entityFramework>
<defaultConnectionFactory
type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory,
EntityFramework">
<parameters>
<parameter value="v11.0" />
</parameters>
</defaultConnectionFactory>
</entityFramework>
Using the LocalDbConnectionFactory means you’ll no longer try to connect to SQL Express by default. There is also a connection factory for SQL Compact. You’ll find this factory in your config file if you install the EF SQL Compact NuGet package.
Another common problem I’ve seen popping up when using Code First migrations is typically realized with one of the following exceptions:
System.Data.SqlClient.SqlException: Login failed for user ‘[Your account here]'.
... and ...
System.Data.SqlClient.SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
These permission issues can be hard to fix. The first thing I’d do is add some debugging code to verify the connection string being used by the Entity Framework (the debugging code demonstrated earlier). Once you know the server the Entity Framework is trying to reach you should try to login to the server with a management tool (like SQL Management Studio, which also works with LocalDb), if you can. The problem is you might not be able to login with your account.
Even if you are an administrator on your local machine you might find yourself with limited privileges in your own, local SQL Server. One scenario where this can happen is when SQL Server or SQL Server Express is installed onto your machine by a different user, or you installed the software using a different account, or perhaps your machine was built from a cloned image file. As of SQL 2008, just being an administrator in Windows doesn’t make you a sysadmin in SQL Server.
To fix the permission issues you can try to login to the server using the sa account, but of course you must know the password and the sa account must be enabled. You can also try to login to your machine using the Windows account used for the software installation. Once logged in with high privileges you’ll need to add your Windows login to the list of SQL Server logins and (ideally) put yourself in the sysadmin server role.
When all else fails, you can try to regain control of SQL Server by starting the server in single user mode (see Connect to SQL Server When System Administrators Are Locked Out). The SQL Express blog also published a script to automate this process (see How to take ownership of your local SQL Server), which should also work with SQL 2012. The nuclear option, if you don’t care about any of the local databases, is to uninstall and reinstall SQL Server.
If you are using SQL LocalDb, don’t ever delete the physical .mdf and .log files for your database without going through the SQL Server Object Explorer in Visual Studio or in SQL Server Management Studio. If you delete the files only, you end up with an error like the following in a web application:
Cannot attach the file ‘…\App_Data\DepartmentDb.mdf' as database 'DepartmentDb'.
Or the following error in a desktop app:
SqlException: Cannot open database "DepartmentDb" requested by the login. The login failed.
In this case the database is still registered in LocalDb, so you’ll need to go in the Object Explorer and also delete the database here before SQL Server will recreate the files.
Explicitly controlling the connection string for the Entity Framework is easy. You can pass a connection string to the DbContext constructor, or you can pass the name of a connection string that resides in the application’s configuration file. For example, the following code will make sure the Entity Framework connects to the local SQL Server default instance and use a database named departments.
public class DepartmentDb : DbContext
{
public DepartmentDb()
: base(@"data source=.;
initial catalog=departments;
integrated security=true")
{
}
public DbSet<Person> People { get; set; }
}
While the following would tell the framework to look for a connection string named departments in the config file:
public class DepartmentDb : DbContext
{
public DepartmentDb() : base("departments")
{
}
public DbSet<Person> People { get; set; }
}
If the Entity Framework does not find a connection string named “departments” in you config file, it will assume you want a database named departments on the local SQL Express or LocalDb instances (depending on which connection factory is in place).
Finally, if you do not pass any hints to the DbContext constructor, it will also look for a connection string with the same name as your class (DepartmentDb) in the config file.
As you can see, there are lots of options the Entity Framework tries when establishing a connection. The goal of all this work is to make it easy for you to use databases, but when software is misconfigured or not installed correctly, all these options can also make troubleshooting a bit difficult.
When troubleshooting connection problems with the Entity Framework the first step is to always figure out what server and database the framework is trying to use. If the problems are permission related, the next step is to find a way to make yourself a sysadmin on the server (or at least get yourself in the dbcreator role). If the problem is making a connection to a server instance that doesn’t exist, you can always explicitly configure a connection string for an existing instance of SQL Server.
If all else fails, use SQL Server Compact, as nearly anything you can do with Code-First Entity Framework will work the compact edition of SQL Server.
OdeToCode by K. Scott Allen
Subscribe
Twitter
Search
About
Learn C#