ASP.NET CORE MVC - SQLite with Entity Framework Core

ASP.NET CORE MVC TUTORIAL SERIES · PART 4

Connecting ASP.NET Core MVC to SQLite with Entity Framework Core

Replace the temporary in-memory product data with a real SQLite database. Install Entity Framework Core, create a DbContext, configure a connection string, register the context with dependency injection, create a migration, and generate the first database.

Objective

By the end of this tutorial, the Product Management application will have a working SQLite database containing a Products table managed through Entity Framework Core.

Lab environment

This series uses a Xubuntu virtual machine with Linux username xubuntu, .NET 8, Visual Studio Code, and the project created in Parts 1–3.

In this tutorial

1. Verify the Part 3 project
2. Understand persistence and ORM concepts
3. Install EF Core for SQLite
4. Create ApplicationDbContext
5. Configure the connection string
6. Register EF Core with dependency injection
7. Create the first migration
8. Create and inspect the SQLite database
9. Troubleshoot common problems

1. Open and Verify the Existing Project

Open the Xubuntu Terminal and return to the project:

cd ~/aspnet-mvc-tutorial/ProductManagement
pwd

The expected path is:

/home/xubuntu/aspnet-mvc-tutorial/ProductManagement

Confirm the important Part 3 files:

ls Models/Product.cs
ls Controllers/ProductsController.cs
ls Views/Products/Index.cshtml
ls Views/Products/Details.cshtml

Open the project:

code .

Build it before making database changes:

dotnet build

Checkpoint

Continue when the four files exist and the build succeeds.

2. Why We Need Persistent Storage

In Part 3, ProductsController created product objects directly in memory. They disappear when the application stops. A database allows records to persist between application runs.

Part 3 Controller → Product objects → Memory Part 4 Application → EF Core → SQLite → Database file

3. SQLite and Entity Framework Core

SQLite is a relational database engine that stores the database in a file. Entity Framework Core (EF Core) is an object-relational mapper, or ORM, that allows the application to work with C# entities while EF Core handles database interaction.

Product object ↕ Entity Framework Core ↕ Products table ↕ SQLite database

C#	Database
Product class	Products table
Product.Id	Id column
Product.Name	Name column
A Product object	A product row

4. Confirm .NET 8

dotnet --version
cat ProductManagement.csproj

Confirm that the project contains:

<TargetFramework>net8.0</TargetFramework>

5. Install the EF Core SQLite Provider

Install the SQLite provider on the EF Core 8 release line:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 8.0.20

Add the design-time package used by EF Core tools:

dotnet add package Microsoft.EntityFrameworkCore.Design --version 8.0.20

Check the packages:

dotnet list package

You should see Microsoft.EntityFrameworkCore.Sqlite and Microsoft.EntityFrameworkCore.Design.

6. Install the EF Core Command-Line Tool

Install dotnet-ef:

dotnet tool install --global dotnet-ef --version 8.0.20

If the tool is already installed, use:

dotnet tool update --global dotnet-ef --version 8.0.20

Verify it:

dotnet ef --version

Version consistency

This series targets .NET 8, so the tutorial deliberately keeps the EF Core provider, design package, and command-line tool on the EF Core 8 release line.

7. Create the Data Folder

mkdir -p Data
touch Data/ApplicationDbContext.cs
code Data/ApplicationDbContext.cs

8. Create ApplicationDbContext

Add:

using Microsoft.EntityFrameworkCore;
using ProductManagement.Models;

namespace ProductManagement.Data;

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

    public DbSet<Product> Products { get; set; } = null!;
}

DbContext is the main EF Core class through which the application interacts with its database. DbSet<Product> represents the set of Product entities that can be queried and saved.

Controller ↓ ApplicationDbContext ↓ DbSet<Product> ↓ Entity Framework Core ↓ SQLite

9. Configure the SQLite Connection String

Open appsettings.json and add a ConnectionStrings section:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=ProductManagement.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

The connection string tells SQLite to use the file:

ProductManagement.db

10. Register ApplicationDbContext

Open Program.cs. Add:

using Microsoft.EntityFrameworkCore;
using ProductManagement.Data;

After var builder = WebApplication.CreateBuilder(args);, register the context:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(
        builder.Configuration.GetConnectionString("DefaultConnection")));

Keep the rest of your existing working Program.cs code.

Do not unnecessarily replace Program.cs

ASP.NET Core templates can differ slightly depending on SDK/template updates. Add the EF Core configuration to the working file created in Part 1 rather than replacing unrelated middleware and routing code.

11. What Dependency Injection Does Here

Program.cs ↓ Register ApplicationDbContext ↓ ASP.NET Core DI container ↓ Controller requests the context ↓ Configured context supplied automatically

We will inject this context into ProductsController in Part 5.

12. Build Before Creating a Migration

dotnet build

Continue only when the output ends with:

Build succeeded.

13. Create the Initial Migration

dotnet ef migrations add InitialCreate

This creates a Migrations directory. Check it:

ls Migrations

The timestamp varies, but you should see files similar to:

2026XXXXXXXXXX_InitialCreate.cs
2026XXXXXXXXXX_InitialCreate.Designer.cs
ApplicationDbContextModelSnapshot.cs

