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?

Taegost · Accepted Answer · 2021-01-01 06:20:31Z

19

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?

Remove package Microsoft.Data.Sqlite
Add package Microsoft.Data.Sqlite.Core
Add package SQLitePCLRaw.bundle_e_sqlcipher
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.

edited Jan 1, 2021 at 6:20

Taegost

1,2151 gold badge18 silver badges26 bronze badges

answered Dec 30, 2019 at 2:50

Bala

7041 gold badge8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tinmac Over a year ago

In startup.cs don't forget to change UseSqlServer to UseSqlite : eg services.AddDbContext<ApplicationDbContext>(options => options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));

lightbulb112 · Accepted Answer · 2019-12-16 02:04:36Z

0

I recommended starting by placing your db file in the same directory as the project you are working on. Read the EF Core tutorial from microsoft docs, they use sqllite in that example.

answered Dec 16, 2019 at 2:04

lightbulb112

1331 silver badge9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related