EF Core Basics

Earlier, Todos were stored in memory and disappeared whenever the service restarted. In this chapter, we use a SQLite database for storage and Entity Framework Core (EF Core) to read and write data as C# objects, generating SQL for us. Tools like this are called object-relational mappers (ORMs).

The handlers below receive a database context directly instead of using ITodoStore; input validation and error handling follow the earlier examples. The complete code is split across three files:

15-efcore-basics/Program.cs

using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.EntityFrameworkCore;
using Scalar.AspNetCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddOpenApi();
builder.Services.AddValidation();
builder.Services.AddProblemDetails();
var connectionString = builder.Configuration.GetConnectionString("Todos")
    ?? throw new InvalidOperationException("Missing ConnectionStrings:Todos configuration.");
builder.Services.AddDbContext<TodoDbContext>(options => options.UseSqlite(connectionString));

var app = builder.Build();

app.UseExceptionHandler();
app.UseStatusCodePages();

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
    app.MapScalarApiReference();
}

// For standalone learning samples only; EnsureCreatedAsync does not update existing tables.
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<TodoDbContext>();
    await db.Database.EnsureCreatedAsync();
}

app.MapGet("/todos", async (TodoDbContext db) =>
    await db.Todos.AsNoTracking().OrderBy(t => t.Id).ToListAsync());

app.MapGet("/todos/{id:int}", async Task<Results<Ok<Todo>, NotFound>> (int id, TodoDbContext db) =>
{
    var todo = await db.Todos.FindAsync(id);
    return todo is null ? TypedResults.NotFound() : TypedResults.Ok(todo);
});

app.MapPost("/todos", async Task<Created<Todo>> (CreateTodo input, TodoDbContext db) =>
{
    var todo = new Todo { Title = input.Title };
    db.Todos.Add(todo);
    await db.SaveChangesAsync();
    return TypedResults.Created($"/todos/{todo.Id}", todo);
});

app.Run();

15-efcore-basics/Models.cs

using System.ComponentModel.DataAnnotations;

public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool Done { get; set; }
}

public record CreateTodo([Required, StringLength(100)] string Title);

15-efcore-basics/TodoDbContext.cs

using Microsoft.EntityFrameworkCore;

public class TodoDbContext(DbContextOptions<TodoDbContext> options) : DbContext(options)
{
    public DbSet<Todo> Todos => Set<Todo>();
}

Run and verify

Open a terminal at the repository root:

cd samples/15-efcore-basics
dotnet run

The project file declares the SQLite provider. dotnet run restores dependencies first, so there is no need to install a separate SQLite service:

15-efcore-basics/EfCoreBasics.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="10.0.12" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.12" />
    <PackageReference Include="Scalar.AspNetCore" Version="2.17.10" />
  </ItemGroup>
</Project>

The database location comes from the configuration file:

15-efcore-basics/appsettings.json

{
  "ConnectionStrings": {
    "Todos": "Data Source=todos-15.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.EntityFrameworkCore.Database.Command": "Warning"
    }
  },
  "AllowedHosts": "*"
}

On first startup, the application creates todos-15.db in the project directory. The output below assumes a new database. Open another terminal and query first, then create a Todo:

curl http://localhost:5080/todos

[]

curl -i -X POST http://localhost:5080/todos -H "Content-Type: application/json" -d '{"title":"Buy milk"}'

Response excerpt (common headers such as dates are omitted):

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8
Location: /todos/1

{"id":1,"title":"Buy milk","done":false}

Return to the service terminal, press Ctrl+C, and run dotnet run again. Then retrieve the Todo:

curl http://localhost:5080/todos/1

{"id":1,"title":"Buy milk","done":false}

The Todo is still there after restart, which shows that the data was written to the SQLite file.

Tip

Each chapter uses its own database file. Repeating an exercise keeps existing data and identifiers. To use a new, empty database, stop the service and run dotnet run -- --ConnectionStrings:Todos="Data Source=practice-15.db", choosing a filename that does not exist yet; there is no need to delete the old data.

