How do I properly use my SQLite db in my asp.net core 3.1 MVC application using EF?

Question

So I'm trying to create a ASP.NET Core 3.1 MVC application which uses Identity and it's working well so far. I wanted to add a SQLite db to it so I can store users etc using EF (EntityFramework) Code First and this is what I did.

I first created a database file using SQLite, so I have the Database.db file in a folder on my desktop.

I then tried finding documentations regarding how to implement it, but there were so many and all of them are very different so I couldn't pinpoint what would be the proper way of implementing it.

I started by setting up my Context

public class UserContext : IdentityDbContext
{
    public DbSet<User> Users { get; set; }
}

public class User : IdentityUser
{
    public string CustomerID { get; set; }

    public string SubscriptionID { get; set; }

    public int IsActive { get; set; }
}

Super straight forward, and then I modified my ConfigureServices method which is in my Startup.cs to AddIdentity

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
    services.AddIdentity<User, IdentityRole>().AddEntityFrameworkStores<UserContext>();

}

Perfect, now I have Identity added. Great.

Here is the issue, I wanted to be able to create a initial migration to create a table in my database.db file but I have no idea where to put the connectionString and how to access it.

I read on some forums that I should store it inside appsettings.json, but what would the connectionString then look like since it's a file on my disk?

I do not believe that this would work

"ConnectionStrings": {
    "sqlConnection": "Server=C:\\Users\\user\\Desktop\\user\\Database\\Database.db;Database=Blogging;Trusted_Connection=True;\\\""
  }

or is that what the connectionString should look like?

How do I properly implement the SQLite database file to my project so that I can access and create my first migration?

I also didn't find a way to create the database.db file with a password when creating it, is this possible to change?

Answer 1

By following the below steps, the Sqlite Database will be created.

• Remove Microsoft.EntityFrameworkCore.SqlServer Nuget Package, as you are not using SqlServer

• Add Microsoft.EntityFrameworkCore.Sqlite Nuget Package

• Change DefaultConnection in appsettings.json to:

"DefaultConnection": "Data Source=UserDatabase.db"

• Delete the Migrations folder. (Note: The Migrations folder should not be deleted when you have deployed your database and upgrade your Table structure.)

• Open Package Manager Console and run: add-migration MigrationName

• Open Package Manager Console and run: update-database

If you have problems running the commands in Package Manager: You can run the same commands in a terminal window using the dotnet command, just note that it will create a Migrations folder in whichever folder you run the command in:

• dotnet ef migrations add MigrationName
• dotnet ef database update

The Sqlite Database will be created.

I also didn't find a way to create the database.db file with a password when creating it, is this possible to change?

1. Remove package Microsoft.Data.Sqlite

2. Add package Microsoft.Data.Sqlite.Core

3. Add package SQLitePCLRaw.bundle_e_sqlcipher

4. Change DefaultConnection in appsettings.json to:

"DefaultConnection": "Data Source=UserDatabase.db;Password=ComplexPassword"

Refer SQLite doesn't support encrypting database files by default. for more info.