Tutorials / Build a REST API with C# and .NET · Lesson 17 of 17

On this page

A Data Project and the First Table with EF Core

Last lesson the three books were typed into the code. Restart the app and nothing is saved, because there is nowhere to save anything. Lesson 10 showed a SQLite file with a Books table. This lesson connects the two.

Our goal is small: turn a C# class into a real table in a SQLite file, without writing CREATE TABLE ourselves. We stop when the table exists and you have seen it. Reading and saving books through the API comes in the next lessons.

Here is the route:

Create a second project, BookShop.Data, that will hold everything about the database, and move Book into it.
Meet EF Core, the tool that maps classes to tables, and install it.
Write a DbContext, the class that stands for the database.
Create a migration, a file that describes the change to the database, and read it.
Apply the migration, open the SQLite file and see the table.
Look at the SQL that EF Core wrote for us.

This lesson leaves out reading and writing rows (lessons 17 and 18), seed data, relationships between tables, and moving to a database server (lesson 33). No book is saved yet.

Where we are

This is the solution from lesson 15. We touch these files:

BookShop/
  BookShop.slnx                          <- changes (a new project)
  Directory.Build.props
  Directory.Packages.props               <- changes (two package versions)
  src/
    BookShop.Api/
      BookShop.Api.csproj                <- changes (a reference and a package)
      Program.cs                         <- changes (the database is registered)
      appsettings.json                   <- changes (the connection string)
      Book.cs                            <- DELETED (it moves)
      Controllers/
        BooksController.cs               <- changes (one using line)
    BookShop.Data/                       <- NEW project
      BookShop.Data.csproj               <- NEW
      Book.cs                            <- NEW (moved here, with two new properties)
      BookShopDbContext.cs               <- NEW
      Migrations/                        <- NEW (made by a tool)

Run every command in this lesson from the solution folder, BookShop/, the one that holds BookShop.slnx.

Why a second project

In lesson 01 we planned a solution with several projects, each with one job. The API project receives requests and sends answers. Talking to the database is a different job, so it goes in its own project.

Today the API is the only thing that uses the database, so you could fairly ask: why not keep the database code inside the API project? It would work. Here is the reason we split anyway.

Software outlives its first plan

A system rarely stays the one program it started as. Once the bookshop API works, the shop owner starts asking for things:

A nightly job that runs at the end of each day, counts the sales and writes the accounting figures. It is a program with no web page and no requests, but it needs the same Books table.
An import tool that reads a supplier's catalogue file and adds the new titles.
A second app, such as a back-office tool for staff, next to the public API.

None of these is a web API. If the database code lives inside the API project, each new program has two bad choices. It can copy the code, and then you maintain the same table definition in three places. Or it can reference the whole API project, and drag a web application into a job that has nothing to do with the web.

If the database code already sits in its own project, each new program just adds a reference to it, as the API does now.

The solid boxes exist today. The dashed ones are guesses about the future, drawn so you see the shape. You do not build them now. You only keep the door open.

This is not over-engineering

Over-engineering is building for a future you only imagine. The rule of thumb is to build what you need now, and to avoid pieces that are expensive to add later. This split passes that test:

It is cheap now. One more project, three commands, and the code goes where it would have gone anyway.
It is expensive later. Pulling database code out of a grown API means untangling it from controllers and settings while the system is live.
The need is common, not exotic. Many real systems end up with a scheduled job, an import, or a second app next to the main one. Asking for one is normal and not a sign of a badly planned system.

We stop at two projects on purpose. We do not add a project for every idea (a "domain" project, a "services" project and so on). Each extra project costs reading and wiring time. A tiny app that will never grow can keep everything in one project, and a very large one splits into more layers. Two is a good size for this course.

Two smaller reasons

The arrow goes one way. The API uses the Data project, never the other way round. That keeps web code out of the data code.
Tests can use it alone. A test project (lesson 17) can reference the Data project without starting a web app.

Create the project

Terminal: create a class library, add it to the solution, and make the API use it:

dotnet new classlib -n BookShop.Data -o src/BookShop.Data
dotnet sln add src/BookShop.Data
dotnet add src/BookShop.Api reference src/BookShop.Data