A migration is a versioned description of changes required for the database schema.

Product model ↓ dotnet ef migrations add ↓ Migration files ↓ database update ↓ SQLite schema

14. Inspect the Migration

Open the generated file ending with _InitialCreate.cs. In its Up() method, you should find instructions for creating the Products table with columns corresponding to:

Id
Name
Description
Price
Quantity

15. Create the SQLite Database

Apply the migration:

dotnet ef database update

Then verify the database file:

ls -l ProductManagement.db

Database created

Your application now has a SQLite database schema containing the Products table.

16. Inspect SQLite from the Terminal

The SQLite command-line program is useful for inspecting the database. Install it if necessary:

sudo apt update
sudo apt install sqlite3

Open the database:

sqlite3 ProductManagement.db

At the SQLite prompt:

.tables
.schema Products

You should see Products and __EFMigrationsHistory. Exit with:

.quit

17. What Is __EFMigrationsHistory?

EF Core uses the __EFMigrationsHistory table to record migrations already applied to the database.

Migration ↓ database update ↓ __EFMigrationsHistory ↓ EF Core knows the database version

18. Current Project Structure

ProductManagement/
├── Controllers/
│   ├── HomeController.cs
│   └── ProductsController.cs
├── Data/
│   └── ApplicationDbContext.cs
├── Migrations/
│   ├── ..._InitialCreate.cs
│   ├── ..._InitialCreate.Designer.cs
│   └── ApplicationDbContextModelSnapshot.cs
├── Models/
│   ├── ErrorViewModel.cs
│   └── Product.cs
├── Views/
│   ├── Home/
│   ├── Products/
│   │   ├── Index.cshtml
│   │   └── Details.cshtml
│   └── Shared/
├── wwwroot/
├── appsettings.json
├── ProductManagement.db
├── ProductManagement.csproj
└── Program.cs

19. Important: ProductsController Is Not Database-Backed Yet

The database now exists, but the Part 3 ProductsController still creates its sample Product objects in memory.

ProductsController → hard-coded products ApplicationDbContext → SQLite database Part 5 will connect these together.

This is intentional

Part 4 focuses on database infrastructure. Part 5 will inject ApplicationDbContext into the controller and replace the hard-coded list with real database operations.

20. Run the Application

dotnet run

Use the localhost address displayed in the terminal and test:

/Products
/Products/Details/1

The pages should still work as in Part 3. Their displayed product data is still temporary at this stage.

21. Troubleshooting

dotnet ef command not found

Check installed global tools:

dotnet tool list --global

If necessary:

dotnet tool install --global dotnet-ef --version 8.0.20

UseSqlite is not recognised

Check the package and namespace:

dotnet list package

using Microsoft.EntityFrameworkCore;

Unable to create ApplicationDbContext

Confirm the context has a DbContextOptions<ApplicationDbContext> constructor and is registered in Program.cs.

ProductManagement.db does not exist

Creating a migration does not apply it. Run:

dotnet ef database update

The Products table is empty

This is expected. Part 4 creates the schema but does not insert product records. Database-backed Create and Read operations begin in Part 5.

22. Verification Exercise

From the project directory, run:

dotnet build
dotnet ef migrations list
ls -l ProductManagement.db
sqlite3 ProductManagement.db

Inside SQLite:

.tables
.schema Products
.quit

Confirm that the project builds, InitialCreate exists, the database file exists, and the database contains a Products table.

23. Knowledge Check

1. What problem does persistent storage solve?
2. What does ORM stand for?
3. What role does EF Core play?
4. What is ApplicationDbContext?
5. What does DbSet<Product> represent?
6. Where is the connection string stored?
7. Why is the context registered in Program.cs?
8. What does dotnet ef migrations add InitialCreate do?
9. What does dotnet ef database update do?
10. Why does EF Core create __EFMigrationsHistory?
11. Does ProductsController read from SQLite at the end of Part 4?

Show suggested answers

1. It keeps data available after the application stops or restarts.
2. Object-Relational Mapper.
3. It maps C# entities to database structures and provides database access APIs.
4. The EF Core database context for the application.
5. The set of Product entities that EF Core can query and save.
6. In appsettings.json.
7. So dependency injection can provide a configured context to classes that need it.
8. It generates migration files describing the initial schema.
9. It applies pending migrations to the configured database.
10. To record which migrations have already been applied.
11. No. That begins in Part 5.

24. Part 4 Summary

• introduced persistent storage, SQLite and ORM concepts;
• installed the EF Core SQLite provider and design-time tools;
• created ApplicationDbContext and DbSet<Product>;
• configured the SQLite connection string;
• registered the context with dependency injection;
• created the InitialCreate migration;
• applied the migration and created ProductManagement.db;
• inspected the Products table and migration history.

Product Model ↓ ApplicationDbContext ↓ Entity Framework Core ↓ SQLite ↓ ProductManagement.db

Next: Part 5 — CRUD: Create and Read

In Part 5, we will inject ApplicationDbContext into ProductsController, retrieve products asynchronously from SQLite, display database records, create a product form, use model binding, and save new products with SaveChangesAsync().

Official references

• SQLite Database Provider — EF Core
• Getting Started with EF Core
• EF Core Migrations
• DbContext Configuration