A DB-First Model in Code-First Clothing

Every EF Core model carries its birthmarks, and this one's say plainly: I was a database first, and the code-first costume came later. The attendance platform's AttendanceContext — thirty-plus entities, twenty migrations, split across two partial classes — presents as a code-first model with a migrations history. Read the fluent configuration for ten minutes and the truth surfaces: this schema was reverse-engineered from a live database, scaffolding artefacts and all, and then promoted to “code first” without anyone sweeping up. After part four's view-layer archaeology, this part digs through the model that feeds those views.

None of this is a scandal; scaffolding an existing database is the sanctioned on-ramp. The lessons are in what happens when the scaffold's output is treated as finished code rather than as a first draft.

A surrogate that reigns, a natural key that rules

The Employee entity has two identities:

public class Employee
{
    [Key]
    public decimal Id { get; set; }          // numeric(18,0) in the database

    public int EmployeeId { get; set; }      // the badge number - the real key

    public string EmployeeName { get; set; }
    public decimal? ManagerId { get; set; }  // note: decimal, like Id - not int, like EmployeeId
    public virtual ICollection<AttendanceDay> AttendanceDays { get; set; }
}

Id is the primary key — a decimal, because the original table used SQL's numeric(18,0) and the scaffolder translated it faithfully. But nothing in the domain references it. The key everyone actually uses is EmployeeId, the badge number: it is what the sync jobs upsert on, what the URL carries, what every other table stores. The model makes this work with one of EF Core's genuinely under-appreciated features, the alternate key:

modelBuilder.Entity<AttendanceDay>()
    .HasOne(d => d.Employee)
    .WithMany(e => e.AttendanceDays)
    .HasForeignKey(d => d.EmployeeId)
    .HasPrincipalKey(e => e.EmployeeId);   // relate on the natural key, not the PK

HasPrincipalKey tells EF the relationship targets EmployeeId rather than the primary key, and EF enforces uniqueness on it. This is the right tool, used correctly, and it deserves the compliment before the critique: when the world outside your database already assigns identifiers, relating on the natural key is honesty; the surrogate is bookkeeping.

The critique: the two identities never stopped leaking into each other. ManagerId is a decimal — it stores the surrogate of the manager while EmployeeId is an int storing the badge number, so the two “employee reference” columns in the same table are different types pointing at different keys. Every join had to remember which world it was in. A model with two keys needs a ruling on which one child tables reference, enforced everywhere; this one made the ruling per column.

The index that names itself

The fluent configuration carried some fifteen index definitions copied verbatim from the database. Two categories stand out.

First, the machine-named: indexes with hash-suffixed names in the style Azure SQL's automatic tuning generates when someone clicks “apply recommendation”. Reasonable in the database; weird fossilised in C#, where the name now implies a person designed it.

Second, my favourite artefact in the entire estate:

modelBuilder.Entity<AttendanceDay>()
    .HasIndex(e => e.LogDate)
    .HasName("<Name of Missing Index, sysname,>");

That string is the placeholder from SQL Server Management Studio's missing-index template — the bit you are supposed to replace before running the script. Someone pasted the template, executed it unedited, and the database happily created an index literally named <Name of Missing Index, sysname,>. The scaffolder, faithful to a fault, then immortalised it in the model, and the migrations carried it forward from then on. Three near-duplicate indexes on the same date column travelled alongside it, each presumably another day's performance firefight.

The index worked. Indexes do not care what they are called. But a scaffold copies your database's mistakes into your codebase with the same fidelity as its design — and once they are in the model, they have a pension. The placeholder name survived every migration because no migration ever had a reason to touch it.

The DbSet that scaffolded a list

The best illustration that nobody was reading the model closely:

public virtual DbSet<List<string>> SqlDataSet { get; set; }

A DbSet of List<string>. EF Core, asked to treat List<string> as an entity, did its conventional best: it found the one settable scalar property on List<T> — Capacity — added a shadow key, and a subsequent migration dutifully created a real table with an id and a Capacity int column. The table shipped to production. Nothing ever wrote to it; nothing could meaningfully read it. It was the type-system equivalent of a typo, load-bearing enough to pass review because it compiled and the migration ran.

The intent, as far as I can reconstruct, was a container for raw SQL results — the List<List<string>>-returning query runner in the data-access layer suggests as much. The lesson is smaller and sharper: migrations are generated code, and generated code needs the same review as written code, because EF will always find a way to say yes.

Proxies everywhere, projections anyway

The context enabled lazy-loading proxies, and every navigation property was dutifully virtual. Lazy loading is a defensible convenience — until it meets code shaped like this:

var rows = context.AttendanceDays
    .Where(d => d.LogDate >= from)
    .ToList()                                  // materialise first...
    .Select(d => new AttendanceRowDto
    {
        Date = d.LogDate,
        Name = d.Employee.EmployeeName,        // ...then walk a lazy navigation per row
        Shift = d.SuggestedShift.ShiftName
    })
    .ToList();

The early ToList() ends the SQL translation; every d.Employee after it is a proxy interception firing its own SELECT. A month of rows for a team becomes hundreds of single-row queries — the classic N+1, invisible in development against a ten-row database and dominant in production. The same projection with the Select before materialisation is one SQL statement with joins. The codebase contained both shapes, which is the tell: nobody had a rule, so each author rolled the dice. My rule since: proxies are for aggregates you intend to walk; anything that renders a table should be a translated projection, and the ToList() goes last.

Sensitive logging, unconditionally

Two configuration choices compounded everything above. First, from part one, the context was registered with a transient lifetime, so repositories in the same request held different contexts — no shared unit of work, no identity-map reuse, and yet more queries. Second, OnConfiguring contained an unconditional:

optionsBuilder.EnableSensitiveDataLogging();

That switch exists for development: it puts parameter values — names, ids, comments, everything an attendance system knows about a person — into log output. Enabled unconditionally, it applied in production too, where those values flowed toward whatever sinks were configured. In an app whose data is literally employee monitoring, the logging pipeline became a second, unaudited copy of the database. The fix is one if (env.IsDevelopment()). The lesson is that OnConfiguring has no environment awareness unless you give it some — which is an argument for configuring contexts at registration, where the environment is in scope.

One last birthmark: the csproj excluded two migration files with <Compile Remove> while keeping their replacements — history edited by exclusion rather than deletion, so the folder listed migrations the model snapshot no longer agreed with. Delete the file; git remembers.

The model is only half the data story. The other half is how this application talks to its thirty-one stored procedures — three different ways, plus a fourth that never ran. That is the next part.