Create the First DbContext, Entity, Database Connection, and End-to-End Query

Learning outcomes

The ServiceHub team now has a verified .NET/EF toolchain, but no useful persistence path. This lesson connects all layers end to end: a CLR entity becomes EF model metadata, a DbContext uses a provider to reach SQLite, migrations create the relational schema, SaveChangesAsync persists a tracked entity, and a LINQ query becomes parameterized SQL at enumeration time.

1. The smallest useful model has four distinct layers

A CLR entity is an ordinary C# object whose state the application understands. An EF model describes how entity types, properties, keys, relationships, and relational facets map to a store. A DbContext is a short-lived unit-of-work object that owns model access, query services, and a change tracker. A provider supplies database-specific translation, type mapping, commands, migrations, and ADO.NET integration.

Those layers are related but not interchangeable. Adding a DbSet<WorkOrder> does not create a table. Creating a migration does not update a database until that migration is applied. Constructing a LINQ query does not normally send SQL until the query is executed. And a successful SQL command does not mean EF can ignore database constraints or transaction semantics.

2. Define the first entity and context

Keep the first model intentionally small. Later chapters add relationships, value objects, concurrency, query filters, and provider-specific mappings. Starting small makes it possible to see exactly which behavior comes from conventions and which behavior comes from explicit configuration.

namespace ServiceHub.EfLab.Domain;public enum WorkOrderPriority{    Low = 1,    Normal = 2,    High = 3}public sealed class WorkOrder{    public int Id { get; set; }    public string CustomerName { get; set; } = string.Empty;    public string Summary { get; set; } = string.Empty;    public WorkOrderPriority Priority { get; set; } = WorkOrderPriority.Normal;    public DateTimeOffset OpenedUtc { get; set; }}

using Microsoft.EntityFrameworkCore;using ServiceHub.EfLab.Domain;namespace ServiceHub.EfLab.Data;public sealed class ServiceHubContext(DbContextOptions<ServiceHubContext> options)    : DbContext(options){    public DbSet<WorkOrder> WorkOrders => Set<WorkOrder>();    protected override void OnModelCreating(ModelBuilder modelBuilder)    {        var workOrder = modelBuilder.Entity<WorkOrder>();        workOrder.ToTable("WorkOrders");        workOrder.HasKey(x => x.Id);        workOrder.Property(x => x.CustomerName)            .HasMaxLength(120)            .IsRequired();        workOrder.Property(x => x.Summary)            .HasMaxLength(400)            .IsRequired();    }}

By convention, Id is the primary key. Explicit configuration gives the important strings database facets instead of relying on unlimited text by accident. The enum is currently represented through its underlying integer mapping; later model-building lessons make conversions and provider types explicit.

3. Give both runtime code and design-time tooling a construction path

The application creates DbContextOptions<ServiceHubContext> with UseSqlite. Migrations tooling also needs to construct the context. A small IDesignTimeDbContextFactory<TContext> keeps this first console lab deterministic. Chapter 02 later compares dependency injection, factories, OnConfiguring, background-worker lifetimes, and pooling in depth.

using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Design;namespace ServiceHub.EfLab.Data;public sealed class ServiceHubContextFactory    : IDesignTimeDbContextFactory<ServiceHubContext>{    public ServiceHubContext CreateDbContext(string[] args)    {        var options = new DbContextOptionsBuilder<ServiceHubContext>()            .UseSqlite("Data Source=servicehub-lab.db")            .Options;        return new ServiceHubContext(options);    }}

This factory contains a non-secret local SQLite path. Do not copy this pattern with a production password embedded in source. Lesson 5 moves connection configuration outside code and establishes the course secret-handling rule.

4. Create and apply the first migration

A migration is source-controlled schema intent generated from the EF model and its snapshot. It must be reviewed before application. The first migration establishes the baseline rather than asking EF to create an untracked schema with EnsureCreated.

