A Case of SQLite with Entity Framework Core in ASP.NET Core

2019-05-31

Here we have a case of SQLite with Entity Framework Core in ASP.NET Core:

1: Get Visual Studio 2019, .NET Core 2.2

We can get some info from https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/new-db?tabs=visual-studio)

2: Create an ASP.NET Core 2.2 MVC project

Create model class:

    public class MyData
    {
        [Key]
        public int MID { get; set; }
        public string MName { get; set; }
        public string MDesc { get; set; }

        public ICollection<SData> SDatas { get; set; }
    }

    public class SData
    {
        [Key]
        public int SID { get; set; }
        public string SName { get; set; }
        public string SContent { get; set; }

        public int MID { get; set; }
        public MyData MyData { get; set; }
    }

In Visual Studio 2019, menu "Extensions", add new extension "SQLite/SQL Server Compact Toolbox", this can have a manage UI for Sqlite in the Visual Studio. (after installed, Tools > SQLite/SQL Server Compact Toolbox too see, from here, we can create a Sqlite database, add a new sqlite connection ... Create button )

Visual Studio 2019, right click the project, and Nuget package, install "Microsoft.EntityFrameworkCore.Sqlite", also, maybe "Microsoft.EntityFrameworkCore.Design" ?

In Visual Studio 2019, Controller folder, add a new Controller (via "MVC Controller with views, using Entity Framework"), based on existing Model class, click '+' button to create a new Context, Visual Studio will generate code for new Controller, new views and new Data context.

    public class CoreWeb1Context : DbContext
    {
        public CoreWeb1Context (DbContextOptions<CoreWeb1Context> options)
            : base(options)
        {
        }

        public DbSet<CoreWeb1.Models.MyData> MyData { get; set; }

        public DbSet<CoreWeb1.Models.SData> SData { get; set; }
    }

In the file "appsettings.json"

    "DBContext": "Server=(localdb)\\mssqllocaldb;Database=DBContext-9de81597-33e1-49c5-8a1f-bfbde298a4b8;Trusted_Connection=True;MultipleActiveResultSets=true",
    "CoreWeb1Context": "Data Source=Data/mysqlite01.db"

we can have multiple Context in the file, but for simple, maybe we just keep one Context, such as "CoreWeb1Context"

How to generate tables in Sqlite database?

After we set done above, in the Windows command line, input the 2 commands as below:

dotnet ef migrations add InitialCreate --context=CoreWeb1Context
dotnet ef database update --context=CoreWeb1Context

Now we have data tables in the Sqlite database.

About the migrations, we also can get information from Microsoft site

If updated the model, we should run the migration commands:

dotnet ef migrations add AddDescInMyData
dotnet ef database update