Scaffold DbContext and Entities from Existing Schemas with Provider Tooling

Learning outcomes

1. The practical problem: the schema exists, but the EF model does not

A ServiceHub acquisition delivers a small SQLite database created by another application. Operations needs the data inside the existing work_orders, technicians, and reporting objects, but there is no C# model, no EF migrations history, and no guarantee that column names reflect the language the new application should expose. Database-first work begins from that reality: the database schema is input, and generated EF code is a derived artifact.

EF Core calls this process reverse engineering or scaffolding. The provider reads schema metadata—tables, views, columns, keys, foreign keys, indexes, store types and certain provider-specific annotations—builds an EF IModel, and then emits a DbContext plus entity classes that can recreate that model in the application. The tool is not recovering intent that was never stored in the database.

2. Pin the design-time toolchain before reading the schema

dotnet ef dbcontext scaffold is a design-time command. The target project needs Microsoft.EntityFrameworkCore.Design, and it must reference the provider that understands the source database. Keep Microsoft-shipped EF packages on the same patch unless official guidance says otherwise. A package restore is not proof that a mismatched provider can interpret the schema correctly.

dotnet new classlib -n ServiceHub.LegacyScaffold -f net10.0cd ServiceHub.LegacyScaffolddotnet add package Microsoft.EntityFrameworkCore.Sqlite --version 10.0.11dotnet add package Microsoft.EntityFrameworkCore.Design --version 10.0.11dotnet new tool-manifestdotnet tool install dotnet-ef --version 10.0.11dotnet tool run dotnet-ef --versiondotnet --info

The version output is part of the reproducibility record. If a later re-scaffold changes hundreds of lines, first ask whether the database changed, the provider/tool changed, or both.

3. Build a disposable legacy database whose intent is partly hidden

The lab database deliberately encodes relational facts but not the richer ServiceHub domain semantics from earlier chapters. For example, revision is just a TEXT column in SQLite; nothing in SQLite schema metadata says “application-managed optimistic concurrency token.”

PRAGMA foreign_keys = ON;CREATE TABLE technicians (    technician_id INTEGER PRIMARY KEY,    display_name  TEXT NOT NULL,    email_address TEXT NULL UNIQUE);CREATE TABLE work_orders (    work_order_id       INTEGER PRIMARY KEY AUTOINCREMENT,    work_order_number   TEXT NOT NULL UNIQUE,    customer_name       TEXT NOT NULL,    summary             TEXT NOT NULL,    priority            INTEGER NOT NULL DEFAULT 2,    opened_utc          TEXT NOT NULL,    assigned_technician_id INTEGER NULL,    revision            TEXT NOT NULL,    CONSTRAINT fk_work_orders_technician      FOREIGN KEY (assigned_technician_id)      REFERENCES technicians(technician_id) ON DELETE SET NULL);CREATE INDEX ix_work_orders_priority ON work_orders(priority);CREATE VIEW open_work_order_summary ASSELECT work_order_id, work_order_number, customer_name, priorityFROM work_ordersWHERE priority >= 2;

sqlite3 servicehub-legacy.db < legacy-schema.sqlsqlite3 servicehub-legacy.db ".schema"sqlite3 servicehub-legacy.db "PRAGMA foreign_key_list('work_orders');"

If the sqlite3 CLI is unavailable, use any free SQLite shell/GUI or copy a disposable database created from the same DDL. The learning requirement is the schema, not a particular GUI.

4. Run the smallest correct scaffold command

The command has two required conceptual inputs: a connection string and the provider assembly. --no-onconfiguring prevents the scaffolder from placing the connection string in generated OnConfiguring code. Generated code goes to an explicitly named namespace and folder so it can be deleted/recreated safely.

dotnet tool run dotnet-ef dbcontext scaffold   "Data Source=servicehub-legacy.db"   Microsoft.EntityFrameworkCore.Sqlite   --context ServiceHubLegacyContext   --context-dir Persistence/Generated   --output-dir Persistence/Generated/Entities   --namespace ServiceHub.LegacyScaffold.Persistence.Generated.Entities   --context-namespace ServiceHub.LegacyScaffold.Persistence.Generated   --no-onconfiguring

Expected output is one context plus one class per scaffolded entity/view. Exact navigation names are generated names, not proof of domain terminology. Re-running with --force can overwrite generated files, so Lesson 3 will move all custom behavior elsewhere before using it.

5. Read the generated code as evidence, not authority

A simplified result resembles the following. The point is not the exact formatting—the T4 templates and provider version can change formatting—but the model facts EF reconstructed.

public partial class WorkOrder{    public long WorkOrderId { get; set; }    public string WorkOrderNumber { get; set; } = null!;    public string CustomerName { get; set; } = null!;    public string Summary { get; set; } = null!;    public long Priority { get; set; }    public string OpenedUtc { get; set; } = null!;    public long? AssignedTechnicianId { get; set; }    public string Revision { get; set; } = null!;    public virtual Technician? AssignedTechnician { get; set; }}

