Typed expression indexes render Substring and Trim differently from EF Core's queries, so no query uses them #72

Problem

PostgreSQL uses an expression index only for a query whose expression matches it. Typed expression indexes (HasExpressionIndex(x => …)) render Substring and Trim differently from Npgsql's query translation of the same lambda, so the index applies and no EF query uses it. The same class of defect 5.4.0 fixed for JSON members.

Measured against ToQueryString() on Npgsql 10.0:

substr and substring are different functions to the planner, and btrim(x) trims spaces only. Evidence from the PostgreSQL analysis (PG 18, enable_seqscan=off): WHERE substring("Code",2,3)='abc' is a Seq Scan against an index on substr(...); a btrim(x, E' \t\n\r') query is a Seq Scan against an index on btrim(x). And a unique index on btrim("Code") accepts both 'Y\t' and 'Y', which Trim() treats as equal: the uniqueness the model declares is not what the database enforces.

ToLower, ToUpper, Replace, ??, Length (EF's ::int on length() is a no-op cast) and string concatenation of non-nullable operands already match.

Fix

Render substring(…) and btrim/ltrim/rtrim(…, E' \t\n\r'), as Npgsql's query translation does, and pin each against ToQueryString() the way NpgsqlJsonMemberRenderingTests does for JSON members.

Changed answer

Typed expression indexes over Substring, Trim, TrimStart or TrimEnd re-create on the first migrations add after upgrading (the stored template changes). A unique trimmed index may then refuse rows that differ only in tabs or line breaks at the ends, which are the duplicates it was declared to refuse. Ships in a minor with a ### Changed answers row.

Found by the 5.5.0 pre-tag analysis. Present since typed expression indexes shipped.