Visual Studio: right-click the solution in Solution Explorer and choose Add, then New Project. Pick Class Library (C#), name it BookShop.Data and set the location to the src folder. Then right-click Dependencies under BookShop.Api, choose Add Project Reference and tick BookShop.Data.

Both routes give the same result. The three commands do this:

dotnet new classlib makes a class library, a project that holds code but does not run by itself (lesson 02).
dotnet sln add lists the project in BookShop.slnx.
dotnet add ... reference writes a ProjectReference into the API's project file. Now the API can use the classes in the Data project.

The template also made a Class1.cs. Delete it. Then open BookShop.Data.csproj. It repeats three settings that Directory.Build.props already gives every project (lesson 14). Remove the PropertyGroup, so the whole file is:

<Project Sdk="Microsoft.NET.Sdk">

</Project>

We add the package line in a moment.

What EF Core is

Lesson 10 made a table by writing SQL. Doing that for every table, and then writing more SQL to read and save every row, gets long. Worse, the C# code and the SQL must always agree: if a column is renamed in one, someone must remember the other.

An ORM (object-relational mapper) takes that job. You describe your data as C# classes, and the ORM turns them into tables and turns your C# code into SQL. Entity Framework Core, called EF Core, is the ORM that Microsoft makes for .NET.

Here is the same idea as a table, with the SQL Server words you already know:

In C#	In the database	In SQL Server terms
A class such as `Book` (an entity)	A table	A table
A property such as `Title`	A column	A column
One `Book` object	A row	A row
A property named `Id`	The primary key	`PRIMARY KEY` (and `IDENTITY`)
`DbContext`	A connection and a unit of work	A connection to one database
A migration	A saved change to the database	A versioned `ALTER TABLE` script

An entity is simply a class that EF Core stores in a table. Book is our first entity.

💡 EF Core is one choice. You can write SQL by hand with the plain ADO.NET classes, or use a small helper such as Dapper that runs your SQL and fills your objects. Both are real, and teams use them. This course uses EF Core because it is the standard Microsoft tool, and because it creates the table and keeps it in step with the class.

Install EF Core

EF Core comes as packages, installed with NuGet like any other (lesson 14). We need two packages and one command-line tool.

What	Where it goes	What it is for
`Microsoft.EntityFrameworkCore.Sqlite`	`BookShop.Data`	EF Core itself, plus the part that speaks to SQLite. It is called the provider
`Microsoft.EntityFrameworkCore.Design`	`BookShop.Api`	Helpers that the `dotnet ef` tool needs while we work. It does not ship with the app
`dotnet-ef`	your computer	The command-line tool that makes and applies migrations

The provider is the part that changes if you move to another database. SQL Server has its own, Microsoft.EntityFrameworkCore.SqlServer. The rest of your code stays the same, which is why lesson 33 can be short.

Terminal:

dotnet add src/BookShop.Data package Microsoft.EntityFrameworkCore.Sqlite
dotnet add src/BookShop.Api package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef

Visual Studio: right-click BookShop.Data, choose Manage NuGet Packages, and install Microsoft.EntityFrameworkCore.Sqlite. Do the same on BookShop.Api for Microsoft.EntityFrameworkCore.Design. The tool has no click path. Open View, then Terminal, and run the third command there.

--global installs the tool once for your whole computer, not just this solution. If the tool is already installed, dotnet tool update --global dotnet-ef brings it to the newest version. If it is older than the packages, it prints a warning like "The Entity Framework tools version is older than that of the runtime". The tool still worked for us, but updating is a good habit.

As in lesson 14, the versions go into Directory.Packages.props. When we wrote this lesson it looked like this. Your numbers may be newer:

<Project>
  <PropertyGroup>
    <ManagePackageVersionsCentrally>true</ManagePackageVersionsCentrally>
  </PropertyGroup>
  <ItemGroup>
    <PackageVersion Include="Microsoft.AspNetCore.OpenApi" Version="10.0.12" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.13">   <!-- new -->
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageVersion>
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.13" />   <!-- new -->
    <PackageVersion Include="Scalar.AspNetCore" Version="2.18.2" />
  </ItemGroup>
</Project>

PrivateAssets says "use this package while building here, but do not pass it on". That is right for a tool helper.

The entity: move `Book`

Book belongs with the data, so it moves to the Data project. It also gets the two columns the bookshop needs from the outline: Stock and Category.

Create Book.cs in src/BookShop.Data/. The whole file is:

namespace BookShop.Data;                      // new

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public string Author { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }            // new
    public string Category { get; set; } = "";   // new
}

