Webority.SchemaGuard 3.1.0

Webority.SchemaGuard

Compares an EF Core model against a SQL Server database project (.dacpac) — offline, in seconds, with no database connection, no credentials and no firewall rule.

Why

A .sqlproj repo usually gates .sqlproj → staging → prod with SqlPackage /Action:DeployReport. That says nothing about the layer above it: whether the EF model the applications actually run against agrees with the schema. That boundary is where the expensive bugs live — a property renamed on one side only, an enum whose store type moved, a column EF thinks is NOT NULL that the database lets be NULL. All of it compiles green and fails at runtime, per request, in production.

Because DACPAC ↔ live is already gated, proving EF ↔ DACPAC proves EF ↔ live transitively.

On its first run against a 400-table estate it found 4 tables with no primary key, 2 unenforced unique indexes on email columns, encrypted decimal columns being written as strings into DECIMAL columns, 10 relationships with no foreign key, and a mapped column that existed in no table or migration.

How it works

1. Builds the DbContext's relational model (context.Model.GetRelationalModel()) — EF's own answer to "what SQL do I think I'm talking to", so HasColumnType, value converters, owned types, table splitting and shadow properties are already resolved. Reflection over entity properties would miss all of that.
2. Reads the built .dacpac through DacFx.
3. Canonicalises both sides' store types so implied facets (datetimeoffset vs scale 7) don't read as differences.
4. Compares, and classifies each difference.

What it compares: tables, columns (name, store type, nullability, computedness, identity, whether a default exists), primary keys, unique constraints, indexes, and foreign keys. Uniqueness and foreign keys are compared in both directions, and so is every column property above; a foreign key is matched on both ends, with its delete rule. Table and column presence is the asymmetric one: model-only is an error, database-only is information unless nothing can supply the column's value. See Not compared (yet) for what it does not.

Reading context.Model also forces model creation, which is the cheapest gate for the EF constructor-parameter rename class — a compile-green change that breaks every request at runtime.

Contradictions vs omissions

This split is what makes the tool usable rather than a wall of noise.

• A contradiction — both sides declared something and they disagree. One is wrong. → Error
• An omission — the entity never declared a length or requiredness, so EF fell back to nvarchar(max) NULL. The database enforces the truth. → Info

With nullable reference types disabled, omissions outnumber contradictions roughly fifty to one. On the reference estate the first run reported 3,077 "errors"; correct classification took that to 61 real ones.

column-scale-undeclared is the one omission that is not noise, and it covers every type that carries a scale: decimal and numeric, which round on the way in, and datetime2, datetimeoffset and time, which truncate. EF's default for an undeclared decimal is decimal(18,2), so a column at decimal(18,9) is written through a scale-2 parameter and rounded; its default for an unconfigured DateTime is datetime2(7), so a DATETIME2(0) column loses the sub-second component of every write. Nothing throws either way. Length omissions and same-scale precision omissions stay Info.

Constraints are matched by column set, not by name, because constraint-name drift is endemic and belongs to the DeployReport gate, not this one. A foreign key is matched on both ends, its dependent columns and its principal table and columns, because the same column can legitimately point at a different parent or at a different key of the same one.

Uniqueness and relationships are compared in both directions

Uniqueness the database enforces and the model does not declare used to produce no finding at any severity: someone drops an .IsUnique() while refactoring, the database goes on enforcing it, and the application stops doing its duplicate check because the model says the column is not unique. The second registration with an existing address then arrives as a raw constraint violation. It is reported as an error when EF maps every column of the set, and as information when it does not, on the same reasoning as relationships below. A filter disagreement over a set both sides call unique is reported once, by the model-to-database pass.

A relationship can go missing from either side, and only one of those used to be caught.

EF declares one the database does not enforce, and orphan rows are already possible. The reverse is quieter and was invisible: a HasOne that was deleted, or never written, leaves the column mapped and every gate green, while the database goes on enforcing the constraint. EF then plans no delete behaviour, builds no index on the dependent column, and cannot load the parent at all. It was measured twice in one release on the reference estate.

Which of those is an error turns on whether EF maps both ends:

• EF maps every column on both sides and still models no link → foreign-key-missing-in-ef, Error. Both sides declared the relationship's columns and they disagree about the relationship.
• EF does not map the dependent column, or no entity maps the principal table or its key column → foreign-key-unmapped, Info. Any one of those is enough: there is no declaration to contradict. The message names the end that is missing, so the line is actionable rather than noise.

