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,
Microsoft.EntityFrameworkCore.Tools

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 -Project "Databases" -Force -ContextDir "[Dir]\Context" -OutputDir "[Dir]" - UseDatabaseNames -NoPluralize

Error Text:

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.

SQL Server CE is out of support. Why are you using it, and where will you find the binaries? You realize that *you* will be responsible for any bugs in the product if you use it? — Panagiotis Kanavos, Sep 01 '23 at 09:34
While SQL Server CE was still supported, @ErikEJ maintained the [EF provider for SQL Server CE](https://github.com/ErikEJ/EntityFramework.SqlServerCompact). This only targets on .NET Framework because SQL Server CE itself only run on Windows. The NuGet package is still available but no longer maintained — Panagiotis Kanavos, Sep 01 '23 at 09:40
Why not target SQLite instead? EF Core already supports SQLite - in fact the provider was built and maintained by ErikeEJ too. It was so good it was eventually incorporated into EF Core itself. There *are* some significant differences compared to CE, especially around concurrent access and transaction behavior, but CE is already unsupported — Panagiotis Kanavos, Sep 01 '23 at 09:41
What's your actual scenario? There may be another MS product that fits, although it would almost certainly involve an Azure subscription. — Panagiotis Kanavos, Sep 01 '23 at 09:52
I want to store my data in a resource that requires the least amount of external programs to install the program on the user's computer, and its volume is small, and the speed of reading and writing on that resource is average, that's why I want to use SQL Server CE. If there is something else, please suggest and tell how to use it. I want the information to be on the user's computer, not via Azure. @PanagiotisKanavos — Mohammad Hasan Salmanian, Sep 01 '23 at 11:01
Why not SQLite? I repeat, SQL Server Compact is out of support. The last release was 10 years ago, so that was more than enough time for people to migrate away from it. You had to install it separately too - either directly, or through a Windows Installer merge module. The engine was never included in .NET itself — Panagiotis Kanavos, Sep 01 '23 at 11:02
And likewise, if there is another library, a side program that must be installed on the user's computer, tell me. @PanagiotisKanavos — Mohammad Hasan Salmanian, Sep 01 '23 at 11:07
On the other hand, the SQLite engine is just a library. When you add `Microsoft.EntityFrameworkCore.Sqlite` to your package, a [SQLite library](https://www.nuget.org/packages/SQLitePCLRaw.bundle_e_sqlite3/) is added to the project. Nothing else has to be installed — Panagiotis Kanavos, Sep 01 '23 at 11:07
You can use an example of `Scaffold-DbContext` where the desired sqlite database file has a password. @PanagiotisKanavos — Mohammad Hasan Salmanian, Sep 01 '23 at 14:14
I asked another question and explained all the problems there: https://stackoverflow.com/questions/77024042/how-to-use-scaffold-dbcontext-in-net-core-for-sqlite-database @PanagiotisKanavos — Mohammad Hasan Salmanian, Sep 01 '23 at 15:18
I have a EF C´6 Classic prodvider for SQL Server Compact, I suggest you use that: https://www.nuget.org/packages/ErikEJ.EntityFramework.SqlServerCompact/6.4.0-beta4 — ErikEJ, Sep 02 '23 at 13:52
And SQL Server Compact supports a database password, correct — ErikEJ, Sep 02 '23 at 13:54

ErikEJ · Answer 1 · 2023-09-03T06:50:04.570

0

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.

edited Sep 03 '23 at 06:50

answered Sep 02 '23 at 14:05

ErikEJ

40,951
5
75
115

I don't say if there is Ef Core 7 provider or not, I mean that I use .net core version 7 – Mohammad Hasan Salmanian Sep 02 '23 at 19:29
Can you tell me when the final version will be released? – Mohammad Hasan Salmanian Sep 02 '23 at 19:34
If you have given the post -1, please remove it because it has not yet been published to the library officially and I did not see it in nuget either. – Mohammad Hasan Salmanian Sep 02 '23 at 19:39
How can this library be used by the Database First method? – Mohammad Hasan Salmanian Sep 02 '23 at 19:59

score -1 · Answer 2 · answered Sep 02 '23 at 05:08

-1

First Add the Microsoft.Data.SqlClient NuGet package. This contains the ADO.NET provider for SQL CE according to this then Add the Microsoft.EntityFrameworkCore.SqlServer NuGet package. This contains the EF Core provider for SQL Server.

now in your DbContext class, specify the SQL CE provider and connection string:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
  optionsBuilder.UseSqlServer(
    @"Data Source=C:\mydb.sdf;Password=mypassword123"); 
}

the last step run scaffold

Scaffold-DbContext "Data Source=C:\mydb.sdf;Password=mypassword123" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

answered Sep 02 '23 at 05:08

sep7696

494
2
16

Did you try it yourself and it didn't give an error? – Mohammad Hasan Salmanian Sep 02 '23 at 10:17
@MohammadHasanSalmanian i test it many years ago but approach is correct – sep7696 Sep 02 '23 at 10:41
@MohammadHasanSalmanian u can check this out dude https://stackoverflow.com/questions/18060667/cannot-connect-to-server-a-network-related-or-instance-specific-error I can guide here, not complete your project so try it ur self goodluck – sep7696 Sep 02 '23 at 12:03
Have you ever worked with **SQL Server Compact**? – Mohammad Hasan Salmanian Sep 02 '23 at 13:33
@MohammadHasanSalmanian goodluck mate – sep7696 Sep 02 '23 at 17:12

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

2 Answers2