Then delete Book.cs from src/BookShop.Api/. It now exists once, in the Data project.

namespace BookShop.Data; is new. In lesson 15 we left the namespace out so the class was easy to use. Now it lives in a project of its own, so it gets a proper name. A namespace is a label on a group of classes, so two classes with the same name in different projects do not clash. Other files say using BookShop.Data; to use Book.
Id is special. EF Core has a rule: a property named Id (or BookId) is the primary key. Because it is a number, EF Core also makes the database fill it in. In SQL Server you would write IDENTITY(1,1). We write nothing.
Category is a plain string for now. A fixed list of categories is neater, and lesson 23 does that. Until then, text is the simplest thing that works.

Lesson 15's controller must now find Book. Open src/BookShop.Api/Controllers/BooksController.cs and add one line at the top. The whole file is:

using BookShop.Data;                          // new
using Microsoft.AspNetCore.Mvc;

[ApiController]
[Route("books")]
public class BooksController : ControllerBase
{
    [HttpGet]
    public List<Book> GetAll()
    {
        var books = new List<Book>
        {
            new Book { Id = 1, Title = "The Pragmatic Programmer", Author = "Andrew Hunt", Price = 39.90m },
            new Book { Id = 2, Title = "Clean Code", Author = "Robert C. Martin", Price = 34.50m },
            new Book { Id = 3, Title = "Refactoring", Author = "Martin Fowler", Price = 44.00m }
        };
        return books;
    }
}

The books still come from the code. We change that in lesson 18.

The DbContext

A DbContext is the class that stands for the database in your code. Think of it as the door to the database. You ask it for a table, and it does the talking.

Create BookShopDbContext.cs in src/BookShop.Data/. The whole file is:

using Microsoft.EntityFrameworkCore;

namespace BookShop.Data;

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

    public DbSet<Book> Books { get; set; }
}

Line by line:

: DbContext means our class is a kind of DbContext, a class from EF Core that already knows how to talk to a database (lesson 08 on classes).
The constructor takes DbContextOptions: the settings, such as which database to open. We do not choose them here. Whoever creates the context passes them in. You know this pattern from lesson 03: the class asks for what it needs, and gets it from outside.
: base(options) hands those settings up to the DbContext underneath.
DbSet<Book> Books is one table. A DbSet<Book> means "the set of all books in the database". The property name, Books, becomes the table name. Add one DbSet for each table you want.

Nothing in this class says "SQLite". That is on purpose. The choice of database is made in one other place.

Choose the database: SQLite

Where does the file live? We say it with a connection string: a short text that tells the app how to reach a database. For SQL Server it holds a server name, a database name and a login. For SQLite it is only a file name.

Open src/BookShop.Api/appsettings.json and add the ConnectionStrings part. The whole file is:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {                       // new
    "BookShop": "Data Source=bookshop.db"      // new
  }
}

Remove the two // new marks when you copy it. JSON does not allow comments, and the marks are only here to point out the new lines. Data Source=bookshop.db means "the file called bookshop.db". A name with no folder is a file next to where the program runs from.

Now we tell the app to use the context. Open src/BookShop.Api/Program.cs. The whole file is:

using BookShop.Data;                                                                          // new
using Microsoft.EntityFrameworkCore;                                                          // new
using Scalar.AspNetCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddOpenApi();

var connectionString = builder.Configuration.GetConnectionString("BookShop");                 // new
builder.Services.AddDbContext<BookShopDbContext>(options => options.UseSqlite(connectionString));   // new

var app = builder.Build();

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

app.UseHttpsRedirection();

app.MapControllers();

app.Run();

Both new lines are in the services part of the four-step picture from lesson 14:

GetConnectionString("BookShop") reads the text we just added to appsettings.json.
AddDbContext<BookShopDbContext>(...) registers the context with DI, as in lesson 03. UseSqlite(connectionString) is the one line that says "this is SQLite". For SQL Server it would say UseSqlServer(...), and that is the only line that changes.

AddDbContext registers the context with a scoped lifetime: one new context for each web request (lesson 07). The next lesson shows why that is the right choice.

Build to check that everything fits:

dotnet build

Build succeeded.
    0 Warning(s)
    0 Error(s)

The first migration

We have a class and a context. The database file has no table yet. Something must create it, and later change it when the class changes.

A migration is a file, written by EF Core, that describes one change to the database. The first one says "create the Books table". If you add a property next week, you create a second migration that says "add this column". Together they are the history of your database, kept in git next to the code. If you know SQL Server projects with numbered .sql scripts, it is the same idea, except EF Core writes the script for you by comparing your classes with what it knew before.

Create the first one. This is a command, and there is no click path in Visual Studio for it, so use the terminal there too (View, then Terminal):

dotnet ef migrations add InitialCreate --project src/BookShop.Data --startup-project src/BookShop.Api

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

Read the command in parts:

dotnet ef migrations add InitialCreate makes a migration and names it InitialCreate. Pick a name that says what changed.
--project src/BookShop.Data is where to put the migration files: the project that holds the context.
--startup-project src/BookShop.Api is the project to start up, to learn the connection string and the settings. The tool runs Program.cs's setup to find the context. That is why the Design package is in the API.

A new folder appeared in the Data project. Your timestamp will differ:

src/BookShop.Data/
  Migrations/
    20261021104153_InitialCreate.cs
    20261021104153_InitialCreate.Designer.cs
    BookShopDbContextModelSnapshot.cs

Open ..._InitialCreate.cs, the one file you can read like a story. The part that matters is the Up method:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Books",
        columns: table => new
        {
            Id = table.Column<int>(type: "INTEGER", nullable: false)
                .Annotation("Sqlite:Autoincrement", true),
            Title = table.Column<string>(type: "TEXT", nullable: false),
            Author = table.Column<string>(type: "TEXT", nullable: false),
            Price = table.Column<decimal>(type: "TEXT", nullable: false),
            Stock = table.Column<int>(type: "INTEGER", nullable: false),
            Category = table.Column<string>(type: "TEXT", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Books", x => x.Id);
        });
}

It reads like the class turned into a table: one CreateTable, one column for each property, Id as the primary key that counts up by itself. The Down method below it does the reverse (drop the table), so a migration can be undone.

The other two files are for EF Core. The Designer file and the snapshot record what the model looked like, so the next migration can compare and find only what changed. You do not edit them.

Two things in the output of Up may surprise you if you know SQL Server:

Price is TEXT. SQLite has no real decimal type. It stores a number as an integer, a floating-point number, or text. EF Core chooses text for decimal, so no cents are lost to rounding. SQL Server would give decimal(18,2). This is fine for this course. It is one of the differences you meet when you change database (lesson 33).
Every column is nullable: false. Because we gave the text properties a default "" and turned on Nullable (lesson 14), EF Core knows a title is never missing. The columns are NOT NULL, like you would write by hand.

Apply it, and look

Making the migration only wrote C# files. The database has not changed. Applying the migration runs it against the database:

dotnet ef database update --project src/BookShop.Data --startup-project src/BookShop.Api

The tool prints a lot of lines, because it logs every SQL command it sends. Near the end you should see the line Applying migration '20261021104153_InitialCreate'. and then Done..

Now look for the file. It was created next to the API project's files:

src/BookShop.Api/bookshop.db

Open it in DB Browser for SQLite, as in lesson 10: File, Open Database, and pick the file. On the Database Structure tab you will see three tables:

Table	What it is
`Books`	Our table, with the six columns from the class
`__EFMigrationsHistory`	EF Core's own notebook. One row for each migration that has run, so it never runs one twice
`__EFMigrationsLock`	A small table EF Core uses so that two copies of the tool cannot change the database at the same moment

Open Browse Data and choose Books. It is empty, which is right: nothing has saved a book yet. Look at __EFMigrationsHistory instead. It has one row, 20261021104153_InitialCreate, and the EF Core version.

