EF Core Database-First: Mapping SchoolDb to C# – Data Access | Lesson 59

Published / updated7 Oct 2026

Introduction

With ADO.NET and Dapper, you wrote every SQL statement yourself. EF Core (Entity Framework Core) is a full ORM: you write LINQ, and it writes the SQL. It also remembers what it loaded, and when you change an object, it writes the UPDATE for you.

SchoolDb already exists: you built it with scripts in Module 3. Starting EF Core from an existing database is called database-first, and it is how most real projects meet EF Core, because the database is usually older than the application.

By the end of this lesson, you will be able to:

Explain database-first and code-first, and when each is used.
Scaffold a database with dotnet ef dbcontext scaffold and read what it generates.
Map your own entity classes with IEntityTypeConfiguration and value converters.
Query with LINQ and see the SQL EF Core sends.
Add and change data with change tracking and SaveChangesAsync.

Why this matters

EF Core is the default data-access tool in .NET, and the Web API uses it in Modules 10 and 11, until Lesson 81 switches the API to Dapper. Most job descriptions for .NET developers list it, and most interviews ask about it.

Knowing what it does underneath matters as much as knowing how to call it. In this lesson, every LINQ query is shown next to its SQL, so you can judge EF Core with what you learned in Modules 4 and 5.

The concept

Example: database-first and code-first

Approach	Starts from	Schema changes are made in	Common when
Database-first	An existing database	SQL scripts, by the database team	The database is shared or older than the app
Code-first	C# classes	C# classes, then EF Core migrations create the SQL	A new app owns its database

The SMS is database-first: the scripts in database/ stay the single source of truth, and EF Core reads what they build. Code-first migrations have their own optional lesson, on a separate library database: Extra 10.

Example: the DbContext

A DbContext is EF Core's session with the database. Think of the school office's front desk: you ask it for records, it fetches them, it remembers which files you took, and when you hand them back with changes, it files the changes.

Each DbSet<T> property is one table you can query with LINQ, such as db.Students. A DbContext is meant to be short-lived: one per web request, or one per unit of work in a console program.

Example: what scaffolding generates

The dotnet ef tool can read a database and write C# for it. This is called scaffolding. First, install the tool for the repository, then run the command from the repository root:

Terminal
dotnet new tool-manifest
dotnet tool install dotnet-ef --version 10.0.12
dotnet ef dbcontext scaffold "Server=localhost;Database=SchoolDb;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer --project src/SchoolApp.Data --output-dir Scaffolded --context ScaffoldedSchoolDbContext --no-onconfiguring

On SchoolDb, it took about 30 seconds and wrote 29 files: one class per table and view, and a 677-line DbContext. Here is the generated Student:

Generated: Scaffolded/Student.cs
public partial class Student
{
    public int Id { get; set; }

    public string AdmissionNumber { get; set; } = null!;

    public string FirstName { get; set; } = null!;

    public string LastName { get; set; } = null!;

    public DateOnly DateOfBirth { get; set; }

    public string Gender { get; set; } = null!;

    public DateOnly AdmissionDate { get; set; }

    public string? Address { get; set; }

    public string Status { get; set; } = null!;

    public DateTime CreatedAt { get; set; }

    public int? CreatedBy { get; set; }

    public DateTime? UpdatedAt { get; set; }

    public int? UpdatedBy { get; set; }

    public bool IsDeleted { get; set; }

    public DateTime? DeletedAt { get; set; }

    public virtual ICollection<BookIssue> BookIssues { get; set; } = new List<BookIssue>();

    public virtual ICollection<Enrollment> Enrollments { get; set; } = new List<Enrollment>();

    public virtual ICollection<StudentGuardian> StudentGuardians { get; set; } = new List<StudentGuardian>();
}

And the part of the generated DbContext that describes the Students table:

Generated: Scaffolded/ScaffoldedSchoolDbContext.cs (part)
modelBuilder.Entity<Student>(entity =>
{
    entity.HasIndex(e => new { e.LastName, e.FirstName }, "IX_Students_LastName_FirstName");

    entity.HasIndex(e => e.AdmissionNumber, "UQ_Students_AdmissionNumber").IsUnique();

    entity.Property(e => e.Address).HasMaxLength(200);
    entity.Property(e => e.AdmissionNumber)
        .HasMaxLength(15)
        .IsUnicode(false);
    entity.Property(e => e.CreatedAt)
        .HasPrecision(0)
        .HasDefaultValueSql("(sysutcdatetime())", "DF_Students_CreatedAt");
    entity.Property(e => e.DeletedAt).HasPrecision(0);
    entity.Property(e => e.FirstName).HasMaxLength(50);
    entity.Property(e => e.Gender)
        .HasMaxLength(1)
        .IsUnicode(false)
        .IsFixedLength();
    entity.Property(e => e.LastName).HasMaxLength(50);
    entity.Property(e => e.Status)
        .HasMaxLength(12)
        .IsUnicode(false)
        .HasDefaultValue("Active", "DF_Students_Status");
    entity.Property(e => e.UpdatedAt).HasPrecision(0);
});

