We decided to migrate from SQL Server Compact to SQL Server Express (possibly LocalDB) as Compact is no longer supported and we believe Express is more feature rich and likely performs better.
I made the changes necessary (although clearly missed something :) ) and the database is recreated as an .mdf file. The problem seems to be when the database is accessed. I get the error I have pasted below. The upshot is that somehow, somewhere I seem to have a reference to a provider with invariant name System.Data.SqlServerCe.4.0 when I have no intention of doing so.
I have done research and found these two relevant posts, which are not fully answered:
- MVC app erroring on SqlServerCe.4.0 provider after converting to Sql Express and
- Entity Framework using the wrong connection provider
I have tried deleting the migrations and have modified the app.config file. I have also deleted on references to SQL Server Compact from the project references. What else should I be looking at? I'm tearing my hair out over this one.
What other information can I provide?
Thanks,
Dave
P.S. Another way to ask the question would be pointing out that https://stackoverflow.com/users/183934/erikej commented in the links above:
"Obviously you reference sqlce somewhere, either in .settings or in code based configuration DbConfiguration class... – ErikEJ Sep 13 '14 at 6:09 "
Where are all possible places that Compact could be referenced?
Exception thrown: 'System.Data.DataException' in EntityFramework.dll
Exception thrown: 'System.Data.DataException' in mscorlib.dll
The thread 0x2df0 has exited with code 0 (0x0).
05.01.2017 21:50:50: Fatal [line: 18 StartupErrorViewModel -> .ctor()]: System.Data.DataException: An exception occurred while initializing the database. See the InnerException for details.System.Data.Entity.Core.MetadataException: Schema specified is not valid. >
Errors:
(0,0) : error 0152: 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.
at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.ThrowOnNonWarningErrors()
at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader.LoadItems(IEnumerable1 xmlReaders, IEnumerable1 sourceFilePaths)
at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Loader..ctor(IEnumerable1 xmlReaders, IEnumerable1 sourceFilePaths, Boolean throwOnError, IDbDependencyResolver resolver) at System.DThe thread 0x6d84 has exited with code 0 (0x0). ata.Entity.Core.Metadata.Edm.StoreItemCollection.Init(IEnumerable1 xmlReaders, IEnumerable1 filePaths, Boolean throwOnError, IDbDependencyResolver resolver, DbProviderManifest& providerManifest, DbProviderFactory& providerFactory, String& providerInvariantName, String& providerManifestToken, Memoizer2& cachedCTypeFunction) at System.Data.Entity.Core.Metadata.Edm.StoreItemCollection..ctor(IEnumerable1 xmlReaders) at System.Data.Entity.Utilities.XDocumentExtensions.GetStorageMappingItemCollection(XDocument model, DbProviderInfo& providerInfo) at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.Diff(XDocument sourceModel, XDocument targetModel, Lazy1 modificationCommandTreeGenerator, MigrationSqlGenerator migrationSqlGenerator, String sourceModelVersion, String targetModelVersion) at System.Data.Entity.Internal.InternalContext.ModelMatches(VersionedModel model) at System.Data.Entity.Internal.ModelCompatibilityChecker.CompatibleWithModel(InternalContext internalContext, ModelHashCalculator modelHashCalculator, Boolean throwIfNoMetadata, DatabaseExistenceState existenceState) at System.Data.Entity.Internal.InternalContext.CompatibleWithModel(Boolean throwIfNoMetadata, DatabaseExistenceState existenceState) at System.Data.Entity.CreateDatabaseIfNotExists1.InitializeDatabase(TContext context) at System.Data.Entity.Internal.InternalContext.<>c__DisplayClassf1.<CreateInitializationAction>b__e() at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) --- End of inner exception stack trace --- at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() at System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c) at System.Data.Entity.Internal.RetryAction1.PerformAction(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action1 action) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) at System.Data.Entity.Internal.Linq.InternalSet1.Initialize() at System.Data.Entity.Internal.Linq.InternalSet1.GetEnumerator() at System.Data.Entity.Infrastructure.DbQuery1.System.Collections.Generic.IEnumerable.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Med.Vax.DataAccess.Repositories.LocalDbRepository1.GetAll() in C:\MedRepo\vax-box-sw\App\DataAccess\Repositories\LocalDbRepository.cs:line 68 at Med.Vax.App.d__28.MoveNext() in C:\MedRepo\vax-box-sw\App\App\App.xaml.cs:line 198 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task ta'Vax.exe' (CLR v4.0.30319: Vax.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\PresentationFramework-SystemXmlLinq\v4.0_4.0.0.0__b77a5c561934e089\PresentationFramework-SystemXmlLinq.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled. 'Vax.exe' (CLR v4.0.30319: Vax.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\PresentationFramework-SystemCore\v4.0_4.0.0.0__b77a5c561934e089\PresentationFramework-SystemCore.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled. sk) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() at Med.Vax.App.d__30.MoveNext() in C:\MedRepo\vax-box-sw\App\App\App.xaml.cs:line 503
ADDITIONAL INFO BASED ON COMMENTS
There are two app.config files, and perhaps this is part of the problem. One in the main app part, and one in the data access layer. Notice the code that is commented out with <!-- --> I asssume this is perfectly fine to do as these are just xml files?
Here's the one in the main app:
<?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" />
<sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="TruMed.AccuVax.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
</sectionGroup>
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<appSettings>
<add key="LoggingVerbosity" value="Debug" />
<add key="StartMaximized" value="True" />
<add key="AllowMultipleInstances" value="False" />
<add key="ScreenSaverTimeoutSeconds" value="60" />
<add key="TranslationFile" value="Translation\TranslationData.xml" />
<add key="SoftwareUpgradeDirectory" value="C:\TruMed\SoftwareUpgrade" />
<add key="FirmwareUpgradeDirectory" value="C:\TruMed\FirmwareUpgrade" />
<add key="PerformDatabaseMaintenance" value="False" />
<add key="AWSRegion" value="us-west-2"/>
<add key="AWSProfilesLocation" value="C:\TruMed\AccuVax\credentials"/>
</appSettings>
<!--
<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>
<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>
-->
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
<userSettings>
<TruMed.AccuVax.Properties.Settings>
<setting name="RobotZParkingLocation" serializeAs="String">
<value>0</value>
</setting>
</TruMed.AccuVax.Properties.Settings>
</userSettings>
</configuration>
and here is the one from the data access layer:
<?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" />
<!-- 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.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>
<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>
-->
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
</configuration>
Except for the commented out sections, I do not believe I am mentioning SQL Server Compact!
ADDITIONAL INFO IN RESPONSE TO COMMENTS 2
The configuration string used is pulled from a .json file and looks like this:
LocalDbConnection:
Server=(LocalDB)\\MSSQLLocalDB;Integrated Security=true;Initial Catalog=Vax3
Previously I was using:
Data Source=C:\ProgramData\TruMed\AccuVax\AccuVax.sdf;
Clearly there is some concern I'm using the wrong connection string and/or adapter as I'm getting messages about SQL Server Compact. I have searched the code for AccuVax.sdf and have not found any instances. Again, database is created no problem; the problem seems to be accessing it.
