07.1 - EF Core: Relationships, Migrations, Querying

Recap

06.1 - EF Core Intro gave you DAL.EF with two entities, an AppDbContext on a SQLite file under the user home folder, the first migration and EfConfigRepository. This lecture finishes the A4 data model: how Configuration and SavedGame are related and configured, how the schema evolves through migrations without losing data, how queries and change tracking really work, and how the game state itself should be stored.

By the end of this lecture you should be able to:

• Configure keys, required/optional properties, lengths, indexes and default values with annotations or the Fluent API.
• Model the one-to-many Configuration → SavedGame relationship and choose a delete behaviour on purpose.
• Run the migrations workflow (add, remove, update, drop) and explain why an applied migration is never edited.
• Query with LINQ-to-entities: filters, projections, Include, AsNoTracking, and update or delete through change tracking.
• Store game state in a JSON column, keep ids stable between JSON files and the database, and seed preset configurations.

Demo code

Lecture demos: csharp-2026-fall

Configuring entities

EF Core builds the model from three sources, in increasing priority: conventions (names and types), data annotations (attributes on the class), Fluent API (code in OnModelCreating). Use conventions where they do the right thing, annotations for simple per-property facts, Fluent API for everything else.

Concern	Convention	Annotation	Fluent API
Primary key	Id or ConfigurationId	[Key]	.HasKey(c => c.Id)
Composite key	—	—	.HasKey(m => new { m.SavedGameId, m.Number })
Required	non-nullable type	[Required]	.Property(c => c.Name).IsRequired()
Max length	provider default	[MaxLength(128)]	.Property(c => c.Name).HasMaxLength(128)
Index	on every FK	[Index(nameof(Name), IsUnique = true)]	.HasIndex(c => c.Name).IsUnique()
Default value	—	—	.Property(c => c.WinLength).HasDefaultValue(4)
Exclude	getter-only properties	[NotMapped]	.Ignore(c => c.Something)
Column name / type	property name	[Column("cfg_name")]	.HasColumnName("cfg_name")

Some of that in one place:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Configuration>(e =>
    {
        e.HasIndex(c => c.Name).IsUnique();
        e.Property(c => c.Name).HasMaxLength(128);
    });

    modelBuilder.Entity<SavedGame>(e =>
    {
        e.Property(g => g.Id).ValueGeneratedNever();          // the engine creates the Guid, not the DB
        e.Property(g => g.CreatedAtUtc).HasDefaultValueSql("CURRENT_TIMESTAMP");
        e.Ignore(g => g.DisplayName);                            // computed in C#, not a column
    });
}

Required vs optional follows nullability: string Name is required, string? Note is optional, int ConfigurationId is a required foreign key, int? ConfigurationId an optional one. With nullable reference types on, your C# types are the schema — one more reason to keep nullable-as-errors.

Default values are Fluent-only. HasDefaultValue is a constant, HasDefaultValueSql is a database expression — CURRENT_TIMESTAMP in SQLite. Remember the default applies only when EF sends no value; a C# initializer like = DateTime.UtcNow on the property is usually simpler and works for both JSON and EF.

Relationships

A4 has one relationship: a configuration has many saved games, every saved game belongs to exactly one configuration.

A fully defined relationship has a navigation property on both ends and an explicit foreign-key property on the dependent side. Give EF all three and it needs no configuration:

public class Configuration
{
    public int Id { get; set; }
    // ...
    public ICollection<SavedGame> SavedGames { get; set; } = new List<SavedGame>();
}

public class SavedGame
{
    public Guid Id { get; set; }
    // ...
    public int ConfigurationId { get; set; }           // FK column — convention: <Navigation>Id
    public Configuration? Configuration { get; set; }  // reference navigation
}

When conventions are not enough

[ForeignKey] — the FK property does not follow the naming convention:

public int ConfigId { get; set; }

[ForeignKey(nameof(ConfigId))]
public Configuration? Configuration { get; set; }

[InverseProperty] — two relationships between the same two entities, so EF cannot pair the navigations. If SavedGame had two players from a Player table:

public class SavedGame
{
    public int PlayerXId { get; set; }
    public Player? PlayerX { get; set; }
    public int PlayerOId { get; set; }
    public Player? PlayerO { get; set; }
}

public class Player
{
    public int Id { get; set; }
    [MaxLength(64)] public string Name { get; set; } = default!;

    [InverseProperty(nameof(SavedGame.PlayerX))]
    public ICollection<SavedGame> GamesAsX { get; set; } = new List<SavedGame>();

    [InverseProperty(nameof(SavedGame.PlayerO))]
    public ICollection<SavedGame> GamesAsO { get; set; } = new List<SavedGame>();
}

