EF Core and SQLite in ASP.NET Core Web API: DbContext, the First Migration and CRUD

Version note: The code in this post is written for .NET 6 and runs unchanged on .NET 10.

Guess First

By the end of this post products will live in a SQLite database and every product will belong to a category. The database only has categories 1 and 2. What happens if you send this request?

curl -i -X POST http://localhost:5080/api/products -H "Content-Type: application/json" -d '{"name":"Ghost","price":10,"categoryId":99}'

[ApiController] will bind the body without trouble, because categoryId is a valid number. Does it return 201, 400, 404, or something else entirely? The answer is in the CRUD section.

Where EF Core Sits in an API

So far products lived in an in-memory list, and everything we added was gone each time the app restarted. Entity Framework Core is .NET's ORM: it turns C# classes into tables and LINQ queries into SQL. In this post we look at EF Core through the eyes of an API developer: registering the DbContext, providing a connection string, running the first migration and wiring CRUD into the controller. Topics such as relationships, loading strategies, the internals of the change tracker and performance are covered in detail in the separate EF Core series.

We picked SQLite as the database because it needs no installation; the database is a single file in the project folder. Anyone who clones the code can run it without setting up a server. Switching providers is as easy as replacing the single line you will see shortly with UseSqlServer or UseNpgsql.

Packages and Matching Versions

The major version of the EF Core packages should match the .NET version you target. For a net6.0 project, the last patch of EF Core 6 is 6.0.36:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 6.0.36
dotnet add package Microsoft.EntityFrameworkCore.Design --version 6.0.36

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <RootNamespace>ProductCatalog</RootNamespace>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.36">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.36" />
  </ItemGroup>
</Project>

When you move the project to .NET 10, changing TargetFramework to net10.0 and both packages to 10.0.14 is all it takes; not a single line of the C# code in this post changes, and I built and ran both. The Sqlite package brings in the provider and EF Core itself. The Design package contains the tooling needed to generate migrations and, thanks to PrivateAssets, does not flow into the app's output.

You also need the dotnet-ef tool for migration commands. Keep its version the same as the packages:

dotnet tool install --global dotnet-ef --version 6.0.36

If another version is already installed on your machine, use update instead of install. If you want a project-specific version, you can create a local tool manifest with dotnet new tool-manifest and install without --global; that is what I did to try both versions side by side.

Entities and AppDbContext

A product now belongs to a category:

namespace ProductCatalog.Models;

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Product> Products { get; set; } = new();
}

namespace ProductCatalog.Models;

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public Category? Category { get; set; }
}

EF Core works with conventions. A property named Id becomes the primary key, and CategoryId together with the Category navigation property describes the foreign key relationship. We made Category nullable; otherwise [ApiController] would treat the category object itself as required in the POST body. We will talk in the next post about how this detail is tied to exposing the entity directly through the API.

using Microsoft.EntityFrameworkCore;
using ProductCatalog.Models;

namespace ProductCatalog.Data;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .Property(p => p.Name)
            .HasMaxLength(100);

        // Seed data is written into the migration
        modelBuilder.Entity<Category>().HasData(
            new Category { Id = 1, Name = "Keyboards" },
            new Category { Id = 2, Name = "Mice" });

        modelBuilder.Entity<Product>().HasData(
            new Product { Id = 1, Name = "Mechanical Keyboard", Price = 1450m, CategoryId = 1 },
            new Product { Id = 2, Name = "Wireless Mouse", Price = 520m, CategoryId = 2 });
    }
}

A DbContext represents a session with the database. The DbSet<T> properties are the doors into the tables, and OnModelCreating is where you configure the model by hand when conventions are not enough. The seed data we provide with HasData will turn into a migration.

Registering the DbContext and the Connection String

The connection string comes from configuration. ConnectionStrings is a special section, and GetConnectionString reads it directly:

{
  "ConnectionStrings": {
    "Catalog": "Data Source=catalog.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

using Microsoft.EntityFrameworkCore;
using ProductCatalog.Data;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("Catalog")));

var app = builder.Build();

app.MapControllers();

app.Run();

