Using Entity Framework 6 with SQL Server Compact 4

Question

I'm trying to create a SQL Server Compact database from the code using code-first scenario, utilizing Entity Framework. Everything worked until I realized I have installed SQL Server 2014 Express and it was using it (instead of local .sdf file, which is what I'm looking for). Therefore, I uninstalled SQL Server 2014 Express.

Now, in my solution, I'm using EntityFramework.SqlServerCompact nuget extension.

However, whenever I try to initialize DbContext, I get error:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

I tried setting up connection string in the app.config to:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="System.Data.SqlServerCe.4.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
    </providers>
  </entityFramework>
  <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.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="CompactDBContext"
         providerName="System.Data.SqlServerCe.4.0"
         connectionString="MyDatabase.sdf"/>
  </connectionStrings>
</configuration>

Or setting it in the code, passing it into DbContext constructor, different variations of the connection string, etc...

This is the DbContext declaration:

 public class CompactDBContext : DbContext
 {
     public DbSet<MyItem> MyItems { get; set; }
 }

And this is how I'm using it:

public void InsertItem()
{
    using (var db = new CompactDBContext())
    {
        MyItem item = new MyItem();

        db.MyItems.Add(item);

        db.SaveChanges();
    }
}

I'm pretty sure this is either duplicate question or I'm missing something really trivial, but I spent hours figuring this out without any luck.

Can someone please point to right direction?

Update 1:

By using DbContext constructor with SqlCeConnection as parameter:

public CompactDBContext() : 
  base(new SqlCeConnection("Data Source=MyDatabase.sdf;Persist Security Info=False;"),
  contextOwnsConnection: true) { }

I'm getting this error:

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code

Additional information: No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlServerCe.4.0'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

Answer 1

After fiddling with the solution and project, I found out the reason. In my setup, my application is library, and I was testing it from UnitTest project. For some reason I by mistake unloaded app.config from the UnitTest project, therefore it was complaing about missing EntityFramework providers. Loading app.confing back into project fixed the problem.