The column details are exactly right, which makes scaffolding a great way to learn the mapping. But compare the class with our Student from Module 7: Gender and Status are plain strings, every property has a setter, and the name checks are gone.

--no-onconfiguring matters too. Without it, the tool writes your connection string into the generated code, and it shows a warning about exactly that.

So in SchoolApp, the scaffolded files are a reference, not the code. Read them, then delete the Scaffolded folder. The next steps map our own classes instead.

Apply it to the SMS

Step 1: packages and the tool

Terminal
dotnet add src/SchoolApp.Data package Microsoft.EntityFrameworkCore.SqlServer --version 10.0.12
dotnet add src/SchoolApp.Data package Microsoft.EntityFrameworkCore.Design --version 10.0.12

src/SchoolApp.Data/SchoolApp.Data.csproj
<Project Sdk="Microsoft.NET.Sdk">

  <!-- Lesson 57: the data layer. It knows about SchoolApp.Core; Core never knows about it. -->
  <ItemGroup>
    <ProjectReference Include="..\SchoolApp.Core\SchoolApp.Core.csproj" />
  </ItemGroup>

  <!-- Lesson 57: the SQL Server driver for .NET. 6.1.x is the version EF Core 10 uses (Lesson 59).
       Lesson 58: Dapper, which maps query results to objects. -->
  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.1.89" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="6.1.7" />
  </ItemGroup>

  <!-- Lesson 59: EF Core for SQL Server, plus the design-time package the dotnet ef tool needs.
       PrivateAssets="all" keeps the design package out of projects that reference this one. -->
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="10.0.12" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.12">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
  </ItemGroup>

  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

</Project>

The tool manifest, dotnet-tools.json, is committed with the code. Anyone who clones the repository runs dotnet tool restore once and gets the same dotnet ef version.

Step 2: the DbContext

src/SchoolApp.Data/EfCore/SchoolDbContext.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore;

// Lesson 59: EF Core's view of SchoolDb. Each DbSet is a table you can query with LINQ.
// The entity classes are our own, from SchoolApp.Core; the Configurations folder maps them to tables.
public class SchoolDbContext : DbContext
{
    public SchoolDbContext(DbContextOptions<SchoolDbContext> options) : base(options)
    {
    }

    public DbSet<Student> Students => Set<Student>();
    public DbSet<AcademicYear> AcademicYears => Set<AcademicYear>();
    public DbSet<SchoolClass> Classes => Set<SchoolClass>();
    public DbSet<Section> Sections => Set<Section>();
    public DbSet<Enrollment> Enrollments => Set<Enrollment>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Finds every IEntityTypeConfiguration class in this project and applies it.
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(SchoolDbContext).Assembly);
    }
}

ApplyConfigurationsFromAssembly finds every configuration class in the project, so adding a table never means editing this file. The DbSets start with five tables; Lesson 60 adds more.

Step 3: configurations for our own classes

A configuration class tells EF Core how one entity maps to its table. It keeps EF Core details out of SchoolApp.Core, so Student stays a plain class with its own rules.

src/SchoolApp.Data/EfCore/Configurations/StudentConfiguration.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 59: how the Student class maps to dbo.Students. The class stays free of EF Core code.
public class StudentConfiguration : IEntityTypeConfiguration<Student>
{
    public void Configure(EntityTypeBuilder<Student> builder)
    {
        builder.ToTable("Students");
        builder.HasKey(s => s.Id);

        // AdmissionNumber has no setter. EF Core maps it because it is named here, and fills it
        // through the Student constructor, whose parameter names match the property names.
        builder.Property(s => s.AdmissionNumber).HasMaxLength(15).IsUnicode(false);  // VARCHAR(15)
        builder.Property(s => s.FirstName).HasMaxLength(50);
        builder.Property(s => s.LastName).HasMaxLength(50);
        builder.Property(s => s.Address).HasMaxLength(200);

        // A value converter: the enum in C#, the CHAR(1) code ('M', 'F', 'O') in the database.
        builder.Property(s => s.Gender)
            .HasColumnType("char(1)")
            .HasConversion(gender => ((char)gender).ToString(), code => (Gender)code[0]);

        // The enum names match the CHECK values, so the built-in string converter is enough.
        builder.Property(s => s.Status).HasMaxLength(12).IsUnicode(false).HasConversion<string>();

        builder.HasAuditColumns();
        builder.Property(s => s.DeletedAt).HasPrecision(0);
    }
}

