Entity Framewok Code First "ADO.NET provider not found" with local SQL Server CE DLL's

Question

I am writing a C# application which uses SQL Server CE 4.0 files, which are accessed through the Entity Framework 6.0 via code-first. (The application needs to be able to use local dll's for the SQL Server CE connection i.e. the application needs to be XCOPY deployable). The application runs fine on my development machine, but on other machines (e.g. VMs with just Win7 and .NET 4.0), I get an ArgumentException:

The ADO.NET provider with invariant name 'System.Data.SqlServerCe.4.0' is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details.

The inner exception message says:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

I have searched Google and SO and most of the comments indicate ensuring the App.config file is correct. I believe mine is (default connection factory and provider sections), but here are the contents:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="System.Data.SqlServerCe.4.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
    </providers>
  </entityFramework>
</configuration>

The build folder includes the following files (in addition to the application-specific files, of course):

• EntityFramework.dll
• EntityFramework.xml
• EntityFramework.SqlServer.dll
• EntityFramework.SqlServer.xml
• EntityFramework.SqlServerCompact.dll
• EntityFramework.SqlServerCompact.xml

In each of the amd64 and x86 subfolders are the following files:

• sqlceca40.dll
• sqlcecompact40.dll
• sqlceer40EN.dll
• sqlceme40.dll
• sqlceqp40.dll
• sqlcese40.dll

I am certain the program runs on the development machine because SQL Server CE has been installed, but how do I get it to run using just local SQL Server CE dll's on other machines?

Solution 1

See http://tech.aendeavors.com/2011/06/09/bin-deploy-sqlce-4-0-and-ef-4-1/

It seems the relevant bits you might be missing are:

• Make sure System.Data.SqlServerCe is referenced and set to "Copy local" in properties.

• Add the following to app.config:

---

<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>

Solution 2

I was facing the same problem today.
After reinstalling EF several times, what solved my problem was referencing EF in my Console project along with the DAL project (the one that actually uses EF).

If you're using a reference that in turn references EF, you gotta add it to this project as well.

Solution 3

Sorry about the late response - actually, there's a really simple solution:

If you investigate the packages\EntityFramework.6.1.3\lib\net45 folder, you'll notice that there's another dll that's already in there - EntityFramework.SqlServer.

Simply add a reference to that, and everything will work.

Comments

[Tim]

I went back and checked this and added (via NuGet) EntityFramework and EntityFramework SQL Server Compact to each assembly. However, this didn't fix it. After I added to the App.Config file per nekno's comment, it worked. I think both are required, but I didn't go back and undo to be sure.

[Tim]

I went back and checked the references per Tico's comment (see my reply), which didn't work. Adding what you suggested to the App.Config file did fix it, but I think both were necessary (the linked article doesn't specify which assemblies need the references). For anyone wondering (I don't mess with the App.Config file much), this goes goes directly inside <configuration>.

[trinaldi]

@Tim, I forgot about the App.config. You're absolutely right!

[mCasamento]

I can confirm the comment above

[dmathisen]

Commenting here on my behalf, again. This same thing happened to me again because I had to remove and re-install .NET Targetting Packs and it screwed up all my Visual Studio projects. Again, the only thing that worked was to uninstall SQL Server Compact and re-install it.