How to use Scaffold-DbContext in .NET Core for Sqlite database

Question

I'm using .NET Core Version 7 and I want to use EF Core to access a Sqlite (*.db3) database file.

This file has a password and I want to add the database tables to the project through database first. The scaffold-dbcontext code is as follows, but it gives an error and does not bring the tables. What is the problem?

In general, SQlite database tables can be added to the project through DatabaseFirst, or they should be added only through CodeFirst, because everywhere I searched, Sqlite databases are created only through CodeFirst.

I want to use database first method.

Scaffold-DbContext "DataSource=Path\db.db3;Password=***" -provider 
Microsoft.EntityFrameworkCore.Sqlite ...

Libraries that I have added in the project:

Microsoft.EntityFrameworkCore.Sqlite,
Microsoft.EntityFrameworkCore.Tools,
SQLitePCLRaw.bundle_e_sqlcipher,
Microsoft.EntityFrameworkCore.Sqlite.Design

This database file is read from SqliteStudio and displays the tables:

Error Text:

To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 26: 'file is not a database'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal.RetryWhileBusy(Func`1 action, Action reset, Int32 timeout, Stopwatch timer)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal.RetryWhileBusy(Func`1 action, Int32 timeout, Stopwatch timer)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal.ExecuteNonQuery(String sql, Int32 timeout)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal..ctor(SqliteConnectionStringBuilder connectionOptions, SqliteConnectionPool pool)
   at Microsoft.Data.Sqlite.SqliteConnectionPool.GetConnection()
   at Microsoft.Data.Sqlite.SqliteConnectionFactory.GetConnection(SqliteConnection outerConnection)
   at Microsoft.Data.Sqlite.SqliteConnection.Open()
   at Microsoft.EntityFrameworkCore.Sqlite.Scaffolding.Internal.SqliteDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Sqlite.Scaffolding.Internal.SqliteDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   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)
SQLite Error 26: 'file is not a database'.

Answer 1

Okay, its not possible to scaffold a SQLite database using a .NET Core framework data provider.

See

• Microsoft docs SQLite doesn't support encrypting database files by default
• SQLite with Password Protection where the article has I'm guessing hand crafted DbContext.
• Work with SQLite Databases Encrypted using SQLitePCLRaw.bundle_e_sqlcipher
• Your Stackoverflow post in regards to Brice
• Just Google ef core sqlite password and it goes on pretty much the same as above

What you might try

• Remove password protection
• Rename the database to something like SystemServices.dll
• Use the above in your connection string

My assumption is that a user or hacker is not going to look at a .dll file for data. And yes it might seem unprofessional but I've used this with other file types and never had someone peek at them.

Answer 2

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

I also used sqlite to perform dbfirst migration, and it was no problem. Here are my steps:

1. Install the following nuget packages:

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Sqlite

Microsoft.EntityFrameworkCore.Tools

2. Use the following command:

Scaffold-DbContext "Data Source=C:\Users\Administrator\demo.db" Microsoft.EntityFrameworkCore.Sqlite -OutputDir Models

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 3

Look at using EF Power Tools + VisualStudio.Data.Sqlite extensions, both free. Spend time reading the docs. When getting to data provider screen, pick other than you will see the data provider to SQLLite.

Nice thing is every thing is visual, here is one of the screens.

Answer 4

Hi Mohammad,

Removing package Microsoft.EntityFrameworkCore.Sqlite and installing Microsoft.EntityFrameworkCore.Sqlite.Core solved the same problem for me.

Take care about using "$" char in password which needs to be escaped : https://stackoverflow.com/questions/47399666/entity-framework-scaffold-dbcontext-login-failed-for-user