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.
Add the packages
Microsoft.EntityFrameworkCore.Sqliteis the SQLite provider, andMicrosoft.EntityFrameworkCore.Designis needed by the migration tools.Install the dotnet-ef tool
dotnet tool install --global dotnet-efadds thedotnet efcommands; doing it once per computer is enough.Write the model and the DbContext
Every table is a class, and the
DbContextis the “door” to the database.Create and apply a migration
dotnet ef migrations add InitialCreatewrites the schema as a C# file, anddotnet ef database updateapplies it to the database.
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-efThe 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.
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");
}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.
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.
dotnet ef migrations add InitialCreate
dotnet ef database updateExpected output
Build started... Build succeeded. Done. To undo this action, use 'ef migrations remove' Build started... Build succeeded. Applying migration '20260927101500_InitialCreate'. Done.
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.
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
It is useful to see what SQL EF Core generates: ToQueryString() returns a query's SQL text without running it.
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
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.
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();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 parameterFromSql (since EF Core 7) turns interpolated values into SQL parametersRemember
- EF Core maps classes to tables:
DbContext+DbSet<T>;Idis the primary key andXxxIda foreign key. - Migrations:
dotnet ef migrations addwrites the schema anddotnet ef database updateapplies it; they are kept in Git. - LINQ on a
DbSetbecomes SQL (ToQueryString()shows it);SaveChangesAsyncsaves the changes. - Load related data in one query with
Includeor a projection — avoid N+1. - Raw SQL via
FromSql($"...");AsNoTracking()for reading,ExecuteUpdateAsync/ExecuteDeleteAsyncfor bulk changes.