Entity Framework Core: working with databases

Create tables from classes with EF Core: `DbContext`, migrations, LINQ to Entities, CRUD operations, relationships and typical performance traps.

  • Advanced
  • 24 min
  • 10 questions
  • Lesson 18 of 22

In this lesson you will learn

  • Write model classes and a DbContext, create and apply a migration
  • Read, add, change and delete data with LINQ
  • Set up relationships and avoid the N+1 problem and SQL injection

With low-level ADO.NET you have to write every SQL query by hand and copy every column into an object. Entity Framework Core (EF Core), the official ORM of .NET, takes this work over: you write your classes, EF Core creates tables for them, translates LINQ queries into SQL and assembles the results into objects. EF Core works with SQL Server, PostgreSQL, MySQL and SQLite; in the examples we will use SQLite, which needs no installation.

  1. Add the packages

    Microsoft.EntityFrameworkCore.Sqlite is the SQLite provider, and Microsoft.EntityFrameworkCore.Design is needed by the migration tools.

  2. Install the dotnet-ef tool

    dotnet tool install --global dotnet-ef adds the dotnet ef commands; doing it once per computer is enough.

  3. Write the model and the DbContext

    Every table is a class, and the DbContext is the “door” to the database.

  4. Create and apply a migration

    dotnet ef migrations add InitialCreate writes the schema as a C# file, and dotnet ef database update applies it to the database.

Terminal
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef
Run in the project folder (the packages are downloaded from nuget.org)

The model and the DbContext

EF Core works by conventions: a property named Id is the primary key, the pair ClassroomId + Classroom is a foreign key, and List<Student> is the relationship “one classroom has many students”. The required keyword says that Name will be a NOT NULL column and must be set when an object is created. The DbSet<T> properties of the DbContext correspond to tables, and OnConfiguring says which database to connect to.

C#
using Microsoft.EntityFrameworkCore;

public class Classroom
{
    public int Id { get; set; }                        // primary key by convention
    public required string Name { get; set; }
    public List<Student> Students { get; set; } = [];  // one classroom, many students
}

public class Student
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public int Score { get; set; }
    public int ClassroomId { get; set; }               // foreign key
    public Classroom Classroom { get; set; } = null!;
}

public class SchoolContext : DbContext
{
    public DbSet<Classroom> Classrooms => Set<Classroom>();
    public DbSet<Student> Students => Set<Student>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=school.db");
}
The model: two classes, one relationship and the context

In a real application the connection string is not written into OnConfiguring: the context receives its options through the constructor — public class SchoolContext(DbContextOptions<SchoolContext> options) : DbContext(options) — and the string comes from a configuration file (you will see this in ASP.NET Core in the next lesson). A DbContext is a lightweight, short-lived object: it is usually created for one request or one unit of work, and it is not thread-safe, so it must not be shared between parallel tasks.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

Migrations

A migration is a C# file that carries a change in the model over to the database. migrations add compares the model with its previous state and creates a class with Up (apply) and Down (revert) methods in the Migrations/ folder; database update runs the migrations that have not been applied yet. Added a new property to a class? Create a new migration — say, AddEmail — and update the database. Migrations are kept in Git, so everyone on the team has a database with the same schema.

Terminal
dotnet ef migrations add InitialCreate
dotnet ef database update

Expected output

Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
Build started...
Build succeeded.
Applying migration '20260927101500_InitialCreate'.
Done.
Sample output: the migration name starts with a timestamp

LINQ to Entities: CRUD operations

A LINQ query written on a DbSet<T> runs not in memory but in the database: EF Core translates the expression tree into SQL (LINQ to Entities). Add and Remove only record the changes, and SaveChangesAsync sends them to the database in one transaction. An object read from the database is tracked (change tracking): it is enough to change its property and call SaveChangesAsync — EF Core writes the UPDATE itself.

C#
await using var db = new SchoolContext();

