06.1 - Entity Framework Core Intro

Recap

A3 stores configurations and game states as JSON files behind IConfigRepository and IGameRepository from 04.2 - Files & Persistence, and 05.2 - Exceptions, Debugging, Code Quality made sure the UI depends on those interfaces rather than on DAL.Json. A4 implements the same interfaces a second time — against a relational database. This lecture sets up the tooling, the entities and the first migration; the next one goes deeper into relationships, migrations and querying.

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

Explain what an ORM does and why this course uses SQLite.
Create the DAL.EF project with the right packages and install the dotnet ef tool.
Write nullable-safe entity classes and an AppDbContext that opens a SQLite file under the user's home folder.
Create and apply the first migration, and run Database.Migrate() at startup.
Add, query and update rows, see the generated SQL, and browse the database in Rider.

Demo code

Lecture demos: csharp-2026-fall

What is an ORM

A relational database stores rows in tables; your program works with objects in memory. An Object-Relational Mapper translates between the two: a class becomes a table, a property becomes a column, an object reference becomes a foreign key, and a LINQ query becomes SQL.

Entity Framework Core is Microsoft's ORM for .NET. It is open source, cross-platform and supports many engines through providers: SQLite, PostgreSQL, MySQL/MariaDB, SQL Server, and more. Your code stays the same; the provider changes the SQL dialect.

With EF Core you:

describe the data as plain C# classes (entities),
describe the database session as a DbContext with one DbSet<T> per table,
generate the SQL that creates and changes the schema (migrations),
query with LINQ and save with SaveChanges().

What you do not get: freedom from understanding the database. Keys, indexes, foreign keys and the SQL EF generates for you are still your responsibility — check them.

Why SQLite

A database is a single file; nothing to install, nothing to start, nothing to configure on the TA's laptop.
Full SQL, transactions, indexes, foreign keys — enough for this course and for most small applications.
The same EF Core code moves to PostgreSQL by changing the provider package and the connection string.

Limitations you will meet: some schema changes need a table rebuild (EF handles it), no real DateTime type (stored as text), and only one writer at a time.

Projects and packages

dotnet new classlib -n DAL.EF
dotnet sln add DAL.EF
dotnet add DAL.EF reference GameEngine
dotnet add DAL.EF package Microsoft.EntityFrameworkCore.Sqlite
dotnet add ConsoleUI reference DAL.EF
dotnet add ConsoleUI package Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Sqlite — the provider; it pulls in EF Core itself. Goes into DAL.EF.
Microsoft.EntityFrameworkCore.Design — design-time support for the dotnet ef tool. It must be referenced by the startup project (ConsoleUI); it does not flow transitively. Missing it produces the error "Your startup project doesn't reference Microsoft.EntityFrameworkCore.Design".
DAL.EF references GameEngine because it implements the repository interfaces defined there and maps to and from GameConfiguration / GameState. GameEngine never references DAL.EF.

The `dotnet ef` tool

Migrations are created with a global CLI tool that is not part of the SDK:

dotnet tool install --global dotnet-ef
dotnet tool update --global dotnet-ef       # later
dotnet ef --version

Versions must match

The tool's major version must match the EF Core packages in the project — EF Core 10 packages need dotnet-ef 10.x. A tool from last year against this year's packages fails with a confusing "could not load assembly" message. dotnet tool list --global shows what you have.

Entities

An entity is a plain class with a primary key. Conventions do most of the work: a property named Id (or ConfigurationId) is the key; int and Guid keys are generated automatically; non-nullable properties become NOT NULL columns; a property of another entity type becomes a foreign key.

using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace DAL.EF;

[Index(nameof(Name), IsUnique = true)]
public class Configuration
{
    public int Id { get; set; }

    [MaxLength(128)]
    public string Name { get; set; } = default!;

    public int BoardWidth { get; set; }
    public int BoardHeight { get; set; }
    public int WinLength { get; set; }

    public ICollection<SavedGame> SavedGames { get; set; } = new List<SavedGame>();
}

public class SavedGame
{
    public Guid Id { get; set; }