⚠️ database update creates the .db file if it does not exist. A relative name such as bookshop.db is looked up from the folder the program runs in, so the file can end up in a different place when you start the app another way. If you find two bookshop.db files, that is why. Keep one, and we will pin the location down when it matters.

💡 A database file is data, not code. Add *.db to your .gitignore so you do not commit it.

The SQL behind it

You never wrote CREATE TABLE, so what did EF Core send? Ask it. This prints the SQL for every migration without running it:

dotnet ef migrations script --project src/BookShop.Data --startup-project src/BookShop.Api

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
    "ProductVersion" TEXT NOT NULL
);

BEGIN TRANSACTION;
CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "Author" TEXT NOT NULL,
    "Price" TEXT NOT NULL,
    "Stock" INTEGER NOT NULL,
    "Category" TEXT NOT NULL
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20261021104153_InitialCreate', '10.0.13');

COMMIT;

This is SQL you can read. Compare it with the CREATE TABLE you wrote in lesson 10:

PRIMARY KEY AUTOINCREMENT is SQLite's version of IDENTITY(1,1).
The whole change runs inside a transaction (BEGIN TRANSACTION ... COMMIT), so it either happens fully or not at all.
The last INSERT writes the line in the history table.

This is why the SQL is worth knowing even when a tool writes it. It is how you check what the tool is about to do, and it is the same script you could hand to a database administrator for a SQL Server in production.

Finally, run the app and check that moving Book broke nothing:

dotnet run --project src/BookShop.Api

curl http://localhost:5155/books

[{"id":1,"title":"The Pragmatic Programmer","author":"Andrew Hunt","price":39.90,"stock":0,"category":""},{"id":2,"title":"Clean Code","author":"Robert C. Martin","price":34.50,"stock":0,"category":""},{"id":3,"title":"Refactoring","author":"Martin Fowler","price":44.00,"stock":0,"category":""}]

The books still come from the code, so the new stock and category show their defaults, 0 and an empty text. Your port may differ, as in lesson 15. The books will come from the table in lesson 18.

Where we ended

Here is the solution now. Compare it with yours. Book.cs moved from the API project to the Data project, and the Data project is new:

BookShop/
  BookShop.slnx                          the Data project is listed
  Directory.Build.props
  Directory.Packages.props               EF Core versions added
  src/
    BookShop.Api/
      BookShop.Api.csproj                references BookShop.Data, has the Design package
      Program.cs                         registers the database
      appsettings.json                   has the connection string
      bookshop.db                        the SQLite file (not committed)
      Controllers/
        BooksController.cs               has `using BookShop.Data;`
    BookShop.Data/
      BookShop.Data.csproj               has the Sqlite package
      Book.cs                            moved here from BookShop.Api
      BookShopDbContext.cs
      Migrations/
        <timestamp>_InitialCreate.cs
        <timestamp>_InitialCreate.Designer.cs
        BookShopDbContextModelSnapshot.cs

Recap

A second project, BookShop.Data, holds everything about the database. The API uses it, never the other way round.
An ORM maps classes to tables. EF Core is Microsoft's. A class is a table, a property is a column, an object is a row.
An entity is a class stored in a table. A property named Id becomes the primary key.
A DbContext is the door to the database. Each DbSet<T> on it is one table.
The provider (here Sqlite) and the connection string say which database. Only UseSqlite(...) is specific to SQLite.
A migration is a saved change to the database. migrations add writes it, database update runs it. Do not edit an old one, add a new one.
migrations script shows the real SQL, and __EFMigrationsHistory records what has run.

Questions to think about

These have no answers here, on purpose. Try them from memory first.

In your own words, what does an ORM do, and what does EF Core's DbSet<Book> stand for in the database?
In the mapping, what is a class, a property and an object?
You add public string Publisher { get; set; } = ""; to Book. What must you do before the database has that column, and why not add the column by hand in DB Browser?
Why does the Book class live in the Data project, and not in the API project?
How would you find out which SQL EF Core is going to run, before it runs?

Next lesson: the Books service. We put the code that works with books in a class of its own, register it with DI, and write our first unit test.