var tenA = new Classroom { Name = "10A" };
tenA.Students.Add(new Student { Name = "Aysel", Score = 95 });
tenA.Students.Add(new Student { Name = "Leyla", Score = 88 });
db.Classrooms.Add(tenA);
await db.SaveChangesAsync();                         // INSERTs; Id values are filled in

List<string> top = await db.Students
    .Where(s => s.Score >= 90)
    .OrderByDescending(s => s.Score)
    .Select(s => s.Name)
    .ToListAsync();                                  // the SQL runs here
Console.WriteLine(string.Join(", ", top));

Student leyla = await db.Students.SingleAsync(s => s.Name == "Leyla");
leyla.Score = 91;                                    // the change tracker notices this
await db.SaveChangesAsync();                         // UPDATE "Students" SET "Score" = ...

db.Students.Remove(leyla);
await db.SaveChangesAsync();                         // DELETE
Console.WriteLine(await db.Students.CountAsync());

Expected output

Aysel
1
Sample output on an empty database: only Aysel has 90+, and after Leyla is deleted one student remains

It is useful to see what SQL EF Core generates: ToQueryString() returns a query's SQL text without running it.

C#
var query = db.Students
    .Where(s => s.Score >= 80)
    .OrderByDescending(s => s.Score)
    .Select(s => s.Name);
Console.WriteLine(query.ToQueryString());

Expected output

SELECT "s"."Name"
FROM "Students" AS "s"
WHERE "s"."Score" >= 80
ORDER BY "s"."Score" DESC
Sample output for SQLite; other providers generate slightly different SQL

Relationships, Include and the N+1 problem

Related data is not loaded automatically: if student.Classroom or classroom.Students was not requested in the query, it stays empty. Include(...) brings related objects in the same query. For a many-to-many relationship it is enough to put a collection on both sides — EF Core creates the join table itself. The best-known performance mistake is N+1: one query for the list, then one more query for every element.

C#
public class Course
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public List<Student> Students { get; set; } = [];
}

// in Student:  public List<Course> Courses { get; set; } = [];
// EF Core creates the join table CourseStudent automatically

var withCourses = await db.Students
    .Include(s => s.Courses)                 // load related data in the same query
    .Where(s => s.Courses.Any(c => c.Title == "Math"))
    .ToListAsync();
A many-to-many relationship: a student has many courses, and a course has many students

N+1: 1 + N queries

var classes = await db.Classrooms.ToListAsync();
foreach (var c in classes)
{
    // one more query for every classroom: the N+1 problem
    int count = await db.Students.CountAsync(s => s.ClassroomId == c.Id);
    Console.WriteLine($"{c.Name}: {count}");
}

Projection: one query

var classes = await db.Classrooms
    .Select(c => new { c.Name, Count = c.Students.Count })
    .ToListAsync();                  // a single query with a subquery
foreach (var c in classes)
    Console.WriteLine($"{c.Name}: {c.Count}");

Dangerous: string concatenation

var list = await db.Students
    .FromSqlRaw("SELECT * FROM Students WHERE Name = '" + name + "'")
    .ToListAsync();                  // SQL injection!

Safe: the interpolation becomes a parameter

var list = await db.Students
    .FromSql($"SELECT * FROM Students WHERE Name = {name}")
    .ToListAsync();                  // {name} becomes a SQL parameter
When you need raw SQL, FromSql (since EF Core 7) turns interpolated values into SQL parameters

Remember

  • EF Core maps classes to tables: DbContext + DbSet<T>; Id is the primary key and XxxId a foreign key.
  • Migrations: dotnet ef migrations add writes the schema and dotnet ef database update applies it; they are kept in Git.
  • LINQ on a DbSet becomes SQL (ToQueryString() shows it); SaveChangesAsync saves the changes.
  • Load related data in one query with Include or a projection — avoid N+1.
  • Raw SQL via FromSql($"..."); AsNoTracking() for reading, ExecuteUpdateAsync/ExecuteDeleteAsync for bulk changes.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

10 questions. Every correct answer earns XP.

Question 1 / 10

Question 1 / 10. Which command applies migrations to the database?