Three details are worth a closer look:

The constructor. Student has no constructor without parameters. EF Core calls the real one, matching parameters such as admissionNumber to properties such as AdmissionNumber.
AdmissionNumber has no setter. EF Core skips such properties unless you name them in the configuration. Naming it here maps it, and the constructor fills it.
HasMaxLength(15).IsUnicode(false) means VARCHAR(15). EF Core then sends VARCHAR parameters, so the index problem from Lesson 57 never appears.

Example: value converters for enums

A value converter translates a property's C# value into a column value and back. Gender is stored as CHAR(1), so the converter turns Gender.Male into "M", and "M" back into Gender.Male.

Status is simpler. Its enum names match the database values, so HasConversion<string>() is enough. Converters also work inside queries: filtering on Gender.Female sends 'F' to SQL Server.

Example: shared mapping and relationships

The audit columns are the same on 13 tables, so a generic extension method maps them once:

src/SchoolApp.Data/EfCore/Configurations/AuditColumns.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 59: the audit columns from Lesson 14 are the same on every table, so they are mapped once.
// A generic extension method (Lessons 45 and 48) that works for any AuditableEntity.
internal static class AuditColumns
{
    public static void HasAuditColumns<T>(this EntityTypeBuilder<T> builder) where T : AuditableEntity
    {
        builder.Property(e => e.CreatedAt)
            .HasPrecision(0)                            // DATETIME2(0)
            .HasDefaultValueSql("SYSUTCDATETIME()");     // SQL Server fills it on INSERT
        builder.Property(e => e.UpdatedAt).HasPrecision(0);
    }
}

HasDefaultValueSql tells EF Core that the database fills CreatedAt. EF Core leaves it out of the INSERT and reads the value back.

Show the Enrollment and master-table configurations

src/SchoolApp.Data/EfCore/Configurations/EnrollmentConfiguration.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 59: dbo.Enrollments and its three foreign keys.
public class EnrollmentConfiguration : IEntityTypeConfiguration<Enrollment>
{
    public void Configure(EntityTypeBuilder<Enrollment> builder)
    {
        builder.ToTable("Enrollments");
        builder.HasKey(e => e.Id);

        // One student has many enrollments (one per year); each enrollment has one student.
        builder.HasOne(e => e.Student).WithMany(s => s.Enrollments).HasForeignKey(e => e.StudentId);
        builder.HasOne(e => e.Section).WithMany(s => s.Enrollments).HasForeignKey(e => e.SectionId);
        builder.HasOne(e => e.AcademicYear).WithMany().HasForeignKey(e => e.AcademicYearId);

        builder.HasAuditColumns();
    }
}

src/SchoolApp.Data/EfCore/Configurations/MasterDataConfigurations.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 59: the master tables from Lesson 9. Small, so they share one file.
public class AcademicYearConfiguration : IEntityTypeConfiguration<AcademicYear>
{
    public void Configure(EntityTypeBuilder<AcademicYear> builder)
    {
        builder.ToTable("AcademicYears");
        builder.Property(y => y.Name).HasMaxLength(7).IsUnicode(false);  // 2026-27
    }
}

public class SchoolClassConfiguration : IEntityTypeConfiguration<SchoolClass>
{
    public void Configure(EntityTypeBuilder<SchoolClass> builder)
    {
        builder.ToTable("Classes");  // the class is SchoolClass, the table is Classes
        builder.Property(c => c.Name).HasMaxLength(20);
    }
}

public class SectionConfiguration : IEntityTypeConfiguration<Section>
{
    public void Configure(EntityTypeBuilder<Section> builder)
    {
        builder.ToTable("Sections");
        builder.Property(s => s.Name).HasMaxLength(2).IsUnicode(false);

        // The foreign key is ClassId, not SchoolClassId, so EF Core must be told.
        builder.HasOne(s => s.SchoolClass).WithMany().HasForeignKey(s => s.ClassId);
    }
}

