Skip to content

Expert · Web APIs with ASP.NET Core and EF Core · topic 35 of 43 · 40 min

Entity Framework Core: databases with C#

Explanation

Entity Framework Core (EF Core) is an ORM: it maps your C# classes to database tables, so you query with LINQ and save with SaveChangesAsync instead of writing most SQL by hand. A DbContext represents a session with the database, and each DbSet<T> is a table. Migrations turn changes in your classes into database schema changes. Watch for the N+1 problem and load related data with Include or projections.

What is an ORM?

Databases store data in tables of rows and columns; C# works with objects. An ORM (Object-Relational Mapper) translates between the two. With EF Core you write db.Products.Where(p => p.Stock == 0) and EF Core generates the SQL, runs it, and turns the rows back into Product objects. You still need to understand SQL to use it well, but you write much less of it.

Setting up

bash
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef
A provider package for your database (Sqlite here; SqlServer and Npgsql for PostgreSQL are common), the Design package for migrations, and the dotnet-ef command-line tool.
csharp
using Microsoft.EntityFrameworkCore;

public class Product
{
    public int Id { get; set; }                  // primary key by convention
    public string Name { get; set; } = "";
    public string Category { get; set; } = "";
    public int Stock { get; set; }
    public decimal Price { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
}
An entity is a class mapped to a table. A property named Id (or ProductId) becomes the primary key.
csharp
builder.Services.AddDbContext<ShopDb>(o =>
    o.UseSqlite(builder.Configuration.GetConnectionString("Shop")));
AddDbContext registers ShopDb as a scoped service: one per request.

Migrations: evolving the schema

bash
dotnet ef migrations add InitialCreate
dotnet ef database update
migrations add compares your classes with the last migration and writes C# code that creates or changes tables. database update applies pending migrations to the database in the connection string.

Commit the Migrations folder to Git: it is the history of your schema. When you add a property, run migrations add again with a clear name (AddProductStock) and review the generated code before applying it.

Careful: Be very careful where database update points. A connection string for a shared or production database plus one command can drop columns and lose data. Apply migrations to production only through your team's reviewed deployment process, never casually from your laptop.

CRUD with EF Core in a minimal API

csharp
app.MapGet("/products", async (ShopDb db) =>
    await db.Products.AsNoTracking().OrderBy(p => p.Name).ToListAsync());

app.MapGet("/products/{id:int}", async (int id, ShopDb db) =>
    await db.Products.FindAsync(id) is Product p ? Results.Ok(p) : Results.NotFound());

app.MapPost("/products", async (Product p, ShopDb db) =>
{
    db.Products.Add(p);
    await db.SaveChangesAsync();                  // INSERT; p.Id is filled in
    return Results.Created($"/products/{p.Id}", p);
});

app.MapPut("/products/{id:int}/stock", async (int id, int stock, ShopDb db) =>
{
    var p = await db.Products.FindAsync(id);
    if (p is null) return Results.NotFound();
    p.Stock = stock;                              // tracked: EF Core notices the change
    await db.SaveChangesAsync();                  // UPDATE
    return Results.NoContent();
});

app.MapDelete("/products/{id:int}", async (int id, ShopDb db) =>
{
    var p = await db.Products.FindAsync(id);
    if (p is null) return Results.NotFound();
    db.Products.Remove(p);
    await db.SaveChangesAsync();                  // DELETE
    return Results.NoContent();
});
ToListAsync and SaveChangesAsync come from Microsoft.EntityFrameworkCore. Nothing is written to the database until SaveChangesAsync.

Change tracking and AsNoTracking

Entities you load are tracked: the DbContext remembers their original values, and SaveChanges writes any differences as UPDATE statements. Tracking costs memory and time, so for read-only queries (lists for display, reports) add AsNoTracking().

Relationships, Include and the N+1 problem

csharp
public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }                       // foreign key
    public Customer Customer { get; set; } = null!;           // navigation property
    public List<OrderItem> Items { get; set; } = new();
}
Navigation properties link entities. EF Core works out the foreign keys from the names.

The N+1 problem: you load N orders with one query, then, inside a loop, touch each order's Customer or Items, and the app runs one more query per order: 1 + N queries. With 500 orders that is 501 round trips to the database, and the page becomes slow. (Without lazy loading turned on, the navigation is simply empty instead, which is a different bug.) Fix it by loading what you need up front.

csharp
// One query with a JOIN: orders together with their customers and items
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .Where(o => o.CustomerId == customerId)
    .ToListAsync();

// Often better: project only the columns you need
var summary = await db.Orders
    .Where(o => o.CustomerId == customerId)
    .Select(o => new { o.Id, Customer = o.Customer.Name, ItemCount = o.Items.Count })
    .ToListAsync();
Include loads related entities in the same query. A Select projection fetches only what the screen needs.

Raw SQL, safely