    [MaxLength(128)]
    public string Name { get; set; } = default!;

    public string StateJson { get; set; } = default!;

    public int ConfigurationId { get; set; }
    public Configuration? Configuration { get; set; }

    public DateTime CreatedAtUtc { get; set; }
    public DateTime UpdatedAtUtc { get; set; }
}

Read it with nullable reference types in mind:

string Name = default! — the column is NOT NULL; the = default! only silences the compiler because EF sets the value when it loads the row. Nullable string? would create a nullable column.
Configuration? Configuration — a navigation property. Nullable because it is only populated when you ask for it (Include, next lecture). The foreign key ConfigurationId is int, not int?, so the relationship itself is required.
ICollection<SavedGame> SavedGames = new List<SavedGame>() — the other side of the relationship, initialised so you can Add to it without null checks.
StateJson — the whole GameState serialized with the same System.Text.Json options DAL.Json uses. Why a JSON column rather than a Move table is discussed in the next lecture.

Entities are not domain objects

Configuration is a table row with an int key and a navigation collection. GameConfiguration is the engine's immutable record. Keep them separate and map in the repository — the engine must not know EF exists, and EF needs settable properties and a parameterless constructor that a record with positional parameters does not have.

DbContext

The context is the database session: it tracks the entities you load, translates queries and writes changes. One DbSet<T> per table.

using Microsoft.EntityFrameworkCore;

namespace DAL.EF;

public class AppDbContext : DbContext
{
    public DbSet<Configuration> Configurations { get; set; } = default!;
    public DbSet<SavedGame> SavedGames { get; set; } = default!;

    public AppDbContext()
    {
    }

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (optionsBuilder.IsConfigured) return;

        optionsBuilder.UseSqlite(DefaultConnectionString());
    }

    public static string DefaultConnectionString()
    {
        var folder = Path.Combine(
            Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),
            "icd0008");
        Directory.CreateDirectory(folder);
        return $"Data Source={Path.Combine(folder, "app.db")}";
    }
}

Two constructors. The one taking DbContextOptions is what dependency injection (Week 8) and tests will use. The parameterless one lets dotnet ef and today's Program.cs create the context with the defaults from OnConfiguring.
IsConfigured guards against overriding options that were passed in from outside.
The connection string points at ~/icd0008/app.db — the same user-home folder convention as the JSON saves. Never a path like C:\Users\me\... or /Users/me/... in code; the TA's machine is not yours. Directory.CreateDirectory is idempotent, so it is fine to call on every startup.

The full set of options for later: .LogTo(...), .EnableDetailedErrors(), .EnableSensitiveDataLogging() (shows parameter values in the log — development only).

The first migration

EF Core does not create tables on its own. A migration is generated C# that describes the difference between the last snapshot of the model and the model now, in both directions.

From the solution folder:

dotnet ef migrations add InitialCreate --project DAL.EF --startup-project ConsoleUI

--project is where the DbContext and the Migrations folder live; --startup-project is the executable the tool builds and runs to discover the context (it needs the Design package). Three files appear in DAL.EF/Migrations:

20261005120000_InitialCreate.cs — Up() creates tables, Down() drops them.
20261005120000_InitialCreate.Designer.cs — metadata for the tool.
AppDbContextModelSnapshot.cs — the model as of this migration; the next migrations add diffs against it.