HasOne(...).WithMany(...).HasForeignKey(...) describes a relationship. Most relationships follow EF Core's naming rules, but the SMS uses ClassId with a SchoolClass property, so that one must be stated. Lesson 60 is all about relationships.

Step 4: the repository and a factory

src/SchoolApp.Data/EfCore/EfStudentRepository.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;

namespace SchoolApp.Data.EfCore;

// Lesson 59: the same operations as Lessons 57 and 58, with EF Core. You write LINQ;
// EF Core writes the SQL, adds the parameters, and creates the Student objects.
public class EfStudentRepository
{
    private readonly SchoolDbContext _db;

    public EfStudentRepository(SchoolDbContext db)
    {
        _db = db;
    }

    public Task<int> CountActiveAsync(CancellationToken cancellationToken = default)
    {
        return _db.Students.CountAsync(s => s.Status == StudentStatus.Active && !s.IsDeleted, cancellationToken);
    }

    public Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        return _db.Students.SingleOrDefaultAsync(s => s.AdmissionNumber == admissionNumber && !s.IsDeleted, cancellationToken);
    }

    public Task<List<Student>> SearchByLastNameAsync(string lastName, CancellationToken cancellationToken = default)
    {
        return _db.Students
            .Where(s => s.LastName == lastName && !s.IsDeleted)
            .OrderBy(s => s.Id)
            .ToListAsync(cancellationToken);
    }

    // EF Core can still run your own SQL. In SqlQuery, each {value} becomes a parameter, not text,
    // so this is as safe from SQL injection as Lessons 57 and 58.
    public Task<List<ClassListEntryDto>> GetClassListAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
    {
        return _db.Database
            .SqlQuery<ClassListEntryDto>($"EXEC dbo.usp_GetClassList @ClassId = {classId}, @SectionName = {sectionName}")
            .ToListAsync(cancellationToken);
    }
}

The methods are one LINQ statement each. SqlQuery runs your own SQL, here a stored procedure, and maps the result to a record. Each {value} in the string becomes a parameter, not text.

Show src/SchoolApp.Data/EfCore/SchoolDbContextFactory.cs

src/SchoolApp.Data/EfCore/SchoolDbContextFactory.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;

namespace SchoolApp.Data.EfCore;

// Lesson 59: builds a SchoolDbContext for the console program and tests.
// (The Web API in Module 10 registers it with dependency injection instead.)
public static class SchoolDbContextFactory
{
    // logSql: called with every SQL command EF Core sends, so you can see what your LINQ becomes.
    public static SchoolDbContext Create(SqlConnectionFactory connections, Action<string>? logSql = null)
    {
        DbContextOptionsBuilder<SchoolDbContext> options = new DbContextOptionsBuilder<SchoolDbContext>();
        options.UseSqlServer(connections.ConnectionString);

        if (logSql is not null)
        {
            options.LogTo(logSql, [DbLoggerCategory.Database.Command.Name], LogLevel.Information, DbContextLoggerOptions.None);
        }

        return new SchoolDbContext(options.Options);
    }
}

Step 5: the program

Show src/SchoolApp.Console/Program.cs

src/SchoolApp.Console/Program.cs
// Lesson 59: EF Core, database-first. Our own classes, mapped to the existing SchoolDb.
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Data;
using SchoolApp.Data.EfCore;

// Print EF Core's SQL only while showSql is true, so the output stays readable.
bool showSql = false;
await using SchoolDbContext db = SchoolDbContextFactory.Create(
    SqlConnectionFactory.FromEnvironment(),
    message => { if (showSql) Console.WriteLine($"  SQL> {message.Trim()}"); });
EfStudentRepository students = new EfStudentRepository(db);

// 1. LINQ in, SQL out: watch the query EF Core writes.
showSql = true;
Student? kabir = await students.GetByAdmissionNumberAsync("ADM-2017-0003");
showSql = false;
Console.WriteLine($"Found: {kabir?.Describe()}, born {kabir?.DateOfBirth:yyyy-MM-dd}, {kabir?.Gender}, {kabir?.Status}");

// 2. The same answers as Lessons 57 and 58.
Console.WriteLine($"Active students: {await students.CountActiveAsync()}");
Console.WriteLine($"Students named Singh: {(await students.SearchByLastNameAsync("Singh")).Count}");
Console.WriteLine($"10-A class list: {string.Join(", ", (await students.GetClassListAsync(10, "A")).Select(e => e.FullName))}");

