SQLite with Entity Framework Core: Setup, Migrations and CRUD (Tested)

Entity Framework Core runs on SQLite with a one-line change of provider, which suits apps that keep their data in a single file. SQLite stores some .NET types differently, though, and its migrations have limits SQL Server doesn’t. In this article I’ll walk you through a small EF Core app on SQLite, from the first migration to the queries, and the SQLite-specific behaviour worth knowing.

TLDR

• Add the Microsoft.EntityFrameworkCore.Sqlite package and call options.UseSqlite("Data Source=app.db").
• Create the database with dotnet ef migrations add InitialCreate and dotnet ef database update.
• The database is one .db file, and the sqlite3 shell can open it.
• EF Core stores decimal as TEXT on SQLite. EF sorts it correctly, but plain SQL on that column sorts it as text.
• Some migration operations need a table rebuild on SQLite, and a few aren’t supported at all.

What is EF Core with SQLite and what will we build?

Entity Framework Core (EF Core) is .NET’s object-relational mapper: you write C# classes and LINQ queries, and EF Core turns them into SQL. Its SQLite provider targets an SQLite database file instead of a server.

To show you how it works, I will build a console app where each Blog has many Post rows. The app writes and reads them through a DbContext.

Prerequisites

Requirement	Version used here
.NET SDK	10.0.401 (mcr.microsoft.com/dotnet/sdk:10.0 Docker image)
sqlite3 shell, to inspect the file	3.45.1 (Debian package in the same container)
Microsoft.EntityFrameworkCore.Sqlite	10.0.12
Microsoft.EntityFrameworkCore.Design	10.0.12, for migrations
dotnet-ef tool	10.0.12

Building the blog app

The app takes four steps, from an empty folder to a running program.

Step 1: Create the project and add the packages

$ dotnet new console -n Blogging -o . 2>&1 | head -1
The template "Console App" was created successfully.
$ dotnet add package Microsoft.EntityFrameworkCore.Sqlite 2>&1 | grep -E "PackageReference for package 'Microsoft.EntityFrameworkCore.Sqlite' version"
info : PackageReference for package 'Microsoft.EntityFrameworkCore.Sqlite' version '10.0.12' added to file '/work/Blogging.csproj'.
$ dotnet add package Microsoft.EntityFrameworkCore.Design 2>&1 | grep -E "PackageReference for package 'Microsoft.EntityFrameworkCore.Design' version"
info : PackageReference for package 'Microsoft.EntityFrameworkCore.Design' version '10.0.12' added to file '/work/Blogging.csproj'.
$ dotnet tool install --global dotnet-ef 2>&1 | tail -1
Tool 'dotnet-ef' (version '10.0.12') was successfully installed.

The project file now references both packages:

$ grep -E 'PackageReference Include|TargetFramework' Blogging.csproj
    <TargetFramework>net10.0</TargetFramework>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.12">
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.12" />

Step 2: Define the entities and the DbContext

UseSqlite takes an ADO.NET connection string. Data Source=blogging.db is a file path relative to the working directory:

using Microsoft.EntityFrameworkCore;

public class Blog
{
    public int Id { get; set; }
    public required string Url { get; set; }
    public List<Post> Posts { get; } = new();
}

public class Post
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public decimal Price { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; } = null!;
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=blogging.db");
}

By convention, Id becomes the primary key and BlogId the foreign key to Blog.

Step 3: Create the database with a migration

$ dotnet ef migrations add InitialCreate 2>&1 | tail -2
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
$ ls Migrations
20261007075216_InitialCreate.Designer.cs
20261007075216_InitialCreate.cs
BloggingContextModelSnapshot.cs
$ dotnet ef database update 2>&1 | tail -1
Done.

EF Core wrote the migration as C# under Migrations/, then created blogging.db. It records applied migrations in __EFMigrationsHistory. The table it created is ordinary SQLite, and the sqlite3 shell can read it:

$ sqlite3 blogging.db '.schema Posts'
CREATE TABLE IF NOT EXISTS "Posts" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Posts" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "Price" TEXT NOT NULL,
    "BlogId" INTEGER NOT NULL,
    CONSTRAINT "FK_Posts_Blogs_BlogId" FOREIGN KEY ("BlogId") REFERENCES "Blogs" ("Id") ON DELETE CASCADE
);
CREATE INDEX "IX_Posts_BlogId" ON "Posts" ("BlogId");
$ sqlite3 blogging.db 'SELECT MigrationId, ProductVersion FROM __EFMigrationsHistory;'
20261007075216_InitialCreate|10.0.12

ON DELETE CASCADE comes from EF Core’s convention for a required relationship, and the index on BlogId speeds up the join. Price is a TEXT column, because SQLite has no decimal type.

Step 4: Write the CRUD code

using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

