Typed filters use SQL null semantics: != and ! on a nullable operand drop NULL rows, a captured null renders = NULL #68

Problem

A typed filter (HasComplexIndex(…, x => …), HasFilter<T>(x => …), AddComplexIndexFilter<T>, typed exclusion-constraint filters) translates comparisons with SQL's three-valued logic, not with the C# semantics the lambda has, and not with what EF Core renders for the same lambda in a query. Rows whose operand is NULL silently fall out of the partial index. For a unique "live rows" index, the 5.2.0 use case, uniqueness is not enforced on rows the application treats as live. No error at migrations add or at apply.

Only == null / != null written as a literal null became IS [NOT] NULL; a captured variable holding null was inlined as NULL, so that index holds no rows at all.

Reproduction (PostgreSQL 18, 5.4.1 and 5.5.0 candidate fd28ded)

public class Acct { public int Id { get; set; } public string Email { get; set; } = ""; public string? Kind { get; set; } }

b.HasComplexIndex(x => x.Email, x => x.Kind != "federated", isUnique: true, indexName: "ux_email_not_federated");

Scaffolds CREATE UNIQUE INDEX "ux_email_not_federated" ON accts ("Email") WHERE "Kind" <> 'federated';. Then:

ctx.Accts.Add(new Acct { Email = "b@x", Kind = null });
ctx.Accts.Add(new Acct { Email = "b@x", Kind = null });
ctx.SaveChanges();                                                   // succeeds
ctx.Accts.Count(x => x.Kind != "federated" && x.Email == "b@x");     // 2

Both duplicates are accepted. The same with the captured null: WHERE "Name" = NULL, and the unique index enforces nothing.

Fix

Translate comparisons with C# semantics, the way EF does, for operands that can be null (a Nullable<T>, or a reference type not annotated non-nullable):

• a != v → (a <> v OR a IS NULL); both sides nullable → EF's form
• a negation over a comparison with a nullable operand is pushed down (De Morgan) instead of wrapping NOT (…) around a comparison that can be NULL
• a parameter-free operand that evaluates to null is a null comparison: IS [NOT] NULL

Predicates whose operands cannot be null translate exactly as before, so their indexes do not change.

Changed answer

Fixing this changes what an existing model scaffolds: the first migrations add after upgrading drops and re-creates every partial index and exclusion constraint whose typed filter compares a nullable operand under != or !, or against a captured null. A silent fix ships in a minor, with a ### Changed answers row.

Found in the 5.5.0 pre-tag analysis.