// 3. The enum converter works inside queries too: Gender.Female becomes 'F' in the SQL.
int girlsIn10A = await db.Enrollments.CountAsync(e =>
    e.SectionId == 19 && e.AcademicYear!.IsCurrent && e.Student!.Gender == Gender.Female);
Console.WriteLine($"Girls in 10-A: {girlsIn10A}");

// 4. Not every C# method can become SQL. GetAgeOn is our own method, so SQL Server cannot run it.
DateOnly onDate = new DateOnly(2026, 10, 1);
try
{
    await db.Students.CountAsync(s => s.GetAgeOn(onDate) >= 15);
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"Not translated: {ex.Message[..ex.Message.IndexOf(" Either", StringComparison.Ordinal)]}");
}

// The same question written with things SQL understands: a date comparison.
int fifteenOrOlder = await db.Students.CountAsync(s => s.DateOfBirth <= onDate.AddYears(-15));
Console.WriteLine($"Students aged 15 or more on {onDate:yyyy-MM-dd}: {fifteenOrOlder}");

// 5. Change tracking: EF Core remembers the values it loaded and saves only what changed.
//    Everything below runs in a transaction that is rolled back, so SchoolDb is not changed.
await using (IDbContextTransaction transaction = await db.Database.BeginTransactionAsync())
{
    kabir!.Address = "12 Lake View Road";
    Console.WriteLine($"Kabir's state before saving: {db.Entry(kabir).State}");

    Student newStudent = new Student("ADM-2026-10001", "Ishaan", "Kumar", new DateOnly(2016, 4, 12), Gender.Male)
    {
        AdmissionDate = new DateOnly(2026, 10, 7)
    };
    db.Students.Add(newStudent);
    Console.WriteLine($"New student's state before saving: {db.Entry(newStudent).State}, Id {newStudent.Id}");

    showSql = true;
    int saved = await db.SaveChangesAsync();
    showSql = false;
    Console.WriteLine($"SaveChanges wrote {saved} rows; new student Id {newStudent.Id}, created {newStudent.CreatedAt:yyyy-MM-dd}");

    await transaction.RollbackAsync();
    Console.WriteLine("Rolled back: Kabir's address and the new student were not kept");
}

