Entity Framework Core

Setting Up

EF Core maps C# classes to database tables and translates LINQ into SQL. This lesson uses SQLite, which needs no server — the database is a single file — so every example below runs as-is:

dotnet new console -n Shop
cd Shop
dotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 8.0.11

For SQL Server or PostgreSQL you would add Microsoft.EntityFrameworkCore.SqlServer or Npgsql.EntityFrameworkCore.PostgreSQL and change one Use... call; everything else stays the same.

The Model and the DbContext

Entities are plain classes; navigation properties (Orders, Customer) express relationships, and ...Id properties become foreign keys by convention. Save this as Shop.cs — the programs below all use it.

global using Microsoft.EntityFrameworkCore;
using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public bool IsActive { get; set; } = true;
    public List<Order> Orders { get; set; } = new();
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Status { get; set; } = "Pending";
    public decimal Total { get; set; }
    public Customer Customer { get; set; } = null!;
}

public class ShopContext : DbContext
{
    public static bool LogSql;
    public static int Commands;

    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseSqlite("Data Source=shop.db").AddInterceptors(new SqlPrinter());

    protected override void ConfigureConventions(ModelConfigurationBuilder builder) =>
        builder.Properties<decimal>().HaveConversion<double>();   // SQLite has no decimal type

    protected override void OnModelCreating(ModelBuilder model)
    {
        model.Entity<Customer>().Property(c => c.Name).HasMaxLength(200);
        model.Entity<Customer>().HasIndex(c => c.Email).IsUnique();
    }

    public static ShopContext CreateFresh()
    {
        var db = new ShopContext();
        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();
        db.Customers.AddRange(
            new Customer { Name = "Alice", Email = "alice@example.com",
                Orders = { new() { Total = 250m }, new() { Total = 80m, Status = "Shipped" } } },
            new Customer { Name = "Bob", Email = "bob@example.com", Orders = { new() { Total = 150m } } },
            new Customer { Name = "Carol", Email = "carol@example.com", IsActive = false });
        db.SaveChanges();
        db.ChangeTracker.Clear();
        return db;
    }

    private class SqlPrinter : DbCommandInterceptor
    {
        public override DbCommand CommandInitialized(CommandEndEventData data, DbCommand command)
        {
            Commands++;
            if (LogSql) Console.WriteLine($"SQL> {command.CommandText}\n");
            return command;
        }
    }
}

The interceptor is not something you need in production; it is here so each example can print the exact SQL it sent. CreateFresh deletes and recreates shop.db with three customers, so every run starts from the same data. The global using makes the EF Core namespace available in Program.cs too.

Creating the Schema

EnsureCreated builds the tables from the model. GenerateCreateScript shows the DDL it runs.

using var db = ShopContext.CreateFresh();
Console.WriteLine(db.Database.GenerateCreateScript());
Console.WriteLine($"{db.Customers.Count()} customers, {db.Orders.Count()} orders");

$ dotnet run
CREATE TABLE "Customers" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Customers" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL,
    "Email" TEXT NOT NULL,
    "IsActive" INTEGER NOT NULL
);


CREATE TABLE "Orders" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Orders" PRIMARY KEY AUTOINCREMENT,
    "CustomerId" INTEGER NOT NULL,
    "Status" TEXT NOT NULL,
    "Total" REAL NOT NULL,
    CONSTRAINT "FK_Orders_Customers_CustomerId" FOREIGN KEY ("CustomerId") REFERENCES "Customers" ("Id") ON DELETE CASCADE
);


CREATE UNIQUE INDEX "IX_Customers_Email" ON "Customers" ("Email");


CREATE INDEX "IX_Orders_CustomerId" ON "Orders" ("CustomerId");



3 customers, 3 orders

From two classes, EF inferred primary keys (Id → INTEGER ... AUTOINCREMENT), a foreign key from CustomerId with ON DELETE CASCADE (the relationship is required, because CustomerId is a non-nullable int), an index on it, and the unique index configured in OnModelCreating. Note Total became REAL — the value converter at work. EnsureCreated is for demos and tests; real applications evolve the schema with migrations (below).

Querying: LINQ Becomes SQL

Operators on a DbSet build an IQueryable; nothing touches the database until you enumerate it, and then the whole chain becomes one SQL statement.

