Search code examples
c#.net-coreentity-framework-coresql-server-cesdf

How to use Scaffold-DbContext in .NET Core for SQL Server Compact (*.sdf File) database

I'm using .NET Core Version 7 and I want to use EF Core to access a SQL Server Compact (*.sdf) database file. What libraries to use and how to use Scaffold-DbContext to build the existing database models and also the database has a password.

I want to use database first method. I added this library to the project:

[ErikEJ.EntityFramework.SqlServerCompact or EntityFrameworkCore.SqlServerCompact40] ,
Microsoft.EntityFrameworkCore.Tools

If the problem is with the provider, what library should be added to the project and what provider should be written?

I have Sql Server Compact and SQLite/Sql Server Compact Toolbox installed, But when I run Scaffold-DbContext as follows, it gives this error:

 Scaffold-DbContext "Data Source=Path\db.sdf;Password=***" -provider
 [ErikEJ.EntityFramework.SqlServerCompact or
EntityFrameworkCore.SqlServerCompact40] ...

enter image description here

Error Text When I am using ErikEJ.EntityFramework.SqlServerCompact library::

System.InvalidOperationException: Unable to find expected assembly attribute [DesignTimeProviderServices] in provider assembly 'ErikEJ.EntityFramework.SqlServerCompact'. This attribute is required to identify the class which acts as the design-time service provider factory for the provider. at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.ConfigureProviderServices(String provider, IServiceCollection services, Boolean throwOnError) at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.CreateServiceCollection(String provider) at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.Build(String provider) at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable1 schemas, IEnumerable1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable1 schemaFilters, IEnumerable1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) Unable to find expected assembly attribute [DesignTimeProviderServices] in provider assembly 'ErikEJ.EntityFramework.SqlServerCompact'. This attribute is required to identify the class which acts as the design-time service provider factory for the provider.

Error Text When I am using EntityFrameworkCore.SqlServerCompact40 library:

System.TypeLoadException: Method 'Create' in type 'EFCore.SqlCe.Scaffolding.Internal.SqlCeDatabaseModelFactory' from assembly 'EntityFrameworkCore.SqlServerCompact40, Version=2.2.0.0, Culture=neutral, PublicKeyToken=9af395b34ac99006' does not have an implementation. at EFCore.SqlCe.Design.Internal.SqlCeDesignTimeServices.ConfigureDesignTimeServices(IServiceCollection serviceCollection) at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.ConfigureDesignTimeServices(Type designTimeServicesType, IServiceCollection services) at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.ConfigureProviderServices(String provider, IServiceCollection services, Boolean throwOnError) at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.CreateServiceCollection(String provider) at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.Build(String provider) at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable1 schemas, IEnumerable1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable1 schemaFilters, IEnumerable1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) Method 'Create' in type 'EFCore.SqlCe.Scaffolding.Internal.SqlCeDatabaseModelFactory' from assembly 'EntityFrameworkCore.SqlServerCompact40, Version=2.2.0.0, Culture=neutral, PublicKeyToken=9af395b34ac99006' does not have an implementation.

Solution

  • I have a EF 6 Classic prodvider for SQL Server Compact - I suggest you use that.

    The final version of the package will be released when I have gotten feedback from users that it works as expected.

    You can view the package in NuGet UI in Visual Studio if you select "Include prerelease".

    You can use the package with the EF 6 Database First toling if you install the SQL Server Compact runtime and the SQLite / SQL Server Compact Tools extension.

    There is no EF Core .NET 7 provider for SQL Server Compact.