Output
  SQL> Executed DbCommand (47ms) [Parameters=[@admissionNumber='?' (Size = 15) (DbType = AnsiString)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [s].[Id], [s].[Address], [s].[AdmissionDate], [s].[AdmissionNumber], [s].[CreatedAt], [s].[CreatedBy], [s].[DateOfBirth], [s].[DeletedAt], [s].[FirstName], [s].[Gender], [s].[IsDeleted], [s].[LastName], [s].[Status], [s].[UpdatedAt], [s].[UpdatedBy]
FROM [Students] AS [s]
WHERE [s].[AdmissionNumber] = @admissionNumber AND [s].[IsDeleted] = CAST(0 AS bit)
Found: Kabir Singh (student ADM-2017-0003), born 2011-01-30, Male, Active
Active students: 10012
Students named Singh: 668
10-A class list: Aarav Sharma, Diya Patel, Kabir Singh, Meera Iyer
Girls in 10-A: 2
Not translated: The LINQ expression 'DbSet<Student>()
    .Count(s => s.GetAgeOn(@onDate) >= 15)' could not be translated.
Students aged 15 or more on 2026-10-01: 732
Kabir's state before saving: Modified
New student's state before saving: Added, Id 0
  SQL> Executed DbCommand (8ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 200), @p2='?' (Size = 200), @p3='?' (DbType = Date), @p4='?' (Size = 15) (DbType = AnsiString), @p5='?' (DbType = Int32), @p6='?' (DbType = Date), @p7='?' (DbType = DateTime2), @p8='?' (Size = 50), @p9='?' (Size = 1) (DbType = AnsiStringFixedLength), @p10='?' (DbType = Boolean), @p11='?' (Size = 50), @p12='?' (Size = 12) (DbType = AnsiString), @p13='?' (DbType = DateTime2), @p14='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Students] SET [Address] = @p0
OUTPUT 1
WHERE [Id] = @p1;
INSERT INTO [Students] ([Address], [AdmissionDate], [AdmissionNumber], [CreatedBy], [DateOfBirth], [DeletedAt], [FirstName], [Gender], [IsDeleted], [LastName], [Status], [UpdatedAt], [UpdatedBy])
OUTPUT INSERTED.[Id], INSERTED.[CreatedAt]
VALUES (@p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14);
SaveChanges wrote 2 rows; new student Id 10013, created 2026-10-07
Rolled back: Kabir's address and the new student were not kept

The answers match Lessons 57 and 58. Your timings and dates will differ.

Example: reading the SQL EF Core writes

The first query is SingleOrDefaultAsync, so EF Core asks for TOP(2): if two rows came back, the key would not be unique, and it would throw. @admissionNumber is a VARCHAR(15) parameter (AnsiString, Size = 15), because the configuration said so.

!s.IsDeleted became [s].[IsDeleted] = CAST(0 AS bit). Logging the SQL like this is the most useful EF Core habit you can build: every surprise in performance starts with a query you never looked at.

Example: when LINQ cannot become SQL

EF Core translates the query, not your C# methods. GetAgeOn is our own method from Lesson 37, so SQL Server has no idea what it does, and EF Core throws "could not be translated" instead of guessing.

The fix is to ask the same question with things SQL understands. "Aged 15 or more on 1 October" means "born on or before 1 October 2011", a plain date comparison that can even use an index: 732 students.

The error also suggests loading everything first with ToListAsync and filtering in C#. On SchoolDb, that would copy 10,012 students into memory to count 732 of them, so rewrite the query instead.

Example: change tracking and SaveChanges

EF Core keeps a copy of every value it loads. When kabir.Address changed, the entry's state became Modified; the new student, added with db.Students.Add, was Added.

SaveChangesAsync compared the values and sent one batch: an UPDATE of the single changed column, and an INSERT that returns the new Id and CreatedAt through OUTPUT. Both ran in one transaction, which this demo rolled back.

Common mistakes

Keeping scaffolded classes as the domain model. Strings replace enums, and the rules are gone. Fix: map your own classes; use scaffolding as a reference.
A connection string in OnConfiguring. The default scaffold writes it into code. Fix: --no-onconfiguring, and pass options from outside.
One long-lived DbContext for the whole application. It tracks more and more objects and is not thread-safe. Fix: a short-lived context per request or unit of work.
Never looking at the generated SQL. Slow queries go unnoticed. Fix: log it, or call ToQueryString() on a query.
Forgetting SaveChangesAsync. Changes stay in memory only. Fix: every unit of work ends with one save.

In real projects

From 16 years of .NET projects

On one project, the team scaffolded a 140-table database and used the generated classes everywhere. It worked for a few months.

Then a column was renamed in the database, the team scaffolded again, and the tool overwrote hundreds of classes. Every business rule someone had added to those classes was lost, and it took two days to put them back.

Since then, I keep generated code and hand-written code apart: generated files are a reference, and the real entities are mapped with configuration classes, exactly as SchoolApp does now.

Best practices

Keep entities in Core and all EF Core mapping in configuration classes in Data.
Map column types exactly: lengths, IsUnicode(false) for VARCHAR, and precision.
Use value converters for enums stored as codes or names.
Log the SQL while developing, and read it.
Commit the dotnet ef tool version in dotnet-tools.json.

Recap

EF Core is a full ORM: LINQ in, SQL out, with change tracking.
Database-first starts from an existing database; scaffolding generates a reference model.
Configuration classes map our own entities without touching Core.
Value converters translate enums to CHAR(1) codes and names.
SaveChangesAsync writes only what changed, in one transaction.

Homework

Library module task: In a single-file app, map Books and BookCopies with your own classes and a small DbContext. List every book with how many copies are available, then find The Jungle Book by ISBN and print the SQL with ToQueryString().

A correct result looks like:

Output
Malgudi Days: 1 of 1 copies available
The Jungle Book: 0 of 1 copies available
Wings of Fire: 2 of 2 copies available
SELECT [b].[Id], [b].[Author], [b].[IsDeleted], [b].[Isbn], [b].[PublishedYear], [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Isbn] = '9780141325293'
Found: The Jungle Book by Rudyard Kipling, 1894

Show the solution

library-assignment/059-library-efcore.cs
// Library homework, Lesson 59: map the library tables with EF Core and query them with LINQ.
// Run with:  dotnet run library-assignment/059-library-efcore.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:property PublishAot=false
using Microsoft.EntityFrameworkCore;
using SchoolApp.Data;

DbContextOptions<LibraryDbContext> options = new DbContextOptionsBuilder<LibraryDbContext>()
    .UseSqlServer(SqlConnectionFactory.FromEnvironment().ConnectionString)
    .Options;
await using LibraryDbContext db = new LibraryDbContext(options);

// 1. Every book with how many of its copies are available. Copies is a navigation property,
//    so EF Core writes the join and the COUNT itself.
var shelf = await db.Books
    .Where(b => !b.IsDeleted)
    .OrderBy(b => b.Title)
    .Select(b => new { b.Title, Available = b.Copies.Count(c => c.Status == "Available"), Total = b.Copies.Count })
    .ToListAsync();
foreach (var book in shelf)
{
    Console.WriteLine($"{book.Title}: {book.Available} of {book.Total} copies available");
}

// 2. One book by ISBN, and the SQL EF Core sent for it.
IQueryable<Book> byIsbn = db.Books.Where(b => b.Isbn == "9780141325293");
Console.WriteLine(byIsbn.ToQueryString());
Book? jungleBook = await byIsbn.SingleOrDefaultAsync();
Console.WriteLine($"Found: {jungleBook?.Title} by {jungleBook?.Author}, {jungleBook?.PublishedYear}");

class Book
{
    public int Id { get; set; }
    public string Isbn { get; set; } = "";
    public string Title { get; set; } = "";
    public string Author { get; set; } = "";
    public short? PublishedYear { get; set; }
    public bool IsDeleted { get; set; }
    public List<BookCopy> Copies { get; set; } = new List<BookCopy>();
}

class BookCopy
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public short CopyNumber { get; set; }
    public string Status { get; set; } = "";
}

class LibraryDbContext : DbContext
{
    public LibraryDbContext(DbContextOptions<LibraryDbContext> options) : base(options)
    {
    }

    public DbSet<Book> Books => Set<Book>();
    public DbSet<BookCopy> BookCopies => Set<BookCopy>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>(book =>
        {
            book.ToTable("Books");
            book.Property(b => b.Isbn).HasMaxLength(13).IsUnicode(false);
            book.Property(b => b.Title).HasMaxLength(200);
            book.Property(b => b.Author).HasMaxLength(100);
            book.HasMany(b => b.Copies).WithOne().HasForeignKey(c => c.BookId);
        });
        modelBuilder.Entity<BookCopy>(copy =>
        {
            copy.ToTable("BookCopies");
            copy.Property(c => c.Status).HasMaxLength(9).IsUnicode(false);
        });
    }
}