// Create
var blog = new Blog { Url = "https://example.com/sqlite" };
blog.Posts.Add(new Post { Title = "Hello SQLite", Price = 0m });
blog.Posts.Add(new Post { Title = "Indexes explained", Price = 4.99m });
db.Blogs.Add(blog);
db.SaveChanges();
Console.WriteLine($"Created blog {blog.Id} with {blog.Posts.Count} posts");

// Read with LINQ
var titles = db.Posts
    .Where(p => p.Blog.Url.Contains("sqlite"))
    .OrderBy(p => p.Title)
    .Select(p => p.Title)
    .ToList();
Console.WriteLine("Titles: " + string.Join(", ", titles));
Console.WriteLine("SQL: " + db.Posts.Where(p => p.BlogId == 1).OrderBy(p => p.Title).ToQueryString().Replace("\n", " "));

// Update
var first = db.Posts.Single(p => p.Title == "Hello SQLite");
first.Title = "Hello, SQLite";
db.SaveChanges();
Console.WriteLine("Renamed: " + db.Posts.Count(p => p.Title == "Hello, SQLite"));

// Delete (posts go with the blog: cascade delete)
db.Blogs.Remove(blog);
db.SaveChanges();
Console.WriteLine($"Blogs left: {db.Blogs.Count()}, posts left: {db.Posts.Count()}");

// decimal is stored as TEXT on SQLite. EF Core still sorts it as a number:
var shop = new Blog { Url = "https://example.com/shop" };
foreach (var price in new[] { 9.99m, 10.50m, 2.00m })
    shop.Posts.Add(new Post { Title = $"Item {price}", Price = price });
db.Blogs.Add(shop);
db.SaveChanges();
Console.WriteLine("EF OrderBy(Price): " + string.Join(", ", db.Posts.OrderBy(p => p.Price).Select(p => p.Price).ToList()));
Console.WriteLine("SQL: " + db.Posts.OrderBy(p => p.Price).ToQueryString().Replace("\n", " "));

Running it

$ dotnet run 2>&1
Created blog 1 with 2 posts
Titles: Hello SQLite, Indexes explained
SQL: SELECT "p"."Id", "p"."BlogId", "p"."Price", "p"."Title" FROM "Posts" AS "p" WHERE "p"."BlogId" = 1 ORDER BY "p"."Title"
Renamed: 1
Blogs left: 0, posts left: 0
EF OrderBy(Price): 2.0, 9.99, 10.5
SQL: SELECT "p"."Id", "p"."BlogId", "p"."Price", "p"."Title" FROM "Posts" AS "p" ORDER BY "p"."Price" COLLATE EF_DECIMAL

What each line shows:

• Create: one SaveChanges() inserted the blog and both posts.
• Read: the LINQ query became SQL, which ToQueryString() prints.
• Update: changing a tracked entity and calling SaveChanges() sent an UPDATE.
• Delete: removing the blog removed its posts too, through the cascade.
• Decimal: EF Core sorted the prices as numbers, using a collation named EF_DECIMAL in the SQL.

Outside EF Core, the same column sorts as text, so “10.5” comes before “2.0”:

$ sqlite3 blogging.db 'SELECT Price, typeof(Price) FROM Posts ORDER BY Price;'
10.5|text
2.0|text
9.99|text

Common problems

These come from the EF Core SQLite limitations page, except the decimal test above and database is locked, which wasn’t reproduced here.

• Types SQLite doesn’t support natively: the docs list four, decimal among them. EF Core reads and writes them, and the docs say comparison and ordering “will require evaluation on the client”. In the test above, EF Core 10.0.12 still ordered decimal in SQL. When you don’t need decimal precision, the docs suggest double, with a value converter if you want to keep decimal in your classes.
• Migrations that need a rebuild: operations such as AlterColumn and DropColumn are done by rebuilding the table. That only works for tables in your EF Core model. Otherwise EF throws NotSupportedException.
• Schemas and sequences: the SQLite provider doesn’t support them.
• Idempotent migration scripts: dotnet ef migrations script --idempotent isn’t possible on SQLite, because it has no procedural language. Use dotnet ef database update, or script from a known migration.
• database is locked: another connection is writing. See database is locked.

Conclusion

EF Core on SQLite works like EF Core anywhere else, with the same DbContext and LINQ. Check two things before you ship. Prefer double over decimal if other tools will read the file with plain SQL, and expect some schema changes to rebuild tables.

Further reading

• C# SQLite
• SQLite vs SQL Server
• SQLite foreign keys
• SQLite data types
• Microsoft docs: EF Core SQLite provider, SQLite provider limitations

Tested with .NET SDK 10.0.401 and EF Core 10.0.12 (Microsoft.EntityFrameworkCore.Sqlite) on 7 October 2026.