using var db = ShopContext.CreateFresh();
ShopContext.LogSql = true;

var active = db.Customers
    .Where(c => c.IsActive && c.Name.StartsWith("A"))
    .OrderBy(c => c.Name)
    .AsNoTracking()
    .ToList();
Console.WriteLine(string.Join(", ", active.Select(c => c.Name)));

var summaries = db.Customers
    .Select(c => new { c.Name, Open = c.Orders.Count(o => o.Status == "Pending"), Spent = c.Orders.Sum(o => o.Total) })
    .ToList();
foreach (var s in summaries) Console.WriteLine($"{s.Name}: {s.Open} open, {s.Spent} total");

$ dotnet run
SQL> SELECT "c"."Id", "c"."Email", "c"."IsActive", "c"."Name"
FROM "Customers" AS "c"
WHERE "c"."IsActive" AND "c"."Name" LIKE 'A%'
ORDER BY "c"."Name"

Alice
SQL> SELECT "c"."Name", (
    SELECT COUNT(*)
    FROM "Orders" AS "o"
    WHERE "c"."Id" = "o"."CustomerId" AND "o"."Status" = 'Pending') AS "Open", (
    SELECT COALESCE(SUM("o0"."Total"), 0.0)
    FROM "Orders" AS "o0"
    WHERE "c"."Id" = "o0"."CustomerId") AS "Spent"
FROM "Customers" AS "c"

Alice: 1 open, 330 total
Bob: 1 open, 150 total
Carol: 0 open, 0 total

The Where, StartsWith and OrderBy were translated to WHERE ... LIKE 'A%' and ORDER BY — filtering happened in the database, not after downloading every customer. The projection with Count and Sum became correlated subqueries, and only the three columns asked for came back. AsNoTracking tells EF not to keep snapshots of the results for change detection: use it for read-only queries.

The N+1 Problem and Include

Accessing related data without loading it causes one extra query per row — the N+1 problem. Include loads the relationship in the same query.

using var db = ShopContext.CreateFresh();

ShopContext.Commands = 0;
foreach (var c in db.Customers.ToList())
{
    int n = db.Orders.Count(o => o.CustomerId == c.Id);
    Console.WriteLine($"{c.Name}: {n}");
}
Console.WriteLine($"one query per customer: {ShopContext.Commands} commands\n");

ShopContext.Commands = 0;
ShopContext.LogSql = true;
foreach (var c in db.Customers.Include(c => c.Orders).AsNoTracking().ToList())
    Console.WriteLine($"{c.Name}: {c.Orders.Count}");
Console.WriteLine($"with Include: {ShopContext.Commands} command");

$ dotnet run
Alice: 2
Bob: 1
Carol: 0
one query per customer: 4 commands

SQL> SELECT "c"."Id", "c"."Email", "c"."IsActive", "c"."Name", "o"."Id", "o"."CustomerId", "o"."Status", "o"."Total"
FROM "Customers" AS "c"
LEFT JOIN "Orders" AS "o" ON "c"."Id" = "o"."CustomerId"
ORDER BY "c"."Id"

Alice: 2
Bob: 1
Carol: 0
with Include: 1 command

The first loop issued 1 query for the customers plus 1 per customer — 4 here, 10,001 for ten thousand customers. Include produced a single LEFT JOIN. The ORDER BY on the keys is how EF groups the joined rows back into objects. For very wide includes, AsSplitQuery() trades the one big join for one query per collection.

Saving: Change Tracking

EF snapshots tracked entities when it loads them. SaveChanges compares each entity with its snapshot and writes only what changed, in a transaction.

using var db = ShopContext.CreateFresh();

var bob = db.Customers.Single(c => c.Name == "Bob");
bob.Email = "robert@example.com";
Console.WriteLine(db.Entry(bob).State);

db.Orders.Add(new Order { CustomerId = bob.Id, Total = 42m });
Console.WriteLine($"{db.ChangeTracker.Entries().Count(e => e.State != EntityState.Unchanged)} pending changes");

ShopContext.LogSql = true;
int rows = db.SaveChanges();
ShopContext.LogSql = false;
Console.WriteLine($"{rows} rows written; new order id = {db.Orders.Max(o => o.Id)}");

$ dotnet run
Modified
2 pending changes
SQL> UPDATE "Customers" SET "Email" = @p0
WHERE "Id" = @p1
RETURNING 1;