The ISBN appears in the SQL as a literal because it is a constant in the code. A value from a variable, such as a user's search, always becomes a parameter. EF Core generates code while it runs, so the app turns off the single-file AOT default.

Quiz

What does database-first mean?
1. EF Core creates the database from classes
2. EF Core is mapped to a database that already exists
3. The database is in memory
4. Only stored procedures are used
Show answer
Answer: B. EF Core is mapped to a database that already exists SchoolDb was built by scripts in Module 3; EF Core maps to it.
Why does SchoolApp not keep the scaffolded classes?
1. They do not compile
2. They use strings instead of enums and lose our rules
3. They are slower
4. They need SQL Server 2025
Show answer
Answer: B. They use strings instead of enums and lose our rules They are a useful reference, but our Core entities hold the rules.
What does a value converter do?
1. Converts SQL to LINQ
2. Translates a property value to a column value and back
3. Changes column types in the database
4. Encrypts data
Show answer
Answer: B. Translates a property value to a column value and back Gender.Male becomes 'M' in the database, and 'M' becomes Gender.Male in C#.
Kabir's Address changed. What does SaveChangesAsync send?
1. An UPDATE of every column
2. An UPDATE of the Address column only
3. A DELETE and an INSERT
4. Nothing until Commit
Show answer
Answer: B. An UPDATE of the Address column only Change tracking compares values and updates only what changed.
How long should a DbContext live?
1. As long as the application
2. One unit of work, such as one web request
3. One query only
4. One day
Show answer
Answer: B. One unit of work, such as one web request Short-lived contexts track fewer objects and are never shared between threads.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Data Access interview questions.

Fresher (0-2 yrs)

F What is EF Core, and what is a DbContext? concept

EF Core is Microsoft's ORM for .NET: you query with LINQ, and it writes the SQL, creates objects, tracks changes, and saves them. A DbContext is one session with the database; each DbSet<T> is a table you can query. A context should be short-lived, such as one per web request.

See the example · Permalink

F What is the difference between database-first and code-first? concept

Database-first starts from an existing database: schema changes are made in SQL, and EF Core maps to the result, often helped by scaffolding. Code-first starts from C# classes, and EF Core migrations create and change the database. Database-first fits shared or older databases; code-first fits new apps that own their database.

