Entity Framework Core

Entity Framework Core is .NET's object-relational mapper (ORM): you work with C# classes and LINQ, and EF Core generates SQL, materializes objects and tracks changes.

Concept

Entity Framework Core is .NET's object-relational mapper (ORM): you work with C# classes and LINQ, and EF Core generates SQL, materializes objects and tracks changes.

A session with the database: queries, change tracking, SaveChanges. Short-lived (one per request/unit of work), not thread-safe

A table-like queryable collection of entities A class mapped to a table; Id/<Type>Id is the key by convention

Navigation properties + foreign keys: one-to-many (Ecu.Dtcs / Dtc.Ecu), one-to-one, many-to-many

Conventions, data annotations ([MaxLength]), or the fluent API in OnModelCreating (indexes, unique constraints, delete behaviour)

Versioned schema changes: dotnet ef migrations add AddFirmware, dotnet ef database update (tool: dotnet tool install -g dotnet-ef). EnsureCreated is for demos and tests only Change tracking

Entities loaded normally are *tracked*; SaveChanges compares them with original values and writes only changes. States: Added, Unchanged, Modified, Deleted, Detached No-tracking queries

AsNoTracking() for read-only queries — faster, less memory

Eager (Include), explicit (Entry(e).Collection(...).Load()), lazy (proxies package; loads on first access — beware of N+1 queries)

SaveChanges is transactional by itself; Database.BeginTransaction() spans several calls

Optimistic concurrency with a concurrency token ([ConcurrencyCheck], IsConcurrencyToken, or a SQL Server rowversion) → DbUpdateConcurrencyException Bulk operations

ExecuteUpdate / ExecuteDelete run set-based SQL without loading entities

Query optimization: project only needed columns with Select, filter in the database (keep IQueryable until the end), avoid N+1 with Include or projections, use AsNoTracking, add indexes, inspect SQL with ToQueryString() and logging, and use AsSplitQuery() for large multi-collection includes.

Why It Matters

Most line-of-business .NET applications — test management systems, fleet portals, manufacturing execution systems — use EF Core. Knowing what SQL it generates and how tracking works separates fast, correct applications from ones with N+1 queries and lost updates.

Syntax

public class GarageContext(DbContextOptions<GarageContext> o) : DbContext(o) { public DbSet<Ecu> Ecus => Set<Ecu>(); }
builder.Services.AddDbContext<GarageContext>(o => o.UseSqlite("Data Source=garage.db"));
var active = await db.Dtcs.Where(d => d.Active).Include(d => d.Ecu).AsNoTracking().ToListAsync();
db.Ecus.Add(ecu); await db.SaveChangesAsync();
await db.Dtcs.Where(d => !d.Active).ExecuteDeleteAsync();

Example

50.1 Model, queries, loading, tracking, transactions and concurrency

The program uses Microsoft.EntityFrameworkCore.Sqlite with a temporary SQLite file.

#:package [email protected]
#:property PublishAot=false
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;

var sqlLog = new List<string>();
string dbPath = Path.Combine(Path.GetTempPath(), "eh_efcore_demo.db");
File.Delete(dbPath);
var options = new DbContextOptionsBuilder<GarageContext>()
    .UseSqlite($"Data Source={dbPath}")
    .LogTo(s => sqlLog.Add(s), [Microsoft.EntityFrameworkCore.Diagnostics.RelationalEventId.CommandExecuted])
    .Options;

