Entity Framework Core

Files are fine for small amounts of data, but real applications keep data in a database: thousands of books, members and loans that many users read and change at the same time. Writing SQL by hand for every query works, but it means mapping rows to objects and back yourself. Entity Framework Core (EF Core) is .NET's official ORM (object-relational mapper): you work with ordinary C# classes and LINQ, and EF Core writes the SQL.

EF Core 10 was released with .NET 10 in November 2025 and is a long-term support release. In this lesson you will build a small library database with SQLite, create it with migrations, and add, query, update and delete data.

Setting up the project

EF Core supports many databases through providers. SQLite stores the whole database in a single file, needs no server, and is perfect for learning. Create a console project and add two packages:

dotnet new console -o LibraryDb
cd LibraryDb
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef

• Microsoft.EntityFrameworkCore.Sqlite is the SQLite provider; it brings EF Core itself along.
• Microsoft.EntityFrameworkCore.Design is needed by the command-line tools at design time.
• dotnet-ef is a .NET tool that adds the dotnet ef commands. You install it once per computer.

For other databases you swap the provider package: Microsoft.EntityFrameworkCore.SqlServer for SQL Server, Npgsql.EntityFrameworkCore.PostgreSQL for PostgreSQL, and community or vendor providers exist for MySQL. Your model and queries stay almost the same. (The .NET 10 SDK also accepts the newer noun-first form dotnet package add; both work.)

The model and the DbContext

The model is a set of plain classes, called entities. A DbContext class represents a session with the database, and each DbSet<T> property represents a table. Put this in Model.cs:

using Microsoft.EntityFrameworkCore;

public class Author
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Book> Books { get; } = [];
}

public class Book
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public int Year { get; set; }
    public int Copies { get; set; }

    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public class LibraryContext : DbContext
{
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=library.db");
}

EF Core follows conventions, so this small model is enough:

• A property named Id becomes the primary key, and SQLite generates its values automatically.
• List<Book> Books on Author and AuthorId plus Author on Book describe a one-to-many relationship: one author has many books. AuthorId becomes a foreign key column.
• required and non-nullable string make the column NOT NULL; a string? property would allow null.
• = null! on Author tells the compiler "EF Core will fill this in", avoiding a nullable warning.

Creating the database with migrations

A migration is a C# file that describes how to change the database schema to match your model. Create the first one and apply it:

dotnet ef migrations add InitialCreate
dotnet ef database update

The first command generates a Migrations folder with code that creates the Authors and Books tables. The second command creates library.db and runs that code. Whenever you change the model later, for example by adding an Isbn property, you add a new migration (dotnet ef migrations add AddIsbn) and update again. Migrations keep a history, so every developer and every server can bring their database to exactly the same version.

Adding and querying data

Now Program.cs can work with the database through the context. Everything uses await because database calls are I/O.

using Microsoft.EntityFrameworkCore;

await using var db = new LibraryContext();

if (!await db.Authors.AnyAsync())
{
    var premchand = new Author { Name = "Premchand" };
    premchand.Books.Add(new Book { Title = "Godan", Year = 1936, Copies = 3 });
    premchand.Books.Add(new Book { Title = "Gaban", Year = 1931, Copies = 2 });

    var narayan = new Author { Name = "R. K. Narayan" };
    narayan.Books.Add(new Book { Title = "Malgudi Days", Year = 1943, Copies = 4 });

    db.Authors.AddRange(premchand, narayan);
    await db.SaveChangesAsync();
}

List<Book> before1940 = await db.Books
    .Where(b => b.Year < 1940)
    .OrderBy(b => b.Year)
    .ToListAsync();

foreach (Book b in before1940)
{
    Console.WriteLine($"{b.Year} {b.Title}");
}

List<Author> authors = await db.Authors
    .Include(a => a.Books)
    .OrderBy(a => a.Name)
    .ToListAsync();

foreach (Author a in authors)
{
    Console.WriteLine($"{a.Name}: books {a.Books.Count}, copies {a.Books.Sum(b => b.Copies)}");
}

On the first run:

1931 Gaban
1936 Godan
Premchand: books 2, copies 5
R. K. Narayan: books 1, copies 4

Here is what happened:

• Add and AddRange only tell EF Core about new objects. Nothing is written until SaveChangesAsync(), which sends all the INSERT statements in one transaction and fills in the generated Id values. Adding books to premchand.Books was enough to set their AuthorId.
• The LINQ query with Where and OrderBy is translated to SQL and runs inside the database. Only matching rows travel to your program. ToListAsync() executes it.
• Related data is not loaded automatically. Include(a => a.Books) asks EF Core to load each author's books in the same query; without it, a.Books would be empty.
• The if (!await db.Authors.AnyAsync()) guard adds the sample data only once, so running the program again does not create duplicates.

Updating and deleting

EF Core tracks the entities it loads. Change their properties, call SaveChangesAsync(), and EF Core works out the UPDATE statements for you.

Book godan = await db.Books.SingleAsync(b => b.Title == "Godan");
godan.Copies += 2;
await db.SaveChangesAsync();
Console.WriteLine($"Godan copies now: {godan.Copies}");

Book? gaban = await db.Books.FirstOrDefaultAsync(b => b.Title == "Gaban");
if (gaban is not null)
{
    db.Books.Remove(gaban);
    await db.SaveChangesAsync();
}
Console.WriteLine($"Books left: {await db.Books.CountAsync()}");

Godan copies now: 5
Books left: 2

For read-only queries, add .AsNoTracking() before ToListAsync(); EF Core then skips change tracking, which is faster. EF Core 7 and later also offer ExecuteUpdateAsync and ExecuteDeleteAsync to change many rows with one SQL statement without loading them first.

Using EF Core in ASP.NET Core

In a web API you do not create the context with new. You register it with dependency injection, and each request gets its own context:

builder.Services.AddDbContext<LibraryContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("Library")));

app.MapGet("/books", async (LibraryContext db) =>
    await db.Books.AsNoTracking().OrderBy(b => b.Title).ToListAsync());

The context class then takes its options through a constructor, public LibraryContext(DbContextOptions<LibraryContext> options) : base(options) { }, instead of overriding OnConfiguring, and the connection string lives in appsettings.json rather than in code. This connects directly to the API you built in ASP.NET Core Minimal APIs.

Common mistakes

• Forgetting SaveChangesAsync(). Add, Remove and property changes stay in memory until you save.
• Expecting related data without Include. Navigation properties stay empty unless you load them.
• Querying inside a loop. Calling the database once per item (the "N+1" problem) is slow; load what you need in one query with Include or a projection using Select.
• Changing the model without a migration. The database no longer matches the classes. Add a migration and update.
• Building SQL from user input with string concatenation. EF Core's LINQ queries are parameterized and safe; if you write raw SQL, use FromSql with interpolated parameters, never + with user text.

Key points

• EF Core maps C# classes (entities) to database tables; a DbContext with DbSet<T> properties is your gateway.
• Providers such as Microsoft.EntityFrameworkCore.Sqlite connect EF Core to a specific database.
• dotnet ef migrations add and dotnet ef database update create and evolve the schema.
• LINQ queries are translated to SQL; ToListAsync, FirstOrDefaultAsync and CountAsync run them.
• Add, change or remove tracked entities, then call SaveChangesAsync().
• Use Include for related data and AsNoTracking for read-only queries; register the context with AddDbContext in ASP.NET Core.

public class Member
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? City { get; set; }
}

public class Loan
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
    public int MemberId { get; set; }
    public Member Member { get; set; } = null!;
    public DateOnly LoanDate { get; set; }
}

// inside LibraryContext
public DbSet<Member> Members => Set<Member>();
public DbSet<Loan> Loans => Set<Loan>();

dotnet ef migrations add AddLoans
dotnet ef database update

var isha = new Member { Name = "Isha", City = "Kochi" };
Book godan = await db.Books.SingleAsync(b => b.Title == "Godan");
db.Loans.Add(new Loan { Book = godan, Member = isha, LoanDate = new DateOnly(2026, 10, 30) });
await db.SaveChangesAsync();

var loans = await db.Loans
    .Include(l => l.Book)
    .Include(l => l.Member)
    .ToListAsync();

foreach (Loan l in loans)
{
    Console.WriteLine($"{l.Member.Name} borrowed {l.Book.Title} on {l.LoanDate:dd MMM yyyy}");
}

Isha borrowed Godan on 30 Oct 2026