Fluent API says the same thing starting from either end — HasOne / HasMany names the navigation on the entity you configure, WithMany / WithOne the inverse:

modelBuilder.Entity<SavedGame>()
    .HasOne(g => g.Configuration)
    .WithMany(c => c.SavedGames)
    .HasForeignKey(g => g.ConfigurationId)
    .OnDelete(DeleteBehavior.Restrict);

Cascade delete

Defaults: required relationships cascade (deleting a configuration deletes its saved games), optional ones set the FK to null. Decide, do not inherit:

• Cascade — deleting a preset silently destroys every game played on it. Probably not what the user meant.
• Restrict — the delete fails with an exception while saved games exist; the UI tells the user to delete the games first. Predictable.
• Make the FK optional (int? ConfigurationId) with SetNull — the games survive without a configuration. Then your load code must handle the null.

For A4 Restrict plus a clear message is the sane choice. To turn cascading off everywhere at once:

foreach (var fk in modelBuilder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
{
    fk.DeleteBehavior = DeleteBehavior.Restrict;
}

Migrations workflow

Every model change goes through a migration; the database is never edited by hand. All commands take --project DAL.EF --startup-project ConsoleUI (omitted below).

dotnet ef migrations add AddSavedGameName   # 1. generate the diff against the snapshot
dotnet ef migrations list                   # 2. see what is pending
dotnet ef database update                   # 3. apply — or let Migrate() do it at startup
dotnet ef migrations remove                 # undo step 1 if the migration is NOT applied yet
dotnet ef database update InitialCreate     # roll the DATABASE back to a named migration
dotnet ef database drop                     # throw the file away (development only)
dotnet ef migrations script                 # SQL for review or manual deployment

The lifecycle:

1. Change entities or OnModelCreating.
2. migrations add with a name that says what changed. Read the generated Up().
3. Apply. If it is wrong and not yet applied → migrations remove, fix the model, add again.
4. If it is applied and wrong → add another migration that corrects it. Never edit a migration that has run anywhere — the snapshot, the history table and someone else's database all disagree with your edit.
5. Commit the Migrations folder. It is source code.

Read the data-loss warning

migrations add prints "An operation was scaffolded that may result in the loss of data" when a column or table is dropped. Renaming a property looks like drop + add to EF. If the data matters, edit the migration before applying it: use migrationBuilder.RenameColumn, or migrationBuilder.Sql("UPDATE ...") to move data before the drop.

SQLite cannot ALTER most things; EF Core handles that by rebuilding the table inside the migration (create new, copy, drop old, rename). It works, but read the generated code when a migration on SQLite looks unexpectedly long.

During A4 development, dropping the database and re-running Migrate() is often faster than fixing a chain of experimental migrations — but squash them into one clean InitialCreate before the D2 defense.

Existing database

The reverse direction exists too: dotnet ef dbcontext scaffold "Data Source=existing.db" Microsoft.EntityFrameworkCore.Sqlite --output-dir Entities generates entity classes and a context from an existing schema. Not needed in this course, since the model is ours.

Querying

LINQ against a DbSet builds an expression tree that the provider translates to SQL. The query runs when you enumerate.

using var db = new AppDbContext();

// filter + sort + execute
var recent = db.SavedGames
    .Where(g => g.ConfigurationId == configId)
    .OrderByDescending(g => g.UpdatedAtUtc)
    .Take(10)
    .ToList();

// single rows
var byKey = db.SavedGames.Find(id);                                  // PK lookup, checks the tracker first, null if missing
var first = db.Configurations.First(c => c.Name == "Classic");       // throws if none
var maybe = db.Configurations.FirstOrDefault(c => c.Name == name);   // null if none
var exactlyOne = db.Configurations.SingleOrDefault(c => c.Name == name); // throws if MORE than one — good for unique columns

// aggregates
var count = db.SavedGames.Count(g => g.ConfigurationId == configId);
var exists = db.Configurations.Any(c => c.Name == name);

Projections

Select loads only the columns you name. IGameRepository.List() wants an id, a name and a timestamp — not the whole JSON blob:

public List<(Guid Id, string Name, DateTime SavedAtUtc)> List()
{
    using var db = new AppDbContext();
    return db.SavedGames
        .OrderByDescending(g => g.UpdatedAtUtc)
        .Select(g => new { g.Id, g.Name, g.UpdatedAtUtc })   // translated: SELECT Id, Name, UpdatedAtUtc
        .AsEnumerable()                                       // from here on: plain LINQ-to-objects
        .Select(x => (x.Id, x.Name, x.UpdatedAtUtc))
        .ToList();
}

Tuple literals cannot appear in expression trees, hence the anonymous type first and the tuple after AsEnumerable(). The SQL is a three-column SELECT; the JSON column never leaves the database.

Loading related data

Navigation properties are null (or empty) unless you load them.

Eager — Include becomes a JOIN; ThenInclude goes a level deeper; filtered includes take Where / OrderBy / Take:

var config = db.Configurations
    .Include(c => c.SavedGames.OrderByDescending(g => g.UpdatedAtUtc).Take(5))
    .First(c => c.Id == configId);

var game = db.SavedGames
    .Include(g => g.Configuration)
    .First(g => g.Id == id);

Explicit — load a navigation later, on an entity you already have:

db.Entry(config).Collection(c => c.SavedGames).Load();
db.Entry(game).Reference(g => g.Configuration).Load();

Lazy — with the Microsoft.EntityFrameworkCore.Proxies package and virtual navigations, EF loads a navigation the moment you touch it. Every access is a query; listing 100 games and printing each configuration's name is 101 queries. Not used in this course.

N+1

foreach (var g in db.SavedGames.ToList())
    Console.WriteLine(db.Configurations.First(c => c.Id == g.ConfigurationId).Name);   // one query per row

Turn on SQL logging (lecture 06.1) and watch. The fix is one Include or one projection with g.Configuration!.Name.

Tracking, updating, deleting

Entities returned by a query are tracked: the context keeps a snapshot, and SaveChanges() compares and writes UPDATE statements for whatever changed.

using var db = new AppDbContext();

var game = db.SavedGames.First(g => g.Id == id);
game.Name = "Rematch";
game.StateJson = JsonSerializer.Serialize(state, JsonOptions);
game.UpdatedAtUtc = DateTime.UtcNow;
db.SaveChanges();                        // UPDATE SavedGames SET Name = ..., StateJson = ..., UpdatedAtUtc = ... WHERE Id = ...

db.SavedGames.Remove(game);
db.SaveChanges();                        // DELETE

// set-based, no loading, no tracking (EF Core 7+)
db.SavedGames.Where(g => g.UpdatedAtUtc < cutoff).ExecuteDelete();
db.SavedGames.Where(g => g.Id == id).ExecuteUpdate(s => s.SetProperty(g => g.Name, "Rematch"));

Read-only lists do not need tracking. AsNoTracking() skips the snapshot, is faster, and makes it obvious the result is not meant to be modified:

var names = db.Configurations.AsNoTracking().OrderBy(c => c.Name).ToList();

Update(entity) marks every property of a detached object as modified — useful when you get an object from outside the context, wasteful otherwise. Find plus targeted property changes writes less.

Async

Every terminal operation has an async twin: ToListAsync, FirstOrDefaultAsync, AnyAsync, CountAsync, FindAsync, SaveChangesAsync, MigrateAsync.

var games = await db.SavedGames.AsNoTracking().OrderByDescending(g => g.UpdatedAtUtc).ToListAsync();
await db.SaveChangesAsync();

The console app may stay synchronous — the repository interface is synchronous and nothing is waiting. The web app in A6 must not block a request thread on the database, so it will use the async versions. One rule either way: one operation at a time per context — await each call; never start two queries on the same DbContext concurrently.

Storing game state

The engine's GameState is a jagged board, a next-player marker, a move list, a configuration and timestamps. How does that become rows?

Option	Schema	Pros	Cons
JSON column	SavedGame.StateJson holds the serialized GameState	one row per game, same serializer as DAL.Json, trivial round-trip, any game type	cannot query inside the state with SQL
Normalized moves	Move table (SavedGameId, Number, Row, Col, Piece); the board is replayed from moves	full history, undo, statistics, queries like "longest game"	replay needs the engine at load time, and multi-step moves (Morris, grid moves) need a richer row
Both	JSON snapshot for fast load, Move rows for history	best of both	two things to keep in sync on every save

For A4 the JSON column is enough and is what the sample entity uses. GameState already round-trips through System.Text.Json in A3 — reuse the very same JsonSerializerOptions (put them in GameEngine as GameStateJson.Options, referenced by both DALs) so that a file and a row contain byte-identical JSON. A normalized Move table is a solid bonus, and the A5 AI benefits from a move history anyway.

Keep ids stable

Cross-play (console → web, JSON → database) only works if a game keeps its identity:

• GameState.Id is a Guid created by the engine when a new game starts — not by the DB, not by the file name. SavedGame.Id stores the same value; ValueGeneratedNever() tells EF to never generate one.
• The JSON file is ~/icd0008/saves/{Id}.json; the row is WHERE Id = @id. IGameRepository.Get(Guid id) works identically against both.
• Save is an upsert keyed on the id: exists → update StateJson and UpdatedAtUtc; new → insert. Same semantics as overwriting the file.
• Configurations are keyed by name in the interface (Get(string name)); the int Id in the table is an implementation detail that never leaves DAL.EF. Store Config inside StateJson as well, so a loaded game is playable even if the preset was edited later.

public void Save(GameState state)
{
    using var db = new AppDbContext();

    var configId = db.Configurations
        .Where(c => c.Name == state.Config.Name)
        .Select(c => c.Id)
        .FirstOrDefault();
    if (configId == 0) throw new KeyNotFoundException($"Configuration '{state.Config.Name}' is not saved.");

    var row = db.SavedGames.Find(state.Id);
    if (row is null)
    {
        row = new SavedGame { Id = state.Id, CreatedAtUtc = state.CreatedAtUtc };
        db.SavedGames.Add(row);
    }

    row.Name = $"{state.Config.Name} {state.CreatedAtUtc:yyyy-MM-dd HH:mm}";
    row.ConfigurationId = configId;
    row.StateJson = JsonSerializer.Serialize(state, GameStateJson.Options);
    row.UpdatedAtUtc = DateTime.UtcNow;

    db.SaveChanges();
}

DateTime and UTC

SQLite has no date type; EF stores DateTime as ISO-8601 text and reads it back with DateTimeKind.Unspecified. Rules:

• Always write DateTime.UtcNow, never DateTime.Now. Name the properties ...Utc so nobody has to guess.
• Convert to local time only when printing: game.UpdatedAtUtc.ToLocalTime().
• If you need the Kind back after loading, a value converter fixes it once for every property of the entity:

modelBuilder.Entity<SavedGame>()
    .Property(g => g.UpdatedAtUtc)
    .HasConversion(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

Seeding preset configurations

The syllabus requires presets that exist on first run. Three options:

• HasData in OnModelCreating — the rows become part of a migration (INSERT in Up()). Requires explicit primary keys; changing a preset means another migration. Fine for truly static data.
• UseSeeding on the options builder (EF Core 9+) — a callback that runs inside Migrate() / EnsureCreated(); you write ordinary code and check what already exists.
• At startup, in Program.cs — after Migrate(), insert whatever is missing through the repository. The same list of presets then serves DAL.Json too.

The third keeps a single source of truth for both DALs, which is exactly the "identical behaviour" A4 asks for:

// GameEngine
public static class Presets
{
    public static readonly IReadOnlyList<GameConfiguration> All =
    [
        new("Classic", 7, 6, 4),
        new("Connect3", 5, 4, 3),
        new("Connect5", 9, 7, 5),
        new("Cylinder", 7, 6, 4, IsCylinder: true),
    ];
}

// ConsoleUI/Program.cs
using (var db = new AppDbContext())
{
    db.Database.Migrate();
}

IConfigRepository configRepository = new EfConfigRepository();
foreach (var preset in Presets.All.Where(p => !configRepository.List().Contains(p.Name)))
{
    configRepository.Save(preset);
}

Whichever way you seed, presets are still configurations: the user may copy one and edit the copy, and the validation from lecture 05.2 applies to the result.

Self preparation QA

Be prepared to explain topics like these:

1. What are the three sources of model configuration and which wins? — Conventions, data annotations, Fluent API — in that order of increasing priority; Fluent API in OnModelCreating overrides everything.
2. What makes a relationship "fully defined" and why bother? — Navigation properties on both ends plus an explicit foreign-key property on the dependent; EF then needs no configuration and you can set ConfigurationId without loading the Configuration.
3. What happens by default when you delete a Configuration with saved games, and what should you do about it? — The required relationship cascades and deletes the games; choose DeleteBehavior.Restrict (or an optional FK with SetNull) explicitly and give the user a message.
4. Why must you never edit a migration that has been applied? — The database's history table, the model snapshot and other machines already reflect the old version; add a new migration that corrects the schema instead.
5. What is the difference between Include, explicit loading and lazy loading? — Include joins related data in the same query; explicit loading fetches a navigation later via Entry(...).Load(); lazy loading fetches on first access and causes N+1 queries, so it is not used here.
6. When do you use AsNoTracking()? — For read-only queries such as lists and projections; it skips the change-tracking snapshot, is faster, and signals that the objects will not be saved back.
7. Why store the game state as a JSON column in A4? — It reuses the A3 serializer, round-trips any game type in one row, and keeps file and row content identical; a normalized Move table adds history and queries at the cost of replaying moves on load.
8. How do a JSON file and a database row refer to the same game? — The engine assigns GameState.Id (a Guid) at game start; the file name and the primary key both use it, ValueGeneratedNever() stops EF from generating its own, and Save upserts on that id.