See the example · Permalink

F What does SaveChangesAsync do? concept

It looks at every tracked entity, finds the added, modified, and deleted ones, and sends the matching INSERT, UPDATE, and DELETE statements in one transaction. Changing one property of a loaded student produced an UPDATE of that single column.

See the example · Permalink

Mid (3-5 yrs)

M What does dotnet ef dbcontext scaffold generate, and why use --no-onconfiguring? concept

It reads a database and writes one class per table and view, plus a DbContext with the full mapping: lengths, types, keys, indexes, defaults, and relationships. Without --no-onconfiguring, it also puts the connection string in the generated code, which leaks credentials into source control.

See the example · Permalink

M What is a value converter in EF Core? Give an example. coding

It translates a property value to a column value and back. In the SMS, Gender is an enum in C# and a CHAR(1) code in the database: HasConversion(g => ((char)g).ToString(), c => (Gender)c[0]). HasConversion<string>() stores an enum by name. Converters also apply to query parameters.

See the example · Permalink

M Why might EF Core throw 'The LINQ expression could not be translated', and how do you fix it? concept

The query calls something SQL cannot run, such as your own C# method (s.GetAgeOn(date)). EF Core throws instead of loading everything. Rewrite the condition with translatable parts, such as a date comparison, rather than calling ToList first and filtering in memory.

See the example · Permalink

Senior (6-9 yrs)

S How do you map rich domain entities, with constructors and read-only properties, to an existing database with EF Core? design

Keep the entities in the core project and put all mapping in IEntityTypeConfiguration classes in the data project. EF Core can call a constructor whose parameter names match properties, map read-only properties that are configured explicitly, and use value converters for enums. The entities need no EF Core attributes or base classes.

How to answer: Describe the layering first, then the specific features (constructor binding, explicit property mapping, backing fields, converters), and say how you verified the mapping, such as comparing against the scaffolded model.

See the example · Permalink

S A colleague wants to scaffold the database again every time a column changes, overwriting the entity classes. What do you advise? scenario

Regenerating overwrites anything added by hand, such as rules, constructors, or enums, and makes the domain model follow the database. Keep scaffolded output as a reference only, map hand-written entities through configuration classes, and change one configuration line when a column changes.

How to answer: Explain the risk with a concrete loss, propose the separation, and mention a check that catches mapping drift, such as an integration test or a column comparison script.

See the example · Permalink

Architect / Lead (10+ yrs)

A Who should own schema changes in a database-first EF Core system, and how do you keep code and database in step? design

The database scripts are the source of truth, reviewed and versioned like code, and run in order by deployment. EF Core mappings follow them. Keep both in one repository, deploy the script before the code that needs it, prefer additive changes, and run integration tests against a database built from the scripts so drift fails the build.

How to answer: Cover ownership, versioning, deployment order, backward-compatible changes, and automated drift detection. Compare briefly with code-first migrations.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-59-ef-core-database-first (code repository coming soon)
Files: src/SchoolApp.Data/EfCore/SchoolDbContext.cs, src/SchoolApp.Data/EfCore/Configurations/StudentConfiguration.cs, src/SchoolApp.Data/EfCore/EfStudentRepository.cs, src/SchoolApp.Console/Program.cs, library-assignment/059-library-efcore.cs
Video: coming soon

What's next

EF Core now reads single tables. Next: relationships, so a report card can load an exam, its subjects, and a student's marks together, and the classic mistakes that make that slow. Lesson 60: EF Core relationships

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: database-first and code-first​

Example: the DbContext​

Example: what scaffolding generates​

Apply it to the SMS​

Step 1: packages and the tool​

Step 2: the DbContext​

Step 3: configurations for our own classes​

Example: value converters for enums​

Example: shared mapping and relationships​

Step 4: the repository and a factory​

Step 5: the program​

Example: reading the SQL EF Core writes​

Example: when LINQ cannot become SQL​

Example: change tracking and SaveChanges​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: database-first and code-first

Example: the DbContext

Example: what scaffolding generates

Apply it to the SMS

Step 1: packages and the tool

Step 2: the DbContext

Step 3: configurations for our own classes

Example: value converters for enums

Example: shared mapping and relationships

Step 4: the repository and a factory

Step 5: the program

Example: reading the SQL EF Core writes

Example: when LINQ cannot become SQL

Example: change tracking and SaveChanges

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next