migrationBuilder.CreateTable(
    name: "Configurations",
    columns: table => new
    {
        Id = table.Column<int>(type: "INTEGER", nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
        Name = table.Column<string>(type: "TEXT", maxLength: 128, nullable: false),
        BoardWidth = table.Column<int>(type: "INTEGER", nullable: false),
        // ...
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Configurations", x => x.Id);
    });

Read the generated code once. Is Name NOT NULL? Is there a unique index? Is the foreign key there? What you see is what your database becomes.

Apply it:

dotnet ef database update --project DAL.EF --startup-project ConsoleUI

The file ~/icd0008/app.db now exists with three tables: Configurations, SavedGames and __EFMigrationsHistory (EF's record of which migrations have been applied).

Migrate at startup

Asking the TA to run dotnet ef database update is not acceptable. The application applies pending migrations itself, once, at startup:

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

Migrate() creates the file if it does not exist and applies every migration not yet in __EFMigrationsHistory. A fresh clone plus dotnet run gives a working database.

Do not mix EnsureCreated() with migrations

Database.EnsureCreated() builds the schema straight from the model without recording any migration. The next Migrate() then tries to create tables that already exist. Use migrations from day one and only Migrate().

Other commands you will need soon — all take the same --project / --startup-project pair:

dotnet ef migrations list
dotnet ef migrations remove          # delete the last migration, if it is not applied yet
dotnet ef database drop              # delete the database file — cheap during development

Browsing the database in Rider

Rider ships the DataGrip database tool.

View → Tool Windows → Database.
+ → Data Source → SQLite. Set File to ~/icd0008/app.db (Rider expands ~). Click Download missing driver files the first time, then Test Connection.
Expand the data source: tables, columns, indexes and foreign keys. Double-click a table to see its rows; edit a cell and submit to change data by hand.
Open a Query Console on the data source and run SQL directly:

SELECT Name, BoardWidth, BoardHeight, WinLength FROM Configurations;
SELECT Id, Name, ConfigurationId, UpdatedAtUtc FROM SavedGames ORDER BY UpdatedAtUtc DESC;
SELECT * FROM __EFMigrationsHistory;

Check after every migration that the schema matches the model 1:1 — column types, nullability, the unique index on Configurations.Name, the foreign key on SavedGames.ConfigurationId. Automatic conventions are convenient until they guess wrong.

Add, query, update

using DAL.EF;
using Microsoft.EntityFrameworkCore;

using var db = new AppDbContext();
db.Database.Migrate();

// Create
if (!db.Configurations.Any(c => c.Name == "Classic"))
{
    db.Configurations.Add(new Configuration
    {
        Name = "Classic", BoardWidth = 7, BoardHeight = 6, WinLength = 4
    });
    db.SaveChanges();
}

// Read
var all = db.Configurations
    .OrderBy(c => c.Name)
    .ToList();

foreach (var c in all)
{
    Console.WriteLine($"{c.Id}: {c.Name} {c.BoardWidth}x{c.BoardHeight} / {c.WinLength}");
}

var classic = db.Configurations.First(c => c.Name == "Classic");

// Update — change a tracked entity, then save
classic.WinLength = 5;
db.SaveChanges();

// Delete
db.Configurations.Remove(classic);
db.SaveChanges();

The pattern is always the same: query or add through the DbSet, change the objects, call SaveChanges(). Until SaveChanges() nothing reaches the database. The context remembers every entity it handed you (change tracking) and works out the UPDATE statements itself.

Any, OrderBy, First, Where are LINQ — but this LINQ is translated to SQL and runs in the database. ToList(), First(), Count(), foreach are the points where the query actually executes.

A `using` per unit of work

DbContext is cheap to create and not thread-safe. Create one per operation (one menu action, one repository call), let using dispose it. Do not keep a single context alive for the whole program run — it would track every entity ever loaded and hand you stale data.

Logging the SQL

Seeing the SQL is the fastest way to learn what EF actually does — and to notice when a loop sends 200 queries.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (optionsBuilder.IsConfigured) return;

    optionsBuilder
        .UseSqlite(DefaultConnectionString())
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging();   // shows parameter values — never in production
}

Output for the First above:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__name_0='Classic' (Size = 7)], CommandType='Text']
      SELECT "c"."Id", "c"."BoardHeight", "c"."BoardWidth", "c"."Name", "c"."WinLength"
      FROM "Configurations" AS "c"
      WHERE "c"."Name" = @__name_0
      LIMIT 1

In a console game the log scrolls over the board; write it to a file (LogTo(message => File.AppendAllText(logPath, message + Environment.NewLine))) or turn it on only while investigating.

Towards the repository

The interfaces do not change. EfConfigRepository maps the record to the entity and back; the interesting part is that Save is an upsert — insert if the name is new, update otherwise — exactly like the JSON version overwrote the file:

public class EfConfigRepository : IConfigRepository
{
    public List<string> List()
    {
        using var db = new AppDbContext();
        return db.Configurations.OrderBy(c => c.Name).Select(c => c.Name).ToList();
    }

    public GameConfiguration Get(string name)
    {
        using var db = new AppDbContext();
        var e = db.Configurations.FirstOrDefault(c => c.Name == name)
                ?? throw new KeyNotFoundException($"Configuration '{name}' not found.");
        return new GameConfiguration(e.Name, e.BoardWidth, e.BoardHeight, e.WinLength);
    }

    public void Save(GameConfiguration config)
    {
        using var db = new AppDbContext();
        var e = db.Configurations.FirstOrDefault(c => c.Name == config.Name)
                ?? db.Configurations.Add(new Configuration { Name = config.Name }).Entity;
        e.BoardWidth = config.BoardWidth;
        e.BoardHeight = config.BoardHeight;
        e.WinLength = config.WinLength;
        db.SaveChanges();
    }

    public void Delete(string name)
    {
        using var db = new AppDbContext();
        db.Configurations.Where(c => c.Name == name).ExecuteDelete();
    }
}

The round-trip tests from lecture 05.1 should now pass against both repositories. IGameRepository follows in the next lecture, once we have looked at how SavedGame relates to Configuration and how the state is stored.

Common errors

Message	Cause
No project was found. Change the current working directory or use the --project option.	Run from the solution folder with `--project DAL.EF --startup-project ConsoleUI`.
Your startup project doesn't reference Microsoft.EntityFrameworkCore.Design	Add the Design package to `ConsoleUI`.
Unable to create a 'DbContext' of type 'AppDbContext'	No parameterless constructor and no options — keep the parameterless one, or configure DI in `Program.cs` (Week 8).
SQLite Error 1: 'no such table: Configurations'	Migration not applied — `Database.Migrate()` at startup, or `dotnet ef database update`.
The model for context has pending changes	You changed an entity and did not add a migration. `dotnet ef migrations add ...`.
Tool and package version mismatch	`dotnet tool update --global dotnet-ef`; majors must be equal.

Self preparation QA

Be prepared to explain topics like these:

What does an ORM do, and what does it not do for you? — It maps classes to tables, properties to columns and LINQ to SQL; it does not relieve you of understanding keys, indexes, relationships and the SQL it generates.
Which project gets Microsoft.EntityFrameworkCore.Sqlite and which gets ...Design, and why? — Sqlite (the provider) in DAL.EF where the context lives; Design in the startup project ConsoleUI, because dotnet ef builds and runs the startup project to find the context and the package does not flow transitively.
Why is = default! on a string property acceptable in an entity? — The column is NOT NULL and EF always fills the property when loading, so the null-forgiving initializer only silences the nullable-as-errors build without making the column nullable.
What is the difference between ConfigurationId and Configuration on SavedGame? — ConfigurationId is the foreign-key column, always present; Configuration is a navigation property, null unless the related row was loaded with Include or is already tracked.
What are the three files a migration creates and what is the snapshot for? — The migration (Up/Down), its designer metadata, and the model snapshot that the next migrations add diffs against.
Why call Database.Migrate() at startup instead of EnsureCreated()? — Migrate() applies pending migrations incrementally and records them; EnsureCreated() creates the schema without migration history and breaks every later migration.
Where does the SQLite file live and why not a hardcoded path? — Under the user profile (Environment.SpecialFolder.UserProfile), e.g. ~/icd0008/app.db; a hardcoded absolute path does not exist on the TA's machine and fails the "builds and runs from a fresh clone" requirement.
When does a LINQ query against a DbSet actually hit the database? — On enumeration: ToList(), First(), Count(), Any(), foreach; Where / OrderBy / Select only build the query.

Recap​

What is an ORM​

Why SQLite​

Projects and packages​

The dotnet ef tool​

Entities​

DbContext​

The first migration​

Migrate at startup​

Browsing the database in Rider​

Add, query, update​

A using per unit of work​

Logging the SQL​

Towards the repository​

Common errors​

Self preparation QA​