SQL> INSERT INTO "Orders" ("CustomerId", "Status", "Total")
VALUES (@p0, @p1, @p2)
RETURNING "Id";


2 rows written; new order id = 4

Only the Email column appears in the UPDATE — not Name or IsActive — because only it differed from the snapshot. The INSERT uses RETURNING "Id" so EF can fill in the database-generated key. Both statements ran in one SaveChanges, which wraps them in a transaction: either both are saved or neither.

Bulk Updates and Deletes

ExecuteUpdate and ExecuteDelete (EF Core 7+) run a single SQL statement against every matching row, without loading entities first.

using var db = ShopContext.CreateFresh();
ShopContext.LogSql = true;

int cancelled = db.Orders
    .Where(o => o.Status == "Pending" && o.Total < 200m)
    .ExecuteUpdate(s => s.SetProperty(o => o.Status, "Cancelled"));

int deleted = db.Customers.Where(c => !c.IsActive).ExecuteDelete();

ShopContext.LogSql = false;
Console.WriteLine($"cancelled {cancelled}, deleted {deleted}");
Console.WriteLine(string.Join(", ", db.Orders.AsNoTracking().Select(o => $"{o.Id}:{o.Status}")));

$ dotnet run
SQL> UPDATE "Orders" AS "o"
SET "Status" = 'Cancelled'
WHERE "o"."Status" = 'Pending' AND "o"."Total" < 200.0

SQL> DELETE FROM "Customers" AS "c"
WHERE NOT ("c"."IsActive")

cancelled 1, deleted 1
1:Pending, 2:Shipped, 3:Cancelled

Each call is one round trip, however many rows match. The trade-off: these bypass the change tracker, so entities already loaded in this context do not see the new values, and SaveChanges logic such as auditing interceptors does not run.

Raw SQL, Safely

When LINQ can’t express a query, FromSql runs your SQL and still returns tracked entities. Interpolated values become parameters, not string concatenation.

using var db = ShopContext.CreateFresh();
ShopContext.LogSql = true;

string prefix = "B";
var matches = db.Customers
    .FromSql($"SELECT * FROM Customers WHERE Name LIKE {prefix + "%"}")
    .Where(c => c.IsActive)
    .ToList();
Console.WriteLine(string.Join(", ", matches.Select(c => c.Name)));

string hostile = "x' OR '1'='1";
Console.WriteLine(db.Customers.FromSql($"SELECT * FROM Customers WHERE Name = {hostile}").Count());

$ dotnet run
SQL> SELECT "c"."Id", "c"."Email", "c"."IsActive", "c"."Name"
FROM (
    SELECT * FROM Customers WHERE Name LIKE @p0
) AS "c"
WHERE "c"."IsActive"

Bob
SQL> SELECT COUNT(*)
FROM (
    SELECT * FROM Customers WHERE Name = @p0
) AS "c"

0

The {prefix + "%"} hole became @p0, a parameter, and EF wrapped the raw SQL in a subquery so the LINQ Where could still be composed on top. The injection attempt matched nothing, because the whole string was sent as one parameter value. FromSqlRaw with string concatenation would not protect you — prefer FromSql.

Transactions Across Several SaveChanges

Each SaveChanges is atomic on its own. When several calls must succeed or fail together, wrap them in an explicit transaction.

using var db = ShopContext.CreateFresh();

try
{
    using var tx = db.Database.BeginTransaction();
    var alice = db.Customers.Single(c => c.Name == "Alice");
    alice.Name = "Alice Smith";
    db.SaveChanges();

    db.Customers.Add(new Customer { Name = "Dup", Email = "bob@example.com" });
    db.SaveChanges();
    tx.Commit();
}
catch (DbUpdateException ex)
{
    Console.WriteLine($"failed: {ex.InnerException?.Message}");
}

db.ChangeTracker.Clear();
Console.WriteLine(string.Join(", ", db.Customers.Select(c => c.Name)));

$ dotnet run
failed: SQLite Error 19: 'UNIQUE constraint failed: Customers.Email'.
Alice, Bob, Carol

The first SaveChanges succeeded, but the second violated the unique email index. Because Commit was never reached, disposing the transaction rolled back both — Alice is still “Alice”, not “Alice Smith”.

Migrations

