.NET 6.0 - Connect to SQLite Database with Entity Framework Core
Tutorial built with .NET 6.0
This post shows goes through the steps to connect a .NET 6 API to SQLite using Entity Framework Core, and automatically create/update the SQLite database from code using EF Core migrations.
We'll start with an example .NET 6 CRUD API from a tutorial I posted recently, it uses the EF Core InMemory db provider by default for testing, we'll update it to connect to a SQLite database and run EF Core migrations to auto generate the database and tables from code. For full details about the .NET CRUD API see .NET 6.0 - CRUD API Example and Tutorial.
Tutorial Contents
- Tools required for this tutorial
- Download & Run the example .NET API
- Update API to use SQLite
- Create SQLite Database with EF Core Migrations
- Restart .NET CRUD API
Tools required for this tutorial
To follow the steps in this tutorial you'll need the following:
- .NET SDK - includes the .NET runtime and command line tools.
- Visual Studio Code - code editor that runs on Windows, Mac and Linux. If you have a different preferred code editor that's fine too.
- C# extension for Visual Studio Code - adds support to VS Code for developing .NET applications.
- SQLite extension for Visual Studio Code - adds support to VS Code for browsing and querying SQLite databases.
Download & Run the example .NET API
Follow these steps to download and run the .NET 6 CRUD API on your local machine with the default EF Core InMemory database:
- Download or clone the tutorial project code from https://github.com/cornflourblue/dotnet-6-crud-api
- Start the api by running
dotnet runfrom the command line in the project root folder (where the WebApi.csproj file is located), you should see the messageNow listening on: http://localhost:4000. - You can test the API directly with a tool such as Postman or hook it up with the example Angular or React app available.
Starting in debug mode
You can also start the application in debug mode in VS Code by opening the project root folder in VS Code and pressing F5 or by selecting Debug -> Start Debugging from the top menu, running in debug mode allows you to attach breakpoints to pause execution and step through the application code. For detailed instructions including a short demo video see VS Code + .NET - Debug a .NET Web App in Visual Studio Code.
Update .NET API to use SQLite
Add SQLite database provider from NuGet
Run the following command from the project root folder to install the EF Core database provider for SQLite from NuGet:
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
Add connection string to app settings
Open the appsettings.json file and add the entry "ConnectionStrings" with a child entry for the SQLite connection string (e.g. "WebApiDatabase"), the connection string should be in the format "Data Source=[DB FILE NAME];".
When EF Core migrations generates the database, the Data Source value will be the name of the file created for the SQLite database.
The updated appsettings.json file with the connection string should look something like this:
{
"ConnectionStrings": {
"WebApiDatabase": "Data Source=LocalDatabase.db"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
}
}
Update Data Context to Use SQLite
The DataContext class located at /Helpers/DataContext.cs is used for accessing application data through Entity Framework. It derives from the Entity Framework DbContext class and has a public Users property for accessing and managing user data.
Update the OnConfiguring() method to connect to SQLite instead of an in memory database by replacing options.UseInMemoryDatabase("TestDb"); with options.UseSqlite(Configuration.GetConnectionString("WebApiDatabase"));.
The updated DataContext class should look like this:
namespace WebApi.Helpers;
using Microsoft.EntityFrameworkCore;
using WebApi.Entities;
public class DataContext : DbContext
{
protected readonly IConfiguration Configuration;
public DataContext(IConfiguration configuration)
{
Configuration = configuration;
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
// connect to sqlite database
options.UseSqlite(Configuration.GetConnectionString("WebApiDatabase"));
}
public DbSet<User> Users { get; set; }
}
Create SQLite Database from code with EF Core Migrations
Install dotnet ef tools
The .NET Entity Framework Core tools (dotnet ef) are used to generate EF Core migrations, to install the EF Core tools globally run dotnet tool install -g dotnet-ef, or to update run dotnet tool update -g dotnet-ef. For more info on EF Core tools see https://docs.microsoft.com/ef/core/cli/dotnet
Add EF Core Design package from NuGet
Run the following command from the project root folder to install the EF Core design package, it provides cross-platform command line tooling support and is used to generate EF Core migrations:
dotnet add package Microsoft.EntityFrameworkCore.Design
Generate EF Core migrations
Generate new EF Core migration files by running the command dotnet ef migrations add InitialCreate from the project root folder (where the WebApi.csproj file is located), these migrations will create the database and tables for the .NET Core API.
Execute EF Core migrations
Run the command dotnet ef database update from the project root folder to execute the EF Core migrations and create the database and tables in SQLite.
Check SQLite Database in VS Code
There should be a new file in the project root folder that contains the SQLite database (e.g. LocalDatabase.db. Open the SQLite db file and it should contain your database with the tables Users and __EFMigrationsHistory. To explore and query SQLite databases you can install a free VS Code extension, I'm currently using the appropriately named SQLite extension - https://marketplace.visualstudio.com/items?itemName=alexcvzz.vscode-sqlite.
Restart .NET 6.0 CRUD API
Stop and restart the API with the command dotnet run from the project root folder, you should see the message Now listening on: http://localhost:4000 and the API should now be connected to SQLite.
Need Some .NET Help?
Search fiverr for freelance .NET developers.
Follow me for updates
When I'm not coding...
Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!