modelBuilder.Entity<WorkOrder>(entity =>{    entity.HasKey(e => e.WorkOrderId);    entity.ToTable("work_orders");    entity.HasIndex(e => e.WorkOrderNumber).IsUnique();    entity.HasIndex(e => e.Priority);    entity.Property(e => e.WorkOrderId).HasColumnName("work_order_id");    entity.Property(e => e.Priority).HasDefaultValue(2L).HasColumnName("priority");    entity.Property(e => e.Revision).HasColumnName("revision");    entity.HasOne(d => d.AssignedTechnician)        .WithMany(p => p.WorkOrders)        .HasForeignKey(d => d.AssignedTechnicianId)        .OnDelete(DeleteBehavior.SetNull);});

Notice how SQLite affinity can surface as broad CLR/store mappings. The tool has not rediscovered Chapter 06 value objects, Chapter 13 concurrency policy, or the domain method that advances Revision. Those semantics existed in application design, not in this SQLite schema.

6. Inspect the runtime model and compare it with the database

Do not stop at generated source. Build the scaffolded context with externally supplied options, then print model metadata. This verifies what the application will actually use after C# compilation.

var options = new DbContextOptionsBuilder<ServiceHubLegacyContext>()    .UseSqlite("Data Source=servicehub-legacy.db")    .LogTo(Console.WriteLine, LogLevel.Information)    .Options;await using var db = new ServiceHubLegacyContext(options);foreach (var entity in db.Model.GetEntityTypes().OrderBy(e => e.Name)){    Console.WriteLine($"ENTITY {entity.DisplayName()} table={entity.GetTableName()} key={entity.FindPrimaryKey()?.Properties.Count ?? 0}");    foreach (var property in entity.GetProperties())        Console.WriteLine($"  {property.Name} : {property.ClrType.Name} nullable={property.IsNullable} concurrency={property.IsConcurrencyToken}");}

SELECT name, type, sqlFROM sqlite_masterWHERE type IN ('table','view','index')ORDER BY type, name;PRAGMA table_info('work_orders');PRAGMA index_list('work_orders');PRAGMA foreign_key_list('work_orders');

If the model reports Revision concurrency=False, that is not a scaffold bug: the SQLite schema contains no metadata saying that text value is a concurrency token. In the model-first ServiceHub context, we deliberately configured it as one.

7. What reverse engineering can—and cannot—infer

The database is authoritative for the physical schema, but not necessarily for every application abstraction. Database-first engineering becomes sustainable when this distinction is explicit.

8. Deliberate failure: use the wrong provider

A common automation error is to copy the scaffold command and change only the connection string. The provider is not cosmetic. It contains the database metadata reader and type mappings.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 10.0.11dotnet tool run dotnet-ef dbcontext scaffold   "Data Source=servicehub-legacy.db"   Microsoft.EntityFrameworkCore.SqlServer   --context WrongProviderContext   --no-onconfiguring

Expected outcome: design-time connection/provider failure, not a usable SQL Server model of the SQLite file. Repair it by using Microsoft.EntityFrameworkCore.Sqlite for SQLite and by version-pinning the provider/tool. A successful package restore does not validate provider/database compatibility.

9. Lab: scaffold, query, verify, reset

1. Create servicehub-legacy.db from the supplied DDL.
2. Pin EF Core 10.0.11 design/provider/tool packages.
3. Scaffold into Persistence/Generated with --no-onconfiguring.
4. Compile the project and inspect db.Model.
5. Run a no-tracking query against WorkOrders and inspect generated SQL/log parameters.
6. Confirm the view is treated as a query source and the SQLite revision column is not magically a concurrency token.
7. Delete the disposable generated folder and database to prove the workflow is reproducible.

var highPriority = await db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority >= 2)    .OrderBy(w => w.WorkOrderId)    .Select(w => new { w.WorkOrderId, w.WorkOrderNumber, w.CustomerName })    .ToListAsync(cancellationToken);Console.WriteLine(db.WorkOrders.Where(w => w.Priority >= 2).ToQueryString());

ToQueryString() proves translation shape; it is not execution timing or a query plan. Database-first does not remove the evidence discipline established in Chapters 08–10.

10. Production judgment and bridge

Scaffolding is appropriate when an existing database schema is the integration contract or schema owner. Keep generated code isolated, version the exact tooling/provider, and test against the real provider when schema inference matters. Do not infer authorization, transaction boundaries, aggregate semantics, concurrency resolution, or performance intent from generated classes. If the database is shared by multiple applications, coordinate schema ownership explicitly before adding EF migrations.

The next lesson narrows the reverse-engineering boundary: selecting only the objects the application owns, preserving or normalizing names intentionally, choosing annotations versus Fluent API, and keeping credentials out of generated source.

Authoritative references

Reverse engineering is provider- and version-sensitive. Re-check these primary sources when regenerating the chapter or adapting it to another database engine.

• Reverse Engineering - EF Core — official workflow, options, generated-code behavior, and inference limits
• EF Core .NET CLI tools — dotnet-ef commands and design-time tooling
• SQLite EF Core provider — SQLite provider behavior and limitations
• Microsoft.EntityFrameworkCore.Design 10.0.11 — design-time package version used by the lab
• Microsoft.EntityFrameworkCore.Sqlite 10.0.11 — mandatory provider package
• dotnet-ef 10.0.11 — pinned CLI tool version