AddDbContext registers AppDbContext as scoped. In the dependency injection post we said "we will see why in the data layer," and here is why: a DbContext is a unit of work. Its change tracker follows the entities you read and modify during that request, and SaveChanges writes them to the database in one go. Sharing that state between two requests is both wrong and dangerous, because DbContext is not thread-safe. Every request gets its own DbContext, and when the request ends the scope closes and the DbContext is disposed. EF Core 6 even shows this in the log; this line appears once per request:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.36 initialized 'AppDbContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite:6.0.36' with options: None

The First Migration

The model is ready, but the database does not exist yet. A migration is a C# file that turns a change in your model into steps to apply to the database. In the project folder:

dotnet ef migrations add InitialCreate
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'

Three files were created in the Migrations folder: 20261103213548_InitialCreate.cs, its .Designer.cs companion, and AppDbContextModelSnapshot.cs, which holds the latest state of the model. A piece of the migration's Up method:

migrationBuilder.CreateTable(
    name: "Products",
    columns: table => new
    {
        Id = table.Column<int>(type: "INTEGER", nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
        Name = table.Column<string>(type: "TEXT", maxLength: 100, nullable: false),
        Price = table.Column<decimal>(type: "TEXT", nullable: false),
        CategoryId = table.Column<int>(type: "INTEGER", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Products", x => x.Id);
        table.ForeignKey(
            name: "FK_Products_Categories_CategoryId",
            column: x => x.CategoryId,
            principalTable: "Categories",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

Think of a migration as the drawing for a home renovation; the drawing is on the table, but no wall has come down yet. To apply it:

dotnet ef database update

The command creates the catalog.db file, builds the tables, inserts the seed data and records the applied migration in the __EFMigrationsHistory table. The last lines of the output:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
      VALUES ('20261103213548_InitialCreate', '6.0.36');
Done.

With EF Core 10 the same commands produce the same tables; in the migration file the seed data is grouped into a single InsertData call, and 10.0.14 is written to the history table. Migrations go into source control, because they are the history of your database.

Wiring CRUD into the Controller

The DbContext takes the place of the in-memory store. Every call to the database is I/O, so we use the async methods; we will cover the details of async and CancellationToken in a separate post.

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ProductCatalog.Data;
using ProductCatalog.Models;

namespace ProductCatalog.Controllers;

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly AppDbContext _db;

    public ProductsController(AppDbContext db)
    {
        _db = db;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<Product>>> GetAll()
    {
        // We only read, so we spare the change tracker the work
        List<Product> products = await _db.Products.AsNoTracking().ToListAsync();
        return products;
    }

    [HttpGet("{id:int}")]
    public async Task<ActionResult<Product>> GetById(int id)
    {
        Product? product = await _db.Products.FindAsync(id);
        if (product is null)
        {
            return NotFound();
        }

        return product;
    }

    [HttpPost]
    public async Task<ActionResult<Product>> Create(Product product)
    {
        _db.Products.Add(product);
        await _db.SaveChangesAsync();
        return CreatedAtAction(nameof(GetById), new { id = product.Id }, product);
    }

    [HttpPut("{id:int}")]
    public async Task<IActionResult> Update(int id, Product input)
    {
        Product? product = await _db.Products.FindAsync(id);
        if (product is null)
        {
            return NotFound();
        }

        product.Name = input.Name;
        product.Price = input.Price;
        product.CategoryId = input.CategoryId;
        await _db.SaveChangesAsync();
        return NoContent();
    }

    [HttpDelete("{id:int}")]
    public async Task<IActionResult> Delete(int id)
    {
        Product? product = await _db.Products.FindAsync(id);
        if (product is null)
        {
            return NotFound();
        }

        _db.Products.Remove(product);
        await _db.SaveChangesAsync();
        return NoContent();
    }
}

curl http://localhost:5080/api/products
[{"id":1,"name":"Mechanical Keyboard","price":1450.0,"categoryId":1,"category":null},{"id":2,"name":"Wireless Mouse","price":520.0,"categoryId":2,"category":null}]

curl -i -X POST http://localhost:5080/api/products -H "Content-Type: application/json" -d '{"name":"USB-C Hub","price":890,"categoryId":1}'
HTTP/1.1 201 Created
Location: http://localhost:5080/api/Products/3

{"id":3,"name":"USB-C Hub","price":890,"categoryId":1,"category":null}

Add marks the entity as "to be added" in the change tracker; the real work happens in SaveChangesAsync, and the database generates the Id and writes it back to the entity. In Update there is no Update call at all. The entity read with FindAsync is already being tracked; changing its properties is enough, and EF Core writes only the columns that changed.

The answer to the guessing question: 500 Internal Server Error. Model binding and validation pass, EF Core sends the INSERT, SQLite rejects the foreign key, and the exception escapes the action:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.

Returning a server error because of a client mistake is wrong; the right thing is to check that the category exists first and return 400. We will turn exceptions into meaningful status codes in the global error handling post.

The SQL in the Console

In the logging post we saw how to filter by category. EF Core logs every command it runs under the Microsoft.EntityFrameworkCore.Database.Command category at Information level. Since Default is Information, these are already in the console:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."CategoryId", "p"."Name", "p"."Price"
      FROM "Products" AS "p"
      WHERE "p"."Id" = @__p_0
      LIMIT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[@p2='?' (DbType = Int32), @p0='?' (Size = 13), @p1='?' (DbType = Decimal)], CommandType='Text', CommandTimeout='30']
      UPDATE "Products" SET "Name" = @p0, "Price" = @p1
      WHERE "Id" = @p2;
      SELECT changes();

This is the trace of the PUT request. CategoryId stayed the same, so it is not in the UPDATE; you are watching the change tracker at work. Parameter values are hidden as ? so that sensitive data does not end up in logs. While developing, follow what EF generates through these lines; in production, raising this category to Warning is usually the right call. On EF Core 10 the same INSERT comes back with a single RETURNING "Id"; the details of the SQL can change from version to version, your code does not.

Common Pitfalls

First, running the app from another folder. Data Source=catalog.db is a relative path and is resolved against the process's working directory. When I started the app from outside the project folder, SQLite created an empty catalog.db there and the first request failed with this:

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: Products'.

Second, putting an Async suffix on the action name. If you name the action GetByIdAsync and write CreatedAtAction(nameof(GetByIdAsync), ...), the route cannot be found, because MVC strips the Async suffix from action names by default. Worse, SaveChangesAsync has already run; the product is written to the database but the client gets this:

System.InvalidOperationException: No route matches the supplied values.

Third, injecting the DbContext into a singleton. This is the most expensive form of the captive dependency from the dependency injection post; in Development the app does not start, in Production every request shares a single DbContext.

Fourth, decimal on SQLite. SQLite has no decimal type, so EF Core stores the value as TEXT; that is why the JSON says 1450.0. On EF Core 6, a query with OrderBy(p => p.Price) is rejected with this error: SQLite does not support expressions of type 'decimal' in ORDER BY clauses. The same query works on EF Core 10. SQL Server and PostgreSQL do not have this problem.

Fifth, running migrations automatically at startup. In the development environment it is a handy shortcut:

if (app.Environment.IsDevelopment())
{
    using (IServiceScope scope = app.Services.CreateScope())
    {
        AppDbContext db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        db.Database.Migrate();
    }
}

Because DbContext is scoped, we resolved it from the scope we opened, not from the root provider. In production, though, several instances may try to run migrations at the same time; make migrations a separate step of your deployment process there.

A Small Experiment

★☆☆ Add an int property called Stock to Product, create a new migration called AddStock and apply it. Read the AddColumn call in the migration file, then look at the list with dotnet ef migrations list.

★★☆ In the Create action, check the category with _db.Categories.AnyAsync(c => c.Id == product.CategoryId) before SaveChangesAsync, and return BadRequest if it does not exist. Send the request from the guessing question again.

★★★ Add Include(p => p.Category) to GetAll and send a request to /api/products. What happened? Read the error message and try to explain the cause from how the entities are linked to each other.

The problem you saw in the third experiment is no accident. Exposing entities directly through the API opens the door to a series of problems, from circular references to clients being able to change fields they should not touch. In the next post we will separate the API contract from the database model with request and response DTOs, and compare doing the mapping by hand and with AutoMapper.