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

[mohamadhasansalmaaniyaan72]

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, 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.<>cDisplayClass0_0.<.ctor>b0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>cDisplayClass3_0`1.b0() 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.<>cDisplayClass0_0.<.ctor>b0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>cDisplayClass3_0`1.b0() 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.

[ErikEJ]

ErikEJ.EntityFramework.SqlServerCompact is a EF 6 "Classic" provider and you can use the Entity Framework Tools installed with Visual Studio to generate database first code with that provider.

[mohamadhasansalmaaniyaan72]

Scaffold-DbContext "Data Source=....." -provider ErikEJ.EntityFramework.SqlServerCompact

That is, what should I use in the provider section to convert the tables into models and add them to the project. Because I have also used EntityFrameworkCore.SqlServerCompact40 in the provider part and it does not create and gives an error

[ErikEJ]

EntityFrameworkCore.SqlServerCompact40 only works with .NET Framework or .NET Core 2.2

[mohamadhasansalmaaniyaan72]

So what should be left? If you need a library or something else, tell me. thanks

[ErikEJ]

What do you mean by "left"???

ErikEJ.EntityFramework.SqlServerCompact is a EF 6 "Classic" (not EF Core!) provider and you can use the Entity Framework Tools installed with Visual Studio to generate database first code with that provider.

[mohamadhasansalmaaniyaan72]

What I meant by the previous comment is, so what provider should be used, since all that you say is installed on Windows. I have used all the providers, but it does not convert the tables into models. I wrote all the errors and updated the post.

[ErikEJ]

You CANNOT use "Scaffold-DbContext" with SQL Server Compact and .NET 6 or later - it is simply not supported.

What you can do is use my Entity Framework (not Core!) provider, as described here:

https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/workflows/existing-database

[mohamadhasansalmaaniyaan72]

ErikEJ.EntityFramework.SqlServerCompact -Version 6.4.0-beta4 What has changed in this version? Will scaffold-dbcontext be added in the final version?

And what does it mean that it is simply not supported? It means that I have to do something else, or the problem is with .net core or the problem is with the library.

[ErikEJ]

ErikEJ.EntityFramework.SqlServerCompact is not an EF Core package and will never support scaffold-dbcontext command.

It is an Entity Framework Classic package.

But it will enable you to use SQL Server Compact with .NET 7, which is what you are asking for, is it not?

Did you read the link above?

[mohamadhasansalmaaniyaan72]

Yes, I read the link above, I wanted to use the newer version. Does this mean that this version only supports the Code First method and in the final version it will not support the database first method written with scaffold-dbcontext at all? (As a suggestion and feedback, do this in the final version if possible.)

[ErikEJ]

The provider is not an EF Core provider and supports database first as described in the link (code first from database, but it is the same thing). Suggest you read and study some documentation sites.

[ErikEJ]

@mhsalmanian72 I have created a draft blog post here, that explains the (somewhat complicated) workflow: https://github.com/ErikEJ/erikej.github.io/blob/master/_drafts/2023-xx-xx-sqlce-net7-ef6.md

[mohamadhasansalmaaniyaan72]

Thank you What library is required for this code only? DbConfiguration.SetConfiguration(new SqlCeDbConfiguration()) Because it doesn't recognize this namesapce: using System.Data.Entity.SqlServerCompact;

---

If I want instead of the above code In the Context class, in the OnConfiguring method, write the following code:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
     optionsBuilder.UseSqlCe(connection);
}

What should I do, is it possible or not? Because when I add the EntityFrameworkCore.SqlServerCompact40 library, the UseSqlCe method is added, but it gives an error in execution. Error text: Could not load type 'Microsoft.EntityFrameworkCore.Infrastructure.IDbContextOptionsExtensionWithDebugInfo' from assembly 'Microsoft.EntityFrameworkCore, Version=7.0.10.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'.'

[ErikEJ]

It is all explained in the draft blog post.

EntityFrameworkCore.SqlServerCompact40 is a peprecated .NET Core 2.2 provider and does NOT work with .NET 6 or later (as I have tried to explain many many times)

[ErikEJ]

@mhsalmanian72 I have now published the blog post https://erikej.github.io/sqlce/entityframework/2023/09/27/sqlce-net7-ef6.html