dotnet tool restoredotnet ef migrations add InitialCreate --project src/ServiceHub.EfLab --output-dir Data/Migrationsdotnet ef migrations list --project src/ServiceHub.EfLabdotnet ef migrations script 0 InitialCreate --project src/ServiceHub.EfLabdotnet ef database update --project src/ServiceHub.EfLab

Paths above assume commands run at the course solution root. If you are already inside src/ServiceHub.EfLab, omit the --project argument. The generated C# migration and model snapshot are review artifacts; do not edit the database manually and then assume the snapshot learned about the change.

CREATE TABLE "WorkOrders" (    "Id" INTEGER NOT NULL CONSTRAINT "PK_WorkOrders" PRIMARY KEY AUTOINCREMENT,    "CustomerName" TEXT NOT NULL,    "Summary" TEXT NOT NULL,    "Priority" INTEGER NOT NULL,    "OpenedUtc" TEXT NOT NULL);

The exact migration SQL is provider/version sensitive. The important mechanism is that EF migration operations are translated by the SQLite provider into SQLite DDL. A SQL Server provider would not emit this same SQL or type surface.

5. Insert a tracked entity and inspect its state transition

When Add is called, the work order becomes Added in the context's state manager. SaveChangesAsync detects pending changes, generates a provider command, executes it in the database, obtains any store-generated key, and normally leaves the entity Unchanged after a successful save.

using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.Logging;using ServiceHub.EfLab.Data;using ServiceHub.EfLab.Domain;var options = new DbContextOptionsBuilder<ServiceHubContext>()    .UseSqlite("Data Source=servicehub-lab.db")    .LogTo(Console.WriteLine, LogLevel.Information)    .Options;await using var db = new ServiceHubContext(options);var workOrder = new WorkOrder{    CustomerName = "Northwind Workshop",    Summary = "Replace vibration sensor on compressor C-14",    Priority = WorkOrderPriority.High,    OpenedUtc = DateTimeOffset.Parse("2026-08-27T00:00:00Z")};db.WorkOrders.Add(workOrder);Console.WriteLine(db.ChangeTracker.DebugView.ShortView);await db.SaveChangesAsync();Console.WriteLine($"Stored WorkOrder Id = {workOrder.Id}");Console.WriteLine(db.ChangeTracker.DebugView.ShortView);

Before saving, expect the debug view to identify the entity as Added. After the successful save, expect Unchanged and a generated positive key. Provider command logs should show a parameterized INSERT. Log formatting and parameter names are diagnostics, not a stable application API.

6. A LINQ query is a recipe until execution

DbSet<T> implements the queryable surface EF uses to build expression trees. The following Where, OrderByDescending, and Select calls create a query description. ToQueryString asks EF for diagnostic command text. ToListAsync is the terminal operation that actually executes the database query and materializes results.

var highPriority = db.WorkOrders    .Where(x => x.Priority == WorkOrderPriority.High)    .OrderByDescending(x => x.OpenedUtc)    .Select(x => new    {        x.Id,        x.CustomerName,        x.Summary    });Console.WriteLine(highPriority.ToQueryString());var rows = await highPriority.ToListAsync();foreach (var row in rows){    Console.WriteLine($"{row.Id}: {row.CustomerName} - {row.Summary}");}

SELECT "w"."Id", "w"."CustomerName", "w"."Summary"FROM "WorkOrders" AS "w"WHERE "w"."Priority" = 3ORDER BY "w"."OpenedUtc" DESC

Whether a value appears as a literal or parameter in ToQueryString depends on expression shape and provider behavior. Runtime command logs are the evidence for the executed command. Neither ToQueryString nor the SQL text is a database execution plan.

7. Materialization and identity tracking happen after rows return

For entity queries, EF reads provider result rows and materializes CLR objects. A tracking query then associates entity instances with the context's state manager. A projection to an anonymous type, as above, avoids constructing full tracked WorkOrder entities when the caller only needs three columns. This is an early example of query shape affecting both database work and EF materialization work.

Do not conclude that “projection is always faster” without workload evidence. It is appropriate when the application genuinely needs a smaller shape; later performance chapters measure translation, network payload, database plans, tracking, and allocation costs separately.

