Use SQLite in .NET Core 3 With Entity Framework Core

As you might know, ASP.NET is able to support many different types of Database providers. Today, I decided to test the use of SQLite in ASP.NET Core 3 with Entity Framework Core. For a quick demonstration, I’ll create a simple console application. You can follow along.

Create Sample Console Project

Create a .NET Core 3.x console application in Visual Studio 2019.

Next Dialog, give the project a name.

Install Entity Framework Core

When working with EF Core, you will want to install the correct package for the Entity Framework Core database provider you want to target in your project. Since this demo project is regarding SQLite, I am going to install the EF Core that supports SQLite.

Add NuGet package reference

Install-Package Microsoft.EntityFrameworkCore.Sqlite

Now that we have SQLite Support added, let’s add a simple model to the project to use a test.
Right-Click the project name, Add, and Class to add the new model as shown below.


Replace content of the employee model class with the following snippet.

namespace SQLiteAspNetCoreDemo
{
    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
    }
}

Create DBContext

Now we need to create the DBContext. Add a new class file and name it SQLiteDBContext.cs and add following code.

using Microsoft.EntityFrameworkCore;

namespace SQLiteAspNetCoreDemo
{
    public class SQLiteDBContext : DbContext
    {
        public DbSet Employees { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite("Data Source=sqlitedemo.db");
    }
}

The code above contains a basic DbSet which is used to create the Employees table.
OnConfiguring() method is used for configuring the DBContext. Here the option is set to use Sqlite database and also setting a connection string or data source to connect.

Now, we’re ready to test things out. Let’s try to add some employee data. Open Program.cs and add the following code.

The moment of truth. Before we can try to run the project we’ll need to run migration first using the following commands in Package Manager Console.
First we’ll need to add Microsoft.EntityFrameworkCore.Tools to allow us to create and apply migration.

Install-Package Microsoft.EntityFrameworkCore.Tools

Next, we need to add migration. Since this is the first migration we’re running we’ll call it InitialCreate.

Add-Migration InitialCreate

A new file will be created in the project with the following content

using Microsoft.EntityFrameworkCore.Migrations;

namespace SQLiteAspNetCoreDemo.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Employees",
                columns: table => new
                {
                    Id = table.Column(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    FirstName = table.Column(nullable: true),
                    LastName = table.Column(nullable: true),
                    Age = table.Column(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employees", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Employees");
        }
    }
}

Update-Database

After running the update command above, we can see the result in the output windows as shown below.

Update the program

We can start testing things out and see if it works. Find the Program.cs file and replace its content with the following.
In the code below, we are creating a new employee object and save it in the database.
Next, we read and get the first employee from the database. Of course, it’s a new database therefore, we’re expecting just one entry in the database. Then, we’re updating the employee first name and the age. Last, we’re deleting that employee.

using System;
using System.Linq;

namespace SQLiteAspNetCoreDemo
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var db = new SQLiteDBContext())
            {
                // Create
                Console.WriteLine("Add New Employee: ");
                db.Employees.Add(new Employee { FirstName = "John", LastName = "Doe", Age = 55 });
                db.SaveChanges();

                Console.WriteLine("Employee has been added sucessfully.");

                // Read
                Console.WriteLine("Querying table for that employee.");
                var employee = db.Employees
                    .OrderBy(b => b.Id)
                    .First();

                Console.WriteLine("The employee found: {0} {1} and is {2} years old.", employee.FirstName, employee.LastName, employee.Age);

                // Update
                Console.WriteLine("Updating the employee first name and age.");

                employee.FirstName = "Louis";
                employee.Age = 90;

                Console.WriteLine("Newly updated employee is: {0} {1} and is {2} years old.", employee.FirstName, employee.LastName, employee.Age);

                db.SaveChanges();

                // Delete
                Console.WriteLine("Delete the employee.");

                db.Remove(employee);
                db.SaveChanges();
            }
        }
    }
}

Test SQLite in ASP.NET Core 3

Finally, the project was run successfully. Below is the output. In case you run into issue with the SQLite table not found, check this post which might help.