Monday, January 6, 2014

Changing Entity Framework default connection factory from LocalDb to Microsoft SQL Server

When you create a project let’s say a Console Application and add Entity Framework, as you know EF will modify the existing app.config file by adding some EF relevant configurations (If you are creating a Web Application, EF will modify the web.config file.).

Now let’s say you are using Code First and adding some data. But you might be wondering where you have mentioned the connection string etc.. EF maintains these configurations in the configuration file and after EF5, it uses LocalDb as the defaultConnectionFactory. Previously it was using SQL Express.

Something extra, if you are wondering why use LocalDb, it’s because of this. Since SQL Express is the free version of Microsoft SQL Server and most developers that target SQL Server, tend to use SQL Express in their development machines. But with LocalDb they no longer have to install and manage a full instance of SQL Express, as the same T-SQL language, programming surface and client-side providers are provided with LocalDb.

So getting back to the topic, as I mentioned before, the app/web.config will be holding this defaultConnectionFactory setting for EF.
<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>
Here you can see the defaultConnectionFactory is set to LocalDbConnectionFactory. Under the parameters, you can find the LocalDb version. For example, v11.0 represents SQL Server 2012.

Now let’s say you want to change the defaultConnectionFactory to a SQL Server instance. For that you will have to change the configuration as follows.
<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Data Source=.;Integrated Security=True" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>
From now onwards, Entity Framework will use SQL Server as the defaultConnectionFactory for that particular application.

Something important to add here, with the release of EF6, it has introduced a new feature which is Code-Based Configuration. Using Code-Based Configuration you can mention the defaultConnectionFactory too. But the config file takes precedence over Code-Based configuration. In other words, if a configuration option is set in both code and in the config file, then the setting in the config file is used.

Happy Coding.

Regards,
Jaliya