SQL Server CE 4.0数据提供程序加载失败及版本不匹配问题求助

初始报错 #

对接开发时首先抛出如下异常：

System.Configuration.ConfigurationErrorsException: 'Failed to find or load the registered .Net Framework Data Provider.'
（中文译义：无法找到或加载已注册的.NET Framework数据提供程序）

异常堆栈跟踪如下：

at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
at System.Data.Entity.Infrastructure.DependencyResolution.DefaultProviderFactoryResolver.GetService(Type type, Object key, Func`3 handleFailedLookup)
at System.Data.Entity.Infrastructure.DependencyResolution.DefaultProviderFactoryResolver.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.CachingDependencyResolver.<>c__DisplayClass1.<GetService>b__0(Tuple`2 k)
at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
at System.Data.Entity.Infrastructure.DependencyResolution.CachingDependencyResolver.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.b__0(IDbDependencyResolver r)
at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.RootDependencyResolver.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.<GetService>b__0(IDbDependencyResolver r)
at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService[T](IDbDependencyResolver resolver, Object key)
at System.Data.Entity.Core.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString)
at System.Data.Entity.Core.EntityClient.EntityConnection..ctor(String connectionString)
at System.Data.Entity.Internal.LazyInternalConnection.Initialize()
at System.Data.Entity.Internal.LazyInternalConnection.get_Connection()
at System.Data.Entity.Internal.LazyInternalContext.get_Connection()
at System.Data.Entity.Database.Create(DatabaseExistenceState existenceState)
at System.Data.Entity.Database.Create()

已完成的配置操作 #

已向项目中添加如下程序集，所有引用均已设置CopyLocal=True：

• MySQL连接器相关程序集：

MySql.Data
MySql.Data.Entity

• SQL Server Compact Edition相关程序集：

System.Data.SqlServerCe
System.Data.SqlServerCe.Entity

• 基础类库程序集：System.Data

app.config中已添加如下配置：

<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>
<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" />
         <remove invariant="MySql.Data.MySqlClient" />
         <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.10.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
</system.data>

异常在创建数据库时触发，触发异常的数据库连接字符串如下：

metadata="res://*/Application Files.Data_Models.ProjectEntitiesModel.csdl|res://*/Application Files.Data_Models.ProjectEntitiesModel.ssdl|res://*/Application Files.Data_Models.ProjectEntitiesModel.msl";provider=System.Data.SqlServerCe.4.0;provider connection string="Data Source=C:\Users\jj\Desktop\da\NewDatabase.sdf"

app.config中配置的完整连接字符串节点如下，使用上述连接字符串时即触发错误：

<connectionStrings>
    <add name="ComponentDetailsEntities" connectionString="metadata=res://StaticDb/Model1.csdl|res://StaticDb/Model1.ssdl|res://StaticDb/Model1.msl;provider=System.Data.SqlServerCe.4.0;provider connection string='data source=&quot;|DataDirectory|\ComponentDetails.sdf&quot;'" providerName="System.Data.EntityClient" />
    <add name="DefaultProjEntities1" connectionString="metadata=&quot;res://*/Application Files.Data_Models.ProjectEntitiesModel.csdl|res://*/Application Files.Data_Models.ProjectEntitiesModel.ssdl|res://*/Application Files.Data_Models.ProjectEntitiesModel.msl&quot;;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=C:\Users\jj\Desktop\TestHF38.sdf&quot;" providerName="System.Data.EntityClient" />
    <add name="DataModelContainer" connectionString="metadata=&quot;res://*/Application Files.Data_Models_Test.DataModel.csdl|res://*/Application Files.Data_Models_Test.DataModel.ssdl|res://*/Application Files.Data_Models_Test.DataModel.msl&quot;;provider=System.Data.SqlServerCe.4.0;provider connection string='Data Source=&quot;|DataDirectory|\Application Files\Data_Models_Test\DefaultProj2.sdf&quot;'" providerName="System.Data.EntityClient" />
  </connectionStrings>

问题排查更新 #

初步排查确认，初始报错原因是引用的System.Data.SqlServerCe.dll版本错误（疑似版本6，该版本不包含SqlProviderFactory实现）。手动移除错误引用、替换为版本4的对应程序集后，初始异常消失，但抛出新的异常：

System.Data.SqlServerCe.SqlCeException: 'Possible file version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality. This could be due to the presence of multiple instances of SQL Server Compact of different versions or due to wrong binaries with same name as SQL Server Compact binaries. Please install SQL Server Compact binaries of matching version.'
（中文译义：检测到ADO.NET提供程序与SQL Server Compact原生二进制文件可能存在版本不匹配，会导致功能异常。该问题可能由环境中存在多个不同版本的SQL Server Compact实例、或存在与SQL Server Compact二进制文件同名的错误文件导致，请安装版本匹配的SQL Server Compact二进制文件。）

新异常的堆栈跟踪如下：

at System.Data.SqlServerCe.NativeMethods.IsValidBinary(String filename)
at System.Data.SqlServerCe.NativeMethods.ValidateNativeBinary(String modulePath)
at System.Data.SqlServerCe.NativeMethods.LoadValidLibrary(String modulePath)
at System.Data.SqlServerCe.NativeMethods.LoadNativeBinariesFromPrivateFolder(String privateInstall)
at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
at System.Data.SqlServerCe.SqlCeConnection..ctor()
at System.Data.SqlServerCe.SqlCeProviderFactory.CreateConnection()

环境补充说明 #

运行环境为Windows 10系统，使用的SQL CE版本为4.0，已尝试公开渠道可查的所有通用解决方案，问题仍未解决。

内容的提问来源于stack exchange，提问作者mkal