ASP.NET Core 10 EF Core with SQLite: Add Persistent Data to Your Minimal API

This is Lesson 3 in the CodeWithChai ASP.NET Core 10 series. You will replace in-memory state with a small SQLite-backed task API, create an EF Core migration, and verify that records survive a restart. The goal is a working local data-access baseline—not a shortcut around production database planning.

Prerequisites: complete Lesson 2: configuration and dependency injection, and have the .NET 10 SDK available. You can also return to the .NET and ASP.NET Core learning track at any time.

Working outcome

By the end, GET /tasks reads task rows from data/tasks.db, POST /tasks inserts a row, and an EF Core migration describes the schema. SQLite is excellent for a local tutorial and compact services, but provider behavior matters: EF Core’s SQLite provider has documented limitations around some schema changes and idempotent migration scripts.

1. Add the EF Core provider and tooling

From the API project directory, add the SQLite provider and design-time package. Install the EF command-line tool if it is not already available. Keep EF Core package versions aligned with the major version used by the project.

dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 10.*
dotnet add package Microsoft.EntityFrameworkCore.Design --version 10.*
dotnet tool install --global dotnet-ef --version 10.*

If dotnet-ef is already installed, use dotnet tool update --global dotnet-ef --version 10.* instead. Confirm the tool can run before moving on:

dotnet ef --version

2. Define the entity and DbContext

Create a Data directory and add this file as Data/TasksDbContext.cs. The entity deliberately uses a simple integer key and UTC timestamp so the example stays focused on data access.

using Microsoft.EntityFrameworkCore;

namespace TasksApi.Data;

public sealed class TaskItem
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public bool IsComplete { get; set; }
    public DateTime CreatedUtc { get; set; }
}

public sealed class TasksDbContext(DbContextOptions<TasksDbContext> options)
    : DbContext(options)
{
    public DbSet<TaskItem> Tasks => Set<TaskItem>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TaskItem>(entity =>
        {
            entity.Property(task => task.Title).HasMaxLength(200).IsRequired();
            entity.Property(task => task.CreatedUtc).IsRequired();
        });
    }
}

The context is registered as scoped in a web application, so each request gets a context lifetime appropriate for a unit of work. Do not store a DbContext in a singleton or share one concurrently across requests.

3. Put the connection string in configuration

Add a connection string to appsettings.json. A relative SQLite data source is resolved from the application’s working directory, so this example stores the database in a local data directory.

{
  "ConnectionStrings": {
    "Tasks": "Data Source=data/tasks.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  }
}

Create the directory before running the migration:

mkdir data

4. Register EF Core and map endpoints

Replace the relevant parts of Program.cs with the following. The request DTO stops callers from setting server-managed fields such as the key and creation time. AsNoTracking is appropriate for the read-only list query because this endpoint does not update returned entities.

using Microsoft.EntityFrameworkCore;
using TasksApi.Data;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<TasksDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("Tasks")
        ?? throw new InvalidOperationException("Connection string 'Tasks' was not found.")));

var app = builder.Build();

app.MapGet("/tasks", async (TasksDbContext db, CancellationToken cancellationToken) =>
{
    var tasks = await db.Tasks
        .AsNoTracking()
        .OrderBy(task => task.Id)
        .Select(task => new { task.Id, task.Title, task.IsComplete, task.CreatedUtc })
        .ToListAsync(cancellationToken);

    return Results.Ok(tasks);
});

app.MapPost("/tasks", async (CreateTaskRequest request, TasksDbContext db,
    CancellationToken cancellationToken) =>
{
    if (string.IsNullOrWhiteSpace(request.Title) || request.Title.Length > 200)
    {
        return Results.ValidationProblem(new Dictionary<string, string[]>
        {
            ["title"] = ["Title is required and must be 200 characters or fewer."]
        });
    }

    var task = new TaskItem
    {
        Title = request.Title.Trim(),
        CreatedUtc = DateTime.UtcNow
    };

    db.Tasks.Add(task);
    await db.SaveChangesAsync(cancellationToken);
    return Results.Created($"/tasks/{task.Id}", task);
});

app.Run();

public sealed record CreateTaskRequest(string Title);

5. Create and apply the migration

Migrations are source-controlled descriptions of schema changes. Create the initial migration, apply it to the local database, then inspect the generated Migrations folder before committing it.

dotnet ef migrations add InitialCreate
dotnet ef database update

EF tooling executes application code to construct the context, so configuration errors can affect design-time commands. If the context lives in a separate project, use the tool’s --project and --startup-project options rather than copying configuration around.

6. Validate persistence

Start the API, create a task, list tasks, stop it, and start it again. The final GET should still return the inserted row.

dotnet run
curl -X POST http://localhost:5000/tasks \
  -H "Content-Type: application/json" \
  -d '{"title":"Learn EF Core migrations"}'
curl http://localhost:5000/tasks

Use the URL shown by dotnet run if your development port differs from the example.

Common mistakes

• Calling EnsureCreated alongside migrations: choose migrations for an evolving relational schema; do not mix the approaches casually.
• Applying migrations automatically from every production instance: coordinate schema deployment deliberately. EF Core’s migration documentation explains the operational trade-offs.
• Assuming SQLite behaves like every server database: SQLite has provider-specific migration and type limitations. Rehearse the move to PostgreSQL or SQL Server if that is the intended production target.
• Tracking read-only results by default: use AsNoTracking where it fits, but keep tracking for edits that you intend to save.
• Keeping a DbContext beyond its request scope: create and dispose it through the registered scoped lifetime.

Production checks

• Commit migration files and review generated SQL or migration operations in pull requests.
• Run dotnet ef migrations has-pending-model-changes in CI before deployment.
• Back up real databases and test migration execution against a production-like copy.
• Use managed secrets/runtime configuration for production connection strings.
• Pick the database provider intentionally; do not treat local SQLite behavior as proof of server-database behavior.

Next lesson

Next, we will add authentication and authorization so the API can identify callers and apply access rules before exposing data-changing endpoints.

Sources

• Microsoft Learn: Use a database with Minimal API, Entity Framework Core, and ASP.NET Core
• Microsoft Learn: Managing migrations
• Microsoft Learn: SQLite provider limitations
• Microsoft Learn: Applying migrations