The Predicate Your ORM Accepted but the Database Rejected

Passing model tests can still produce invalid DDL

ORM abstractions are useful precisely because they let us describe database intent without hand-authoring every statement. That convenience can also blur an important boundary: valid ORM metadata is not necessarily valid provider DDL.

A recent merged EF Core change illustrated this boundary. Two filtered unique indexes contained predicates joined with OR. EF Core accepted the filter strings into its model, and focused tests confirmed those exact strings. SQL Server rejected the filtered-index DDL because its filtered-index predicate grammar is narrower than the grammar accepted in ordinary queries.

Nothing was wrong with the tests as written. They answered their question correctly:

Did the model retain the filter text we configured?

The missing question was:

Can the target database execute the DDL produced from that text?

Those are different guarantees.

The evidence discussed here comes from the committed change and its tests. It should not be read as a claim that a database, migration, or test suite was rerun today.

Why OR crossed the abstraction boundary

EF Core's HasFilter accepts SQL text. Storing that text does not require EF Core to fully validate every rule imposed by every relational provider.

That distinction matters because SQL has multiple subgrammars. A predicate that is natural in a SELECT statement may still be unsupported in a filtered-index definition. Exact-string assertions can verify capitalization, quoting, and the intended expression while saying nothing about whether the provider will accept the resulting CREATE INDEX.

This is a classic abstraction leak: the model is structurally valid, but a provider-specific restriction becomes visible only when the migration reaches the database boundary.

Partition the set into provider-valid filters

Consider this generalized example. It is original illustrative code, not code from a private system:

var key = new[]
{
    nameof(Entry.Kind),
    nameof(Entry.ScopeId),
    nameof(Entry.NormalizedCode)
};

builder.HasIndex(key, "UX_Entry_Eligible")
    .IsUnique()
    .HasFilter("[Kind] = 1 OR [Kind] = 2");

Suppose kinds 1 and 2 represent two disjoint subsets, and Kind is part of the unique key. The same intent can be expressed using two supported predicates:

builder.HasIndex(key, "UX_Entry_Kind1")
    .IsUnique()
    .HasFilter("[Kind] = 1");

builder.HasIndex(key, "UX_Entry_Kind2")
    .IsUnique()
    .HasFilter("[Kind] = 2");

The explicit names are not cosmetic. Calling HasIndex repeatedly with the same property set can locate and reconfigure one existing index in the model. Separate names make the intended two-index structure unambiguous.

Partitioning also requires care. The filters must be disjoint, and the resulting indexes must preserve the original uniqueness rule. If the partition discriminator is absent from the key, splitting one unique index into two may permit a duplicate across the two subsets. That semantic proof belongs before the mechanical rewrite.

Keep migrations and snapshots aligned

When the affected migration has never been applied anywhere, correcting it in place can be cleaner than adding a second migration whose only job is to repair invalid DDL.

That option is safe only while the migration is genuinely unapplied. Once environments may have recorded it, rewriting history creates a different problem.

An in-place correction also needs both representations updated:

• The migration operations that create the indexes.
• The EF Core model snapshot used to calculate future differences.

Changing only one can leave future migration generation working from a model history that never actually existed.

Turn the incident into a model-wide invariant

Fixing two indexes removes the immediate defect. A model-wide guard makes the lesson reusable.

A lightweight generalized guard might inspect every filtered index and reject the known-invalid token:

var disallowedOr = new Regex(
    @"\bOR\b",
    RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);

foreach (var entityType in model.GetEntityTypes())
{
    foreach (var index in entityType.GetIndexes())
    {
        var filter = index.GetFilter();

        if (filter is not null && disallowedOr.IsMatch(filter))
        {
            throw new InvalidOperationException(
                $"Filtered index '{index.Name}' contains unsupported OR.");
        }
    }
}

Running this against the finalized model covers present and future entity configurations, including indexes added somewhere far from the original fix.

The guard should be described honestly. It is a bounded grammar rule, not a provider parser. A word-boundary expression is better than a plain substring search, but it can still be confused by comments, string literals, or quoted identifiers. It also cannot detect every other provider-specific restriction.

Its value is narrower: it turns one known failure mode into fast feedback.

Test at more than one layer

The strongest testing strategy separates the guarantees:

1. Model tests verify index shape, names, uniqueness, and filter metadata.
2. An invariant test rejects known-invalid patterns across the whole model.
3. Migration generation exposes the exact provider-shaped DDL for inspection.
4. Applying that DDL to a disposable compatible instance verifies what the real provider accepts.

Each layer catches a different class of error. The first two are fast and precise. Generation reveals the statement that must be reviewed; compatible database execution is where provider acceptance is actually proved.

The practical lesson is not to distrust ORM tests. It is to label their evidence accurately. A metadata assertion proves metadata. Generation exposes provider-shaped DDL. Executability requires evidence from a compatible database capable of running it.

What database claim does your current migration test suite actually prove?