Your Reader Cannot Be Stricter Than Your Schema

SingleOrDefault looks like a small query choice. In practice, it is an assertion about your data model: for this predicate, zero or one row may exist.

That assertion is valuable when storage enforces the same rule. When the database permits duplicates, however, the reader is relying on an invariant that does not exist. One unexpected row can turn an ordinary lookup into an exception before validation, authorization, or other normal handling begins.

I recently reviewed a committed recovery for exactly that class of mismatch. The useful lesson was not the specific lookup. It was how to contain the exception without pretending the data had become correct.

A query can encode a hidden contract

Consider a generalized account table:

Rows permitted by storage: 0..many per normalized key
Rows expected by reader:    0..1 per normalized key

The read path might be perfectly reasonable in isolation:

var record = await records.SingleOrDefaultAsync(x => x.LookupKey == key);

But the method name does not create uniqueness. It only checks cardinality at read time. If two rows match, the query throws.

This is why I now treat Single and SingleOrDefault as executable invariant assertions. During review, I ask: what proves the invariant? A unique index? A constrained aggregate? A transactionally protected write path? Or only an earlier existence check that can race?

Containment is not repair

When duplicate data already exists, immediately adding a unique index may be unsafe. Migration can fail on the existing rows. Previously accepted imports or registrations may begin failing without a useful remediation path.

At the same time, leaving a critical read path to throw can make a narrow data defect disproportionately disruptive.

A staged response separates two goals:

1. Containment: provide a deterministic, non-crashing resolution path while preserving security and making the defect visible.
2. Correction: repair the existing data and enforce the intended rule for future writes.

The distinction matters. A tolerant reader is a recovery mechanism, not evidence that duplicates are acceptable.

Properties of a safe recovery path

A useful containment pattern keeps the normal case boring:

try
{
    return await ReadExpectedSingleAsync(key);
}
catch (InvalidOperationException)
{
    var matches = await ReadBoundedCandidatesAsync(key);
    if (matches.Count < 2)
        throw;

    ReportCollision(matches.Select(x => x.OpaqueId));
    return ChooseDeterministically(matches);
}

This sketch is intentionally incomplete, but it highlights several important properties.

Keep the fast path unchanged

Most lookups are not ambiguous. Do not make every request pay for a corruption-recovery query. Invoke the fallback only after the expected lookup demonstrates the precise failure you are prepared to handle.

Prove ambiguity before handling it

InvalidOperationException can come from many places. Catching it and returning “not found” would turn an unrelated store failure into a silent wrong answer. Re-query narrowly; if there are not actually multiple matches, rethrow the original fault.

Choose deterministically

Never accept database enumeration order as a resolution policy. Define explicit ranking from legitimate domain facts, then add a stable final tie-break. The same data must produce the same candidate on every attempt.

Determinism does not make the selected record correct. It prevents the recovery itself from becoming random.

Preserve downstream verification

Resolving one candidate must not grant access, approve a transaction, or bypass an eligibility rule. Credential checks, authorization, state validation, and other security decisions still run after resolution. Containment should make the lookup deterministic, not weaken the boundaries that follow it. A legitimate candidate may still lose the ranking, so some affected operations can remain unavailable until the data is repaired.

Bound and observe the fallback

A pathological key should not cause an unbounded scan during a critical request. Cap diagnostic reads, raise a high-signal operational event, and log only what responders need. Opaque record identifiers may support repair; the sensitive lookup value often does not belong in logs.

The stronger end state lives in storage

After containment, the work is not finished. The durable sequence is usually:

inventory collisions
-> decide merge or deletion policy
-> repair existing rows
-> reject or reconcile conflicting writes
-> add the storage constraint
-> remove or narrow temporary recovery code

For relational data, that often means a unique index aligned with the normalization and null semantics used by the application. The write path should return a useful domain result when the constraint rejects a conflict. A pre-insert lookup can improve the message, but the database remains the concurrency-safe authority.

There is a real trade-off. Immediate enforcement gives the cleanest future guarantee but can break deployment against historical data. Temporary tolerance contains the exception but adds policy, cannot guarantee later verification will succeed, and can become permanent by neglect. Treat it as migration work with an owner, telemetry, and an exit condition.

Test the invariant and the escape hatch

Focused tests should cover more than “duplicates do not throw.” I would pin:

• the ordinary single-row path remains untouched;
• selection is stable when candidates arrive in different orders;
• inactive or retired records cannot outrank valid ones;
• unrelated exceptions escape unchanged;
• downstream verification still decides the outcome;
• diagnostics contain repairable identifiers but no sensitive key;
• the candidate read is bounded;
• the future uniqueness constraint rejects a conflicting concurrent write.

The deeper lesson is architectural: reads, writes, and storage must agree on cardinality. If a reader says “exactly one,” make that promise explicit at the boundary able to enforce it. Until then, the code is not reading an invariant. It is discovering, at runtime, whether the hope was true.

Comments

[Mads Hansen]

Treating Single as an executable invariant is a great review heuristic. I’d make the containment path carry an expiry: a feature flag, owner, collision-count SLO, and a removal gate tied to the validated unique index. Otherwise the fallback becomes the real data model. For PostgreSQL, the enforcement rollout can often be made safer with a dedupe ledger, a unique index built concurrently, and then an attached constraint where applicable; the exact normalized expression and NULL semantics must match the reader, or storage will enforce a subtly different key. One more risk: deterministic “winner” selection can split writes across duplicate records. During containment I’d make ambiguous keys read-only or route mutations through a canonicalization map, then replay tests that prove every API and background worker uses the same resolver.