// ---- Create schema and seed ----
using (var db = new GarageContext(options))
{
    db.Database.EnsureCreated();           // demos/tests; real apps use migrations (dotnet ef migrations add Initial)
    var vcu = new Ecu { Code = "VCU", Firmware = "2.4.1" };
    vcu.Dtcs.Add(new Dtc { Code = "P0A80", Active = true });
    vcu.Dtcs.Add(new Dtc { Code = "U0100", Active = false });
    var bms = new Ecu { Code = "BMS", Firmware = "1.9.0", Dtcs = { new Dtc { Code = "P0AFA", Active = true } } };
    db.Vehicles.Add(new Vehicle { Vin = "EHBAUREV250000001", Ecus = { vcu, bms } });
    Console.WriteLine($"state before SaveChanges: {db.Entry(vcu).State}");
    int saved = db.S
…

erDiagram
    Vehicles ||--o{ Ecus : contains
    Ecus ||--o{ Dtcs : reports
    Vehicles {
        int Id PK
        string Vin
    }
    Ecus {
        int Id PK
        string Code UK
        string Firmware
        int Revision
        int VehicleId FK
    }
    Dtcs {
        int Id PK
        string Code
        bool Active
        int EcuId FK
    }

Expected Result

One SaveChanges inserted the vehicle, two ECUs and three DTCs (6 rows) in the right order, filling in generated keys and foreign keys. The LINQ query became a single SQL JOIN. Without Include the navigation collection stayed empty. The no-tracking entity was Detached, so its change was never saved. User B's update was rejected because the Revision concurrency token no longer matched.

state before SaveChanges: Added
SaveChanges wrote 6 rows; generated key VCU.Id = 1, state now Unchanged
SQL for the query:
SELECT "e"."Code" || ':' || "d"."Code"
FROM "Dtcs" AS "d"
INNER JOIN "Ecus" AS "e" ON "d"."EcuId" = "e"."Id"
WHERE "d"."Active"
ORDER BY "d"."Code"
result: VCU:P0A80, BMS:P0AFA
projection: { Code = BMS, Active = 1 }, { Code = VCU, Active = 1 }
without Include: VCU.Dtcs.Count = 0 (navigation not loaded)
eager Include:   VCU.Dtcs.Count = 2
explicit Load:   BMS.Dtcs.Count = 1
tracked entity after edit: Modified, modified: Firmware, original 2.4.1
no-tracking entity state: Detached; SaveChanges wrote 0 rows
after rollback ABS exists: False
concurrency conflict detected: user B's update was rejected (row changed since it was read)
ExecuteDelete removed 1 inactive DTC(s)
SQL commands executed: 29

Common Mistakes

Sharing one DbContext across threads or keeping it alive for the whole application.

N+1 queries: looping over entities and touching a lazy-loaded navigation in each iteration.

Calling ToList() too early, then filtering in memory.

Using EnsureCreated in production instead of migrations.

Loading entities just to delete/update them in bulk (use ExecuteDelete/ExecuteUpdate).

Ignoring DbUpdateConcurrencyException — silently losing another user's changes.

Best Practices

One DbContext per unit of work (scoped in ASP.NET Core).

AsNoTracking() and projections (Select) for read-only queries.

Migrations checked into source control and applied by a deployment step.

Log generated SQL in development and review it; add indexes for frequent filters.

Use concurrency tokens for data edited by several users.

Interview Relevance

Very high: "what does DbContext do?", "tracking vs no-tracking?", "eager vs lazy vs explicit loading?", "what is the N+1 problem?", "how do migrations work?", "how does EF Core handle concurrency?", "EF Core vs Dapper?".

Practice Questions

Add a many-to-many relationship between Ecu and TestCase.

Rewrite the summary projection to include the newest DTC code per ECU.

Create a migration with dotnet ef migrations add Initial in a console project and inspect the generated code.

Use ExecuteUpdate to bump the firmware of all ECUs of one vehicle.

Quick Quiz

Q1. (Multiple choice) Which call prevents the loaded entities from being tracked? A) Include B) AsNoTracking C) AsSplitQuery D) ToQueryString

Answer: B (verified: state Detached, SaveChanges wrote 0 rows).

Q2. (Predict the output) An Ecu is loaded without Include. What is ecu.Dtcs.Count?

Answer: 0 — the navigation was not loaded (verified), even though the database contains its DTCs.

Q3. (True/False) SaveChanges wraps its inserts and updates in a transaction.

Answer: True. Q4. (Find the bug) foreach (var e in db.Ecus.ToList()) Console.WriteLine(e.Dtcs.Count(d => d.Active)); with lazy loading enabled.

Answer: N+1 queries (one per ECU). Use a projection: db.Ecus.Select(e => new { e.Code, Active = e.Dtcs.Count(d => d.Active) }).

Coding Challenge

With EF Core and SQLite in memory, model TestRun (Id, Name, Passed), add three runs, and print the pass rate computed by the database, together with the SQL of the query.

#:package [email protected]
#:property PublishAot=false
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

using var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();                                   // an in-memory database lives as long as its connection
var options = new DbContextOptionsBuilder<BenchContext>().UseSqlite(connection).Options;

using var db = new BenchContext(options);
db.Database.EnsureCreated();
db.Runs.AddRange(new TestRun { Name = "FlashVcu", Passed = true },
                 new TestRun { Name = "ReadDtcs", Passed = true },
                 new TestRun { Name = "CanLoad", Passed = false });
db.SaveChanges();

var query = db.Runs.Select(r => r.Passed ? 1.0 : 0.0);
Console.WriteLine(query.ToQueryString());
Console.WriteLine($"pass rate: {query.Average():P0}");

public class TestRun { public int Id { get; set; } public required string Name { get; set; } public bool Passed { get; set; } }
public class BenchContext(DbContextOptions<BenchContext> options) : DbContext(options)
{
    public DbSet<TestRun> Runs => Set<TestRun>();
}