Entity Framework connecting to SQLEXPRESS not SQL Compact

Question

I have been trying out a code first EF project with SQl Compact 4.0. When I try running the project I get an error that "CREATE DATABASE permission denied in database 'master'.".

I searched for help and in response to a few blogs tried to identify the connection string being used. To my surprise it was

context.Database.Connection.ConnectionString    "Data Source=.\\SQLEXPRESS;Initial Catalog=Ancestors.Models.AncestorsContext;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFrameworkMUE"    string

even though my web.config file includes the following sections...

<connectionStrings>
    <add name="DefaultConnection" providerName="System.Data.SqlServerCe.4.0"    connectionString="Data Source=|DataDirectory|\aspnet-Ancestors-20121012114712.sdf" /> 
</connectionStrings>

<system.data>
<DbProviderFactories>
  <remove invariant="System.Data.SqlServerCe.4.0" />
  <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>

Has anyone any idea why EF is insisting on creating a connection to SQLEXPRESS instead of SQL Compact? I can find no reference at all to SQLEXPRESS anywhere in the solution.

Answer 1

Instead of using the following to pass your connection name to the DBContext

public YourContext() : base("DefaultConnection")
{

}

Use the name directive

public YourContext() : base("name=DefaultConnection")
{

}

This will tell EF to use the parameter as a connection.

Also Adding the "clear" at the top of your connectionStrings sections in web.config may resolve inheritance issues.

Answer 2

Look for the <entityFramework> section in your web.Config, it should have your default connection factory there:

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Data Source=.\SQLEXPRESS; Integrated Security=True; MultipleActiveResultSets=True"/>
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

If you're doing a code first project, you can also alter your default constructor to use your other connection string:

public YourContext() : base("name=DefaultConnection")
{

}