Migrations turn model changes into versioned schema changes. They need the dotnet-ef tool and the design package:

dotnet tool install --global dotnet-ef --version 8.0.11
dotnet add package Microsoft.EntityFrameworkCore.Design --version 8.0.11

Create the first migration from the model in Shop.cs, then apply it. (EnsureCreated and migrations don’t mix: a database made by EnsureCreated has no migration history, so start migrations against a fresh database.)

$ dotnet ef migrations add InitialCreate
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
$ dotnet ef database update
Build started...
Build succeeded.
Applying migration '20260930190320_InitialCreate'.
Done.

Now change the model — add public string? Phone { get; set; } to Customer — and add a second migration. EF diffs the model against its snapshot and generates only the change:

$ dotnet ef migrations add AddCustomerPhone
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
$ dotnet ef migrations list
Build started...
Build succeeded.
20260930190320_InitialCreate
20260930190346_AddCustomerPhone (Pending)

The generated file, Migrations/20260930190346_AddCustomerPhone.cs, has an Up and a Down:

public partial class AddCustomerPhone : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Phone",
            table: "Customers",
            type: "TEXT",
            nullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "Phone",
            table: "Customers");
    }
}

For production, generate the SQL and review it instead of letting the app migrate itself:

$ dotnet ef migrations script InitialCreate AddCustomerPhone
Build started...
Build succeeded.
BEGIN TRANSACTION;

ALTER TABLE "Customers" ADD "Phone" TEXT NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20260930190346_AddCustomerPhone', '8.0.11');

COMMIT;

The property is nullable, so the new column is NULL-able and existing rows are untouched. The __EFMigrationsHistory table is how EF knows which migrations a database already has; dotnet ef database update applies only the pending ones. The timestamps in the migration names come from when you run the command. Because the migration is C#, you can edit it before applying — for example to back-fill data in Up — which is the main reason to prefer migrations over EnsureCreated once real data exists.

Practice

1. Top customer by spend. Write a single query that returns the name of the customer with the highest total across all orders, and print the SQL with ToQueryString() instead of running it through the interceptor.

using var db = ShopContext.CreateFresh();

var query = db.Customers
    .OrderByDescending(c => c.Orders.Sum(o => o.Total))
    .Select(c => c.Name);

Console.WriteLine(query.ToQueryString());
Console.WriteLine();
ShopContext.LogSql = true;
Console.WriteLine(query.First());

$ dotnet run
SELECT "c"."Name"
FROM "Customers" AS "c"
ORDER BY (
    SELECT COALESCE(SUM("o"."Total"), 0.0)
    FROM "Orders" AS "o"
    WHERE "c"."Id" = "o"."CustomerId") DESC

SQL> SELECT "c"."Name"
FROM "Customers" AS "c"
ORDER BY (
    SELECT COALESCE(SUM("o"."Total"), 0.0)
    FROM "Orders" AS "o"
    WHERE "c"."Id" = "o"."CustomerId") DESC
LIMIT 1

Alice

2. Ship all of a customer’s orders. Using ExecuteUpdate, mark every pending order belonging to Alice as Shipped, filtering through the navigation property.

using var db = ShopContext.CreateFresh();
ShopContext.LogSql = true;

int n = db.Orders
    .Where(o => o.Customer.Name == "Alice" && o.Status == "Pending")
    .ExecuteUpdate(s => s.SetProperty(o => o.Status, "Shipped"));

ShopContext.LogSql = false;
Console.WriteLine($"{n} order(s) shipped");

$ dotnet run
SQL> UPDATE "Orders" AS "o"
SET "Status" = 'Shipped'
FROM "Customers" AS "c"
WHERE "o"."CustomerId" = "c"."Id" AND "c"."Name" = 'Alice' AND "o"."Status" = 'Pending'

1 order(s) shipped

3. Detect a no-op save. Load Bob, set his name to "Bob" (unchanged), and show what SaveChanges does.

using var db = ShopContext.CreateFresh();
var bob = db.Customers.Single(c => c.Name == "Bob");
bob.Name = "Bob";

ShopContext.Commands = 0;
Console.WriteLine($"state: {db.Entry(bob).State}");
Console.WriteLine($"rows: {db.SaveChanges()}, commands: {ShopContext.Commands}");

$ dotnet run
state: Unchanged
rows: 0, commands: 0