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

Question

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

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, IEnumerable`1 schemas,

  IEnumerable`1 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, IEnumerable`1

  schemaFilters, IEnumerable`1 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.<Execute 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.

Answer 1

Hi @Mohammad Hasan Salmanian , Welcome to Microsoft Q&A,

According to Database Providers, EntityFrameworkCore.SqlServerCompact40 only supports .NET Framework and EF Core 2. This You are having trouble using Sql Server Compact. For lightweight databases, it is more recommended that you use SQLite, which EF Core has better support for.

Best Regards,

Wenbin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

sql server compact support ended a couple years ago. There is no .net core version. look at either sql express or SQLite for a replacement.

if you have a sql compact database you need to access, you will need to use the old framework to build an application to access.

https://www.microsoft.com/en-US/download/details.aspx?id=30709

Answer 3

The only library that can reverse engineer this database is EntityFramework Reverse POCO Generator.

• Highly rated
• Must understand how to work with T4 templates (their template is well commented) Get T4 Language extension for colorizing templates.
• It is not free, 99.00 per year and has a trial version.

Answer 4

I am trying to add a view without using underlying table in SQL server and did not work.

Scaffold-DbContext Name=StaffContext Microsoft.EntityFrameworkCore.SqlServer -Views vw_Employee -OutputDir "Entities/Staff" -Context StaffContext -Force -NoPluralize

A parameter cannot be found that matches parameter name 'Views'.

At line:1 char:82