LINQ queries are always parameterised, which protects you from SQL injection. If you must write SQL, use db.Products.FromSql($"SELECT * FROM Products WHERE Category = {category}"): the interpolated value becomes a parameter. Never build SQL by adding user input to a string and passing it to FromSqlRaw.

Common mistakes and fixes

  • Forgetting SaveChangesAsync: changes stay in memory and are lost.
  • Calling ToList() before Where: loads the whole table into memory (see IQueryable in module 6).
  • Injecting DbContext into a singleton: DbContext is not thread-safe and must be scoped.
  • SQLite and decimal: SQLite has no real decimal type; EF Core can store decimals there, but comparing or sorting decimal columns in a query is not translated. Fine for learning; real money columns usually live in SQL Server or PostgreSQL, where you also set precision (for example [Precision(18, 2)]).
  • Editing the database by hand and not with migrations: the schema and the code drift apart. Change classes, add a migration, apply it.

Need help?

Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.

Try:

Watch the lesson

Entity Framework Core: databases with C# · 1/7

EF Core

In this lesson you will store data in a real database with Entity Framework Core.

Prefer a video by a teacher? Find free videos on “Entity Framework Core tutorial migrations DbContext ASP.NET Core minimal API” on YouTube ↗

In real life: Low-stock report for a medical store chain

A pharmacy chain's inventory API needs a daily list of medicines with fewer than 20 units left, with the store name, so area managers can restock. One EF Core query with a projection fetches exactly that, in a single round trip.

csharp
app.MapGet("/reports/low-stock", async (ShopDb db) =>
    await db.StockItems
        .AsNoTracking()
        .Where(s => s.Quantity < 20)
        .OrderBy(s => s.Store.City).ThenBy(s => s.Quantity)
        .Select(s => new
        {
            Store = s.Store.Name,
            s.Store.City,
            Medicine = s.Medicine.Name,
            s.Quantity
        })
        .ToListAsync());
EF Core turns this into one SQL query with JOINs to Stores and Medicines, filtering and sorting inside the database. The StockItem, Store and Medicine entities are assumed to exist with navigation properties.

Practice (0/4 done)

Task 1 · Question

You add an entity and change another, but forget SaveChangesAsync. What happens in the database?

Task 2 · Question

What is the N+1 problem?

Task 3 · Question

Which command creates a new migration named AddPhone?

Task 4 · Practice

Turn your NotesApi (or a new BooksApi) into a database-backed API using EF Core with SQLite: add the packages, a Book entity (Id, Title, Author, Year), a LibraryDb DbContext, register it with AddDbContext, create and apply the InitialCreate migration, and implement GET all, GET by id, POST, PUT and DELETE with async EF Core calls. Restart the app and confirm data is still there.

Quick quiz

Question 1

1.What does an ORM do?

Question 2

2.Which property becomes the primary key by convention?

Question 3

3.When should you use AsNoTracking()?

Question 4

4.Which query is safe from SQL injection?

Question 5

5.Why should the Migrations folder be committed to Git?

Read more (free)

Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.

What does an ORM do?

Answer: Maps objects in code to rows in database tables

Object-Relational Mapper.

Which property becomes the primary key by convention?

Answer: Id or <ClassName>Id

EF Core treats Id or ProductId (for Product) as the key.

When should you use AsNoTracking()?

Answer: For read-only queries

No tracking means less memory and faster reads.

Which query is safe from SQL injection?

Answer: FromSql($"SELECT * FROM Users WHERE Name = {name}")

FromSql with interpolation turns the value into a parameter.

Why should the Migrations folder be committed to Git?

Answer: It is the reviewed history of schema changes that every environment applies

Migrations are code that evolves the schema consistently.

Which ORM comes with the Laravel PHP framework?Related

Answer: Eloquent

Eloquent is Laravel's ORM. Hibernate is Java, SQLAlchemy is Python and Drizzle is TypeScript.

From ORMs: working with databases from code

Which is the safe way to change a production database schema?Related

Answer: Reviewed migration scripts with Flyway or Liquibase

Migrations are versioned and reviewed.

From JPA and Spring Data repositories

What is a database migration?Related

Answer: A numbered file that changes the schema by one step, applied in order

Migrations are small, ordered schema changes kept in the repository, so every database can be built the same way.

From Creating the database: schema, migrations and seed data

Why is :category used in @Query instead of joining strings?Related

Answer: Room binds the value safely, preventing SQL injection

Bound parameters are safe and checked.

From Room database

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Unit testing with xUnit →

Next steps

  1. This is the last topic of “Web APIs with ASP.NET Core and EF Core”. The module exam comes next.
  2. Module 8 exam: Web APIs with ASP.NET Core and EF Core · 14 questions · 25 min, without help. 5 topics of this module still to finish first.
  3. Then the next module, Testing, tooling and Unity. 43 topics left in the course before the final exam.

← Previous: Configuration, secrets and the middleware pipeline