36

I got the following error when I used sqlce 4.0 with entityframework 6.0

No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlServerCe.4.0'

My app.config looks like this

....
<configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" requirePermission="false" />
    <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 -->
  <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --></configSections>
 <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, 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" />
    </providers-->
  </entityFramework>
  <connectionStrings>
    <add name="FbMultipleInsOrderContainer" connectionString="metadata=res://*/FbMultipleInsOrder.csdl|res://*/FbMultipleInsOrder.ssdl|res://*/FbMultipleInsOrder.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;data source=|DataDirectory|\FBMultipleOrderSync.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
...

I tried re-installing EF 6. But no avail.

Any clue on this would be much appreciable.

kuhajeyan
  • 10,727
  • 10
  • 46
  • 71
  • 2
    Did you install the EntityFramework.SqlServerCompact package? – ErikEJ Nov 06 '13 at 19:58
  • 2
    You need a – ErikEJ Nov 06 '13 at 20:27
  • 1
    Now I am hit at the following The Entity Framework provider type 'System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SqlServerCe.4.0' could not be loaded – kuhajeyan Nov 07 '13 at 17:21
  • 2
    I got it resolved I had to again add those packages my consumer project as well. – kuhajeyan Nov 07 '13 at 17:40
  • 1. Select your solution in Visual Studio Solution Explorer 2. Go to Tools -> Library Package Manager -> Package Manager Console 3. A window will appear at the bottom of Visual Studio. 4. Type this command : install-package EntityFramework.SqlServerCompact 5. Pres Enter. This will install EntityFramework.SqlServerCompact Nuget package which will fix this. –  Dec 12 '14 at 08:23

12 Answers12

59

After installing the EntityFramework.SqlServerCompact nuget package, check that your app.config contains the following (as per @ErikEJ's comment above):

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

(You can leave the line for SqlClient even though you don't really need it.)

Olly
  • 5,966
  • 31
  • 60
21

ErikEJ has pointed this out, but I'd like to highlight the fact, you MUST remember to install the Nuget package

EntityFramework.SqlServerCompact

I tried following the recommended answer, but didn't realise I didn't have the required NuGet package the App.config was referencing.

David Chiew
  • 584
  • 5
  • 13
6

I met the issue in my unit tests. The tricky thing was that the error had appeared not constantly. I managed to solve it by adding the follwing class my my unit tests solution:

public static class Trick
{
    public static void FixEfProviderServicesProblem()
    {
        // this commented line should be used for SQL Server provider
        //var instance = System.Data.Entity.SqlServer.SqlProviderServices.Instance;

        // this is used for SQL Server CE provider
        var instance = System.Data.Entity.SqlServerCompact.SqlCeProviderServices.Instance;
    }
}
Yaugen Vlasau
  • 2,148
  • 1
  • 17
  • 38
5

To achieve this you just need to register the EntityFramework.SqlServerCompact in Package Manager Console,

To do this open power manager console and type below command:

PM> Install-Package EntityFramework.SqlServerCompact
msturdy
  • 10,479
  • 11
  • 41
  • 52
AKS
  • 269
  • 3
  • 2
2

To resolve this problem

Visual Studio Menu -> Tools -> NuGet Package Manager - > Manage NuGet Packages for Solution

Select 'Browse' Tab and search for following 'EntityFramework.SqlServerCompact'

Install it.

It should work.

Neville Dabreo
  • 309
  • 3
  • 5
1

My issue was the unit tests. You likely only installed the Entity framework for your project, but not your unit tests. To do so:

Right click "Solution" in Solution Explorer (not your project's name)

Click "Manage NuGet Packages"

Search for EntityFramework and press it

You will probably not see a tick next to [Project Name].Tests

KSHMR
  • 741
  • 1
  • 9
  • 24
1

This error arose on a User PC. The user ignored a previous error "The ‘DbProviderFactories’ section can only appear once per config file".

Apparently DB2 corrupted the machine config file on her PC, by adding a duplicate (and empty) <DbProviderFactories /> tag.

The solution was to delete the empty duplicate tag. The machine config file is located in [WindowsDir]\Microsoft.Net\Framework[.NET Version]\Config\Machine.config

Mr Lister
  • 45,515
  • 15
  • 108
  • 150
ActualRandy
  • 180
  • 1
  • 8
1

the solution provided is correct but it does not explain why EF need this assembly. Here is Why...

By default EF's configuration allow you to have a "defaultConnectionFactory" uder entityFramework section. This default factory is configured to "LocalDbConnectionFactory" and its param is "mssqllocaldb".

This factory internally requires "SqlServerCe.4.0" i.e. "SqlServerCompact".

The error happens only if the EF uses its default connection string in DbContext. If EF is provided with some other specified Connection string in your config then this error does not appear. Because EF by default uses the default connection factory.

once you install "SqlServerCe 4.0" the configuration of EF is changed and "LocalDbConnectionFactory" and its param is "mssqllocaldb" are replaced with "SqlServer Ce 4.0". and EF runtime is also able to find the SqlServerCe4.0 Assembly (as it is now part of your references and physically placed in your BIN).

Following is the EF Config before and after installing Sql Server Compact

OLD Config:

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="mssqllocaldb" />
          </parameters>
        </defaultConnectionFactory>
        <providers>
          <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        </providers>
      </entityFramework>

The new configuration is as below:

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

and also it added the following section to describe new Factory

<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>
  • Since I'm not using Entity Framework but am working with an old library that probably predates Entity Framework, this is what solved my issue. Thank you! – Steve Hiner Jun 02 '18 at 01:06
0

Don't forget that Package Manager Console takes app.config/web.config from selected as Startup project!

mr_squall
  • 2,215
  • 22
  • 19
0

For me, because the programmer is not only one, some programmer is already install EntityFramework.SqlServerCompact, but when I pull in my PC and RUN shows above error message, then I just Uninstall and install again.

Haryono
  • 2,184
  • 1
  • 21
  • 14
0

Within Visual Studio, all the .dll files that exist in _bin_deployableAssemblies folder need to be set to Build Action : none

All the .dll's for me were set to content.

This is what I set it to, and it worked straight after:

enter image description here

Dave
  • 8,163
  • 11
  • 67
  • 103
0

I add the provider code below to the Web.config,and install the EntityFramework.SqlServerCompact package both, and then solve the problem.

If lacking one, it fails.

<provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact"/>
Jun Yu
  • 375
  • 1
  • 5
  • 21