8. Database connections are not the same lifetime as DbContext

A context uses the provider's ADO.NET connection abstraction, but EF normally opens a closed connection immediately before an operation and closes it afterward. The context can remain alive across several commands without holding the physical connection open continuously. ADO.NET connection pooling, where supported, is another layer again and is not DbContext pooling.

Console.WriteLine(db.Database.GetDbConnection().State);var count = await db.WorkOrders.CountAsync();Console.WriteLine($"Rows = {count}");Console.WriteLine(db.Database.GetDbConnection().State);

With default EF connection management, the state is commonly Closed before and after the command. Provider logs show opening/execution/closing activity. Do not hard-code a correctness rule around those incidental log lines; explicitly opened connections and transactions change the lifetime.

9. Deliberately wrong approach: defer the query past the context lifetime

The following code returns an IQueryable from inside a disposed context. No database work has occurred when the method returns. Enumeration later needs the context's query services/connection and fails.

static IQueryable<WorkOrder> BuildQuery(){    using var db = new ServiceHubContext(CreateOptions());    return db.WorkOrders.Where(x => x.Priority == WorkOrderPriority.High);}var query = BuildQuery();var rows = await query.ToListAsync(); // context has already been disposed

Expect a context-disposed failure rather than useful data. The repair is not to keep one context globally forever. Keep query construction and execution inside a deliberate unit-of-work lifetime, or materialize the required data before leaving that lifetime.

static async Task<List<WorkOrder>> LoadHighPriorityAsync(){    await using var db = new ServiceHubContext(CreateOptions());    return await db.WorkOrders        .Where(x => x.Priority == WorkOrderPriority.High)        .OrderByDescending(x => x.OpenedUtc)        .ToListAsync();}

Chapter 02 will replace manual construction with dependency-injection and factory patterns for different application lifetimes.

10. Hands-on lab: migrate, insert, query, and verify the database

Use the repository-local toolchain from Lesson 3. If a previous experiment created servicehub-lab.db with EnsureCreated, remove that disposable file before beginning the migration lifecycle.

dotnet tool restoredotnet restoredotnet builddotnet ef migrations add InitialCreate --project src/ServiceHub.EfLab --output-dir Data/Migrationsdotnet ef database update --project src/ServiceHub.EfLabdotnet run --project src/ServiceHub.EfLab

Verification checklist

• The migration directory contains InitialCreate plus a model snapshot.
• The SQLite file exists only in the disposable lab location you intended.
• __EFMigrationsHistory records the applied migration.
• A newly added WorkOrder transitions from Added to Unchanged after a successful save.
• Generated/logged SQL identifies the SQLite provider's quoting/type conventions.
• The query executes only when a terminal async operation such as ToListAsync is reached.
• No production-like credentials, database paths, or unrelated data were touched.

11. Production judgment and next bridge

A production DbContext should have a deliberate short lifetime, externalized configuration, observable provider behavior, and migrations owned by a reviewed deployment process. Do not hold a context forever, execute concurrent operations on it, infer database performance from ToQueryString, or give a runtime application schema-owner permissions merely because migrations need DDL.

Lesson 5 turns this one-file demonstration into a repeatable course environment: stable project layout, deterministic sample data, external configuration, safe secrets, structured EF logging, reset/seed workflows, and a recorded version manifest. That shared lab becomes the foundation for later chapters instead of silently changing assumptions from lesson to lesson.

Authoritative references

• DbContext lifetime, configuration, and initialization — short-lived unit of work, construction, disposal, and thread-safety
• Creating and configuring a model — model discovery and relational mapping entry point
• Managing database schemas with migrations — migration authoring, snapshots, update workflow, and deployment guidance
• Change tracking overview — entity states and tracking lifecycle
• How queries work — LINQ query representation, execution, and materialization concepts
• Simple logging — LogTo and development diagnostics
• SQLite provider — Microsoft-maintained provider and provider limitations