Entity: a row in the database

Todo in Models.cs is an entity that corresponds to a row in a table. By convention, EF Core recognizes Id as the primary key. In this example, the database generates the integer key when a row is inserted, so we no longer need _nextId.

Why is the entity a class while the request is still a record? EF Core tracks specific entity instances and observes property changes during updates, so an ordinary mutable class fits that work. CreateTodo describes the fields a client may submit and remains a concise record. The client cannot use this request model to set Id or Done.

The = "" after Title is an initial property value that avoids an uninitialized non-null string when an object is created. [Required], [StringLength], and AddValidation() on CreateTodo still check whether the input is valid.

Read and write data with DbContext

TodoDbContext inherits from DbContext, the database context. Its DbSet<Todo> is the entry point for querying and writing Todos; it is not a List<Todo> that loads the entire table into memory in advance.

AddDbContext registers the context with the dependency injection container as Scoped by default: the same instance is used within a web request, and the container disposes it when the request ends. UseSqlite selects the database provider, and the connection string specifies the file location.

Note

DbContext is not thread-safe. Do not register it as a Singleton or run multiple queries concurrently on the same instance. Await the current operation before starting the next one. Official lifetime guidance

There is no request scope during startup, so line 26 creates a scope manually and gets a context to initialize the database. using disposes the scope and the services it contains when execution leaves the block.

Querying and saving are separate actions

In GET /todos, OrderBy specifies the result order, and ToListAsync() is what executes the database query and retrieves the list. Without sorting, do not rely on the database to return rows in an order that happens to look consistent. AsNoTracking() means these read-only results do not need change tracking, which reduces the state the context must maintain.

FindAsync(id) looks up one Todo by primary key. If the current context is already tracking it, the method can return it directly; otherwise, it queries the database. If nothing is found, it returns null and the handler responds with 404.

The three POST steps are worth distinguishing:

1. new Todo creates a C# object, but does not write it to the database.
2. db.Todos.Add(todo) marks it for insertion.
3. await db.SaveChangesAsync() performs the insert. Only afterward does todo.Id contain the identifier generated by the database.

Why does Add not save immediately? The context can collect a group of changes and save them together. If you forget SaveChangesAsync(), the object exists in memory, but no row is added to the database.

Task<Created<Todo>> means that after the asynchronous method finishes, it returns a Created<Todo> result. The syntax for async / await is covered in C# Tour.

Technical detail

This example consistently uses EF Core's asynchronous methods. However, the underlying Microsoft.Data.Sqlite does not support asynchronous I/O, so these calls ultimately run synchronously. Other database providers may behave differently. SQLite asynchronous limitations

Creating tables does not upgrade the schema

EnsureCreatedAsync() is suitable for a standalone learning project: it creates the tables required by the model when the database has no tables, but does not upgrade the schema when tables already exist.

Note

After an entity changes, EnsureCreatedAsync() does not automatically add columns. Use migrations to upgrade an existing database; migrations and EnsureCreated cannot be used together directly. Each chapter in this tutorial uses a separate file. To keep and upgrade existing data, see EF Core migrations.

FastAPI comparison

EF Core has a role similar to SQLAlchemy. DbContext can be compared to a Session within a unit of work, and SaveChangesAsync() writes tracked changes. Their APIs and transaction details are not identical.

Tip

For a SQL-oriented view of queries and saving, see EF Core / LINQ ↔ PostgreSQL cheat sheet, which includes runnable comparisons.

Summary

• EF Core maps entity objects to a database. SQLite stores data in a file, so it remains available after the service restarts.
• AddDbContext registers a Scoped context by default. The same context cannot be used concurrently.
• Queries access the database at execution methods such as ToListAsync(); read-only queries can use AsNoTracking().
• Add marks an entity for insertion. SaveChangesAsync() performs the save, and the database generates the primary key.
• EnsureCreatedAsync() is for this tutorial's standalone examples only; it does not upgrade an existing table schema.

Next: Relations and Queries—add categories to Todos and let the database filter results. Previous: Logging.