EF Code First with SQL Server Express 2012 ConnectionString

Question

Everything works just fine with the following connection string.

<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

I recently installed SQL Server 2012 Express on my local machine to test with, but I cannot make the connection. Here is my connection string using Windows Authentication.

<add name="ApplicationServices" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

I'm a total noob who tried his best to search the forums, but am unable to defer my solution from the 'Questions with similar titles' section. Any help is greatly appreciated.

Answer 1

EntityFramework code first uses a defaultConnectionFactory in the app.config or the web.config file which will automatically connect to .\SQLEXPRESS. This configuration looks like this:

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
  </entityFramework>

If you're using EF Code First, you probably have a class derived from DbContext to use as context. By design, EntityFramework will look for a connection string having the same name as your context class and use that one instead of the defaultConnectionFactory. This is how I'm using it:

<connectionStrings>
    <add name="ObjectContext" 
         connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=My.Db.Name; Integrated Security=True; MultipleActiveResultSets=True"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>

What I'm suggesting is to check that EF doesn't use it's defaultConnectionFactory and even force overriding that by adding the connection string named after your context. You should also check this blog article which gives great details about the EF configuration file.