Ef6: EntityFramework.SqlServerCompact 6.3 -how to use?

Created on 24 Sep 2019  路  28Comments  路  Source: dotnet/ef6

Describe what is not working as expected.
I tried to go from EF 6.2 to EF 6.3
There is EntityFramework.SqlServerCompact 6.3.0, but no any sample how it use.
package updated - project not work.

Error CS0246 The type or namespace name 'SqlCeConnection' could not be found (are you missing a using directive or an assembly reference?)

Please give at least minimal information on what to do.

Operating system: (e.g. Windows 7)
IDE: (e.g. Visual Studio 2019)

closed-question
Source
picture parad74

Most helpful comment

"Private Deployment" is a package I created for 6.2!

picture ErikEJ on 25 Sep 2019
👍2

All 28 comments

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.

at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
at System.Data.Entity.Infrastructure.DependencyResolution.DefaultProviderFactoryResolver.GetService(Type type, Object key, Func`3 handleFailedLookup)

parad74 picture parad74 on 24 Sep 2019 
<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="Baseclass.Contrib.Nuget.Output" version="2.4.3" targetFramework="net461" />
  <package id="EntityFramework" version="6.3.0" targetFramework="net461" />
  <package id="EntityFramework.SqlServerCompact" version="6.3.0" targetFramework="net461" />
  <package id="ErikEJ.SqlCeBulkCopy" version="2.1.6.15" targetFramework="net461" />
  <package id="Microsoft.SqlServer.Compact" version="4.0.8876.1" targetFramework="net461" />
</packages>
parad74 picture parad74 on 24 Sep 2019

Why not simply stay on 6.2?

ErikEJ picture ErikEJ on 24 Sep 2019

Hey Erik. I used your dll for a long time in my projects :) thank you a lot.

Now about problem. I have big application on WPF 4.6.1 and I'm have now new project - web client for this application. I write it on Blazor as frontend. I'm really hope that I can use backend all from my project. I used Prism-MVVM and looks like I can replase VM -> Razor page. All everything is going fine. Even if I add backend like my dll. I tried it, but asp dotnet core can't resolve app.config inside dll (db.edmx can't work without app.config in WPF, tried it)

So => if I can use MSSQL CE with dotnet Core 3. I can use ~ 4000 my backend classes.

I hope that 6.3 can work without app.config. Or may be I can include my projects to dotnet core 3 solution if I can move to EF 6.3

parad74 picture parad74 on 24 Sep 2019

Could you provide a small repro project, and I will investigate your options - I assume you are running the backend on Windows? (Linux is out of the question with SQL CE)

ErikEJ picture ErikEJ on 24 Sep 2019

Have you installed the SQL CE 4.0 MSI on the server?

ErikEJ picture ErikEJ on 24 Sep 2019

Windows WPF + ClassLibrary .Net Framework 4.6.1

From Model Project 

  <Reference Include="EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.6.2.0\lib\net45\EntityFramework.dll</HintPath>
      <Private>True</Private>
    </Reference>
    <Reference Include="EntityFramework.SqlServer, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.6.2.0\lib\net45\EntityFramework.SqlServer.dll</HintPath>
      <Private>True</Private>
    </Reference>
    <Reference Include="EntityFramework.SqlServerCompact, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.SqlServerCompact.PrivateDeployment.6.2.0.1\lib\net45\EntityFramework.SqlServerCompact.dll</HintPath>
      <Private>True</Private>
    </Reference>
    <Reference Include="ErikEJ.SqlCe40, Version=2.1.6.14, Culture=neutral, PublicKeyToken=03b0b5a60fec3719, processorArchitecture=MSIL">
      <HintPath>..\packages\ErikEJ.SqlCeBulkCopy.2.1.6.15\lib\net40\ErikEJ.SqlCe40.dll</HintPath>
      <Private>True</Private>
    </Reference>

    <Reference Include="System.Data.Linq" />

    <Reference Include="System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.SqlServerCompact.PrivateDeployment.6.2.0.1\lib\net45\System.Data.SqlServerCe.dll</HintPath>
      <Private>True</Private>
    </Reference>
       <Compile Include="App_Data\AuditDB.Designer.cs">
      <AutoGen>True</AutoGen>
      <DesignTime>True</DesignTime>
      <DependentUpon>AuditDB.edmx</DependentUpon>
    </Compile>
parad74 picture parad74 on 24 Sep 2019

[assembly: EdmSchemaAttribute()]
namespace Count4U.Model.App_Data
{
#region Contexts

/// <summary>
/// No Metadata Documentation available.
/// </summary>
public partial class AuditDB : ObjectContext

...
this is from autigenerated .edmx file.

parad74 picture parad74 on 24 Sep 2019

Have you installed the SQL CE 4.0 MSI on the server?

Yes.
Start to work I with EF4, then EF5 and then EF6.2

System requirements for Application

1.Microsoft .NET Framework 4.6.1
https://www.microsoft.com/en-US/download/details.aspx?id=49982

2.MS SQL CE 4.0
http://www.microsoft.com/en-us/download/details.aspx?id=17876

3.Microsoft庐 SQL Server庐 Compact 4.0 SP1
https://www.microsoft.com/en-US/download/details.aspx?id=30709

  1. Microsoft庐 SQL Server庐 2012 SP3 Feature Pack
    https://www.microsoft.com/en-US/download/details.aspx?id=49999
    file - SQLSysClrTypes.msi
parad74 picture parad74 on 24 Sep 2019

Do you need to update your csproj with something like this?

  <PropertyGroup>
    <PostBuildEvent>
      if not exist "$(TargetDir)x86" md "$(TargetDir)x86"
      xcopy /s /y "$(NuGetPackageRoot)Microsoft.SqlServer.Compact\4.0.8876.1\NativeBinaries\x86\*.*" "$(TargetDir)x86"
      if not exist "$(TargetDir)amd64" md "$(TargetDir)amd64"
      xcopy /s /y "$(NuGetPackageRoot)Microsoft.SqlServer.Compact\4.0.8876.1\NativeBinaries\amd64\*.*" "$(TargetDir)amd64"
    </PostBuildEvent>
  </PropertyGroup>
bricelam picture bricelam on 24 Sep 2019

Do you need to update your csproj with something like this?

what problem is resolving? I can use EntityFramework.SqlServerCompact 6.3.0?

parad74 picture parad74 on 24 Sep 2019

Sorry, having a hard time following the issue. Are you using EntityFramework.SqlServerCompact version 6.3.0 and seeing an error?

Are you using the Microsoft.SqlServer.Compact package to acquire SQL Server Compact or the MSI?

bricelam picture bricelam on 24 Sep 2019

...and what is EntityFramework.SqlServerCompact.PrivateDeployment?

bricelam picture bricelam on 24 Sep 2019

I want to transfer project from EF6.2 to EF 6.3 and use MSSQL CE. I updated EntityFramework.SqlServerCompact 6.3.0 package. And my code not find SqlCeConnection and so on.

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

parad74 picture parad74 on 24 Sep 2019

Does running this inside Package Manager Console fix the errors?

Update-Package -Reinstall EntityFramework.SqlServerCompact
Update-Package -Reinstall Microsoft.SqlServer.Compact
bricelam picture bricelam on 24 Sep 2019

Can you use SQL CE with plain ADO.NET on the machine?

ErikEJ picture ErikEJ on 25 Sep 2019

"Private Deployment" is a package I created for 6.2!

ErikEJ picture ErikEJ on 25 Sep 2019
👍2

Can you use SQL CE with plain ADO.NET on the machine?
Not clear. If about "how I get data form db" - It all - ADO.NET (SqlCeConnection), Bulk, Linq Dynamic - this is the most critical.

parad74 picture parad74 on 25 Sep 2019

"Private Deployment" is a package I created for 6.2!
I know :) It's really thank you a lot. Only by your doc I done EF 5=> EF 6.2

parad74 picture parad74 on 25 Sep 2019 
Update-Package -Reinstall EntityFramework.SqlServerCompact
Update-Package -Reinstall Microsoft.SqlServer.Compact

Done. Now build without error, but work with exception
"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."

Are you using the Microsoft.SqlServer.Compact package to acquire SQL Server Compact or the MSI?
I use package Microsoft.SqlServer.Compact

parad74 picture parad74 on 25 Sep 2019

Please provide a ado.net based repro project, the error indicate that the MSI is not properly installed

ErikEJ picture ErikEJ on 25 Sep 2019 
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <sectionGroup name="applicationSettings"
      type="System.Configuration.ApplicationSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <section name="Count4U.Model.Properties.Settings"
        type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
    </sectionGroup>
  <!-- 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>
  <connectionStrings>
    <add name="Count4U.Model.Properties.Settings.AuditDBConnectionString"
      connectionString="metadata=res://*/App_Data.AuditDB.csdl|res://*/App_Data.AuditDB.ssdl|res://*/App_Data.AuditDB.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source={0}&quot;"/>
    </connectionStrings>
  <applicationSettings>
    </applicationSettings>
  <system.data>
    <DbProviderFactories>

      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite"
        type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>

        <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"/>
    <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>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/>
  </startup>
  <entityFramework>

    <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>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="System.Data.SqlServerCe.4.0"/>
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
   <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <dependentAssembly>

            <bindingRedirect oldVersion="0.0.0.0-4.0.0.1" newVersion="4.0.0.1"/>

        </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>
parad74 picture parad74 on 25 Sep 2019

Add this to your App.config

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0"></remove>
      <add name="Microsoft SQL Server Compact Data Provider" 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>
bricelam picture bricelam on 25 Sep 2019

Never mind, looks like it鈥檚 already there

bricelam picture bricelam on 25 Sep 2019

Thank you all for your support. In my particular case, problem was resolved in work around. I moved my WPF application to .Net 4.8. And after this step - looks like EF 6.3 with MSSQL CE - is working!

parad74 picture parad74 on 25 Sep 2019
🎉1

Moved from what??

ErikEJ picture ErikEJ on 25 Sep 2019

applicaton was on .net framework 4.6.1 => .net framework 4.8
I did it in Visual Studio 2019. Changed in every project step by step .net framework 4.6.1 => .net framework 4.8 ( PropertiesApplicaton form)

parad74 picture parad74 on 25 Sep 2019

I think it repaired config files

parad74 picture parad74 on 25 Sep 2019
Was this page helpful?
0 / 5 - 0 ratings

Related issues

danmiser picture danmiser  路  6Comments
steamwings picture steamwings  路  7Comments
Elufimov picture Elufimov  路  7Comments
domagojmedo picture domagojmedo  路  7Comments
PhilPJL picture PhilPJL  路  7Comments