foreign-key-delete-behaviour-mismatch is a Warning, not an error. EF applies its own delete rule in the change tracker for a graph it has loaded, so the two only diverge on a delete the database itself has to carry out or refuse. Real, and a cascade EF plans that the database will not perform fails the delete outright, but nothing is stranded and no read breaks. Expect a run of these on any repo whose Tables/*.sql foreign keys were hand-written without an ON DELETE clause: EF defaults a required relationship to CASCADE, a bare REFERENCES clause is NO ACTION, and every such pair reports.

The third source of truth (--domain)

A column's maximum length is stated in three places: the SQL column, the EF mapping, and the entity's own DomainValidation call. Everything above compares the first two. The third is ordinary C# inside a constructor body — absent from the relational model and absent from the DACPAC — so a run can report clean while the application is broken.

That is not hypothetical. A shipped entity declared HasMaxLength(256) against an NVARCHAR(512) column that both the mapping and the schema agreed on. One seeded row held 292 characters: stored legitimately, and thereafter unreadable, because EF binds constructor parameters and runs the validation while materializing. Every read of that row threw. Schemaguard passed, correctly, the whole time.

Point --domain at the entity source to close it:

schemaguard --dacpac Product.Database/bin/Debug/Product.Database.dacpac --domain Product.Domain/Entities

• Constructor stricter than the column, and the entity has no parameterless constructor → Error. EF binds the validating constructor and re-runs it while materializing, so the database accepts rows the application cannot read back. It fails at read time on data valid by the schema.
• Constructor stricter, but a parameterless constructor exists → Warning. EF hydrates through property setters, so reads are safe. The bound still contradicts the column — the domain cannot write the column's full width — but it is not an outage.
• Constructor looser than the column → Warning. The domain accepts what the database rejects, so the write fails loudly at the DB. Bad, but it cannot strand unreadable rows.

The parameterless-constructor distinction matters because it is the structural fix for this whole class: give every EF-materialized aggregate a protected Xxx() { } and write-time rules stop running on reads entirely. Aligning each bound with its column is then a consistency exercise rather than an outage waiting for the wrong row.

The check is enabled by supplying the path it needs rather than by a separate flag, and it is deliberately not folded into --conventions — those are house-style preferences, this is a contradiction that breaks reads.

Parsing is syntax-only (CSharpSyntaxTree.ParseText): no project load, no MSBuild, no semantic model, so the run stays offline and fast. The property is resolved from the actual Property = parameter; assignment rather than by capitalising the parameter name, so an entity that stores label into DisplayName is compared against the right column. nvarchar(max) has no bound to contradict and is skipped.

Adding it to a repo

1. Reference the package in a small console project:

<PackageReference Include="Webority.SchemaGuard" Version="<latest>" />
<ProjectReference Include="..\MyProduct.Infrastructure\MyProduct.Infrastructure.csproj" />
<ProjectReference Include="..\MyProduct.Database\MyProduct.Database.sqlproj" ReferenceOutputAssembly="false" />

The database project reference links nothing; it makes one build of the host also build the dacpac, so the script pays for one build instead of two. The script refuses to run without it, because a build that skipped the dacpac would check the code against a stale schema.

2. Write the one file that cannot be shared — how to construct your DbContext:

internal static class MyProductContextProvider
{
    // Never dialled: reading DbContext.Model builds the model without opening a connection.
    private const string DesignTime =
        "Server=schemaguard.invalid;Database=SchemaGuard;Trusted_Connection=True;TrustServerCertificate=True";

    public static DbContext Create()
    {
        // Any startup wiring OnModelCreating needs goes here (e.g. an encrypted-column
        // converter factory — a throwaway key is fine, only column shapes are read).
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseSqlServer(DesignTime)
            .Options;

        return new ApplicationDbContext(options);
    }
}

3. The entry point is one line:

internal static class Program
{
    private static int Main(string[] args) =>
        SchemaGuardRunner.Run(args, MyProductContextProvider.Create);
}

4. Copy the script from templates/ and adjust the paths at the top:

scripts/schemaguard.sh        builds the dacpac, runs the check

HOST_DLL is the host's <AssemblyName> under its target framework, which is not necessarily the project name. The script starts it directly; dotnet run --no-build evaluates the project again first and costs seconds on every run.

5. Baseline the existing drift, then ratchet it down:

scripts/schemaguard.sh --write-baseline schemaguard-baseline.txt   # review the diff, commit it

Write the baseline before the first real run. Every other run passes --baseline, and a file that is not there exits 2 rather than quietly running with nothing suppressed.

Set --baseline-max to the resulting count. It may only ever shrink.

The ceiling counts accepted findings that still occur, not lines in the file. Fixing a baselined finding lowers the number on the next run, before anyone regenerates; a line whose cause is gone spends none of the allowance, and the run says how many such lines the file holds so the regeneration is worth making. Counting lines instead meant fixing drift lowered nothing until the file was rewritten, so the ratchet measured paperwork rather than debt.

A baseline line is the rule, the target and a short fingerprint of the finding's message. The message is where the two store types, the two nullabilities and the two delete rules are actually written, so without it a column changing from one kind of wrong to another under the same rule stayed suppressed. The cost is the other way round: rewording a message resurfaces its finding once, to be re-accepted deliberately. A baseline written before the fingerprint existed carries none, so every consumer regenerates its file once. Such a file is recognised on read (it lacks the # key-format: 2 marker) and the regeneration run treats it as a first baseline rather than reporting every line as changed.

Run by hand before pushing

Nothing runs this in CI, and nothing runs it on push. Run scripts/schemaguard.sh yourself before pushing any change that touches an entity, a DbContext configuration, or Tables/*.sql. It needs no database, no credentials and no firewall rule, and the check itself takes several seconds after one build, so there is little cost to running it every time.

• It prints only errors and warnings (--quiet), so the finding isn't buried under baselined info.
• Catching drift at merge-to-main is too late — it has been on the shared branch for days with other work stacked on it — and re-running the build on a runner costs minutes on every release, so this stays a local, by-hand check rather than a CI or push gate.

Options

--dacpac <path>       .dacpac to compare against
--conventions         also check house rules: date/time naming, enums-as-strings (warnings)
--domain <path>       also check entity CONSTRUCTOR invariants against column widths
--fail-on <level>     error (default) | warning | never
--quiet               print only warnings and errors, not the info backlog
--json <path>         write the findings as JSON, live and suppressed
--baseline <path>     suppress findings listed in this file; fail only on new ones.
                      The file must exist: a path that is not there exits 2
--write-baseline <p>  rewrite the baseline; names what it adds and removes, and
                      refuses to write at all when anything would be added
--accept-new          with --write-baseline: accept the new findings on purpose
--baseline-max <n>    fail if more than n accepted findings still occur (ratchet)

This block is the --help output, asserted line for line by a test. Edit the option list in the code; this copy follows.

Exit codes: 0 clean · 1 findings at or above --fail-on, or a refused regeneration · 2 could not run (a bad argument, no .dacpac, or a --baseline file that is not there).

The --json schema

An object with two lists, not a bare array. Findings holds what the run reports; Suppressed holds what the baseline accepts, which is the number the ratchet exists to shrink and the one a bare array of live findings could never show.

{
  "Findings": [
    { "Severity": 2, "Rule": "column-type-mismatch", "Target": "dbo.Catalogue.Amount",
      "Message": "EF says decimal(18,2), the database project says decimal(10,4)." }
  ],
  "Suppressed": []
}

Severity is its ordinal: 0 info, 1 warning, 2 error. Suppressed is empty when no --baseline was given.

The ratchet is the diff, not the count. --baseline-max proves only that the file did not grow, and five findings fixed while five others are introduced leaves the size identical. A regeneration therefore reads the committed file first, prints the keys it would add and the keys it would remove, and refuses to write anything when there are added ones. --accept-new is how they become debt deliberately. The ceiling stays as a second layer, now counting the accepted findings a run actually meets.

Requirements

• .NET 10
• The database project on the Microsoft.Build.Sql SDK — a legacy SSDT project won't produce a compatible model.
• DacFx new enough for the model the SDK emits. Pinned to 170.4.83; an older one fails with "does not contain the Element class SqlVectorTypeSpecifier". Bump alongside the SDK pin in the consuming .sqlproj.
• A DbContext that builds without a connection. Anything OnModelCreating needs goes in the provider.
• A case-insensitive database. Table, column and constraint identifiers are matched case-insensitively throughout, which is right for the house standard (ModelCollation 1033, CI) and keeps a wall of false errors out. On a database created with a case-sensitive or binary collation it is a false negative: EF's Email matches the database's email, this run passes, and every query fails at runtime with "Invalid column name".

Not compared (yet)

Default expressions (their presence is compared, their text is not, because SQL Server renormalizes it), check constraints, collation, sequences other than identity, computed-column expressions, views, stored procedures. An entity mapped to a view is on neither side of the comparison, so the run names it (entity-mapped-to-view) rather than leaving the gap to this paragraph alone; a keyless entity on a table the database keys is named the same way (entity-keyless-on-keyed-table), since its columns are compared and its key is not. A foreign key's ON UPDATE rule, which EF does not model separately. Column order is deliberately ignored, matching the DeployReport flags.

License

MIT. See LICENSE, which ships inside the package.

Releasing

Bump <VersionPrefix> in Directory.Build.props and merge to main. The fleet's shared publish workflow packs and pushes to nuget.org (--skip-duplicate, so a re-run without a version bump is a no-op).