• Implement proper null semantics for LIKE:x LIKE y returns true iff x and y are non-null and there's a match; otherwise false (Null semantics compensation missing for LIKE negation #26735).
• Remove exceptional behavior for StartsWith/EndsWith/Contains, so they now behave like any other function.
  • Switching to regular function null semantics means that these now receive compensation for 2-value logic just like any other function. Following the LIKE changes above, e.g.x.EndsWith(y) now returns true iff x and y are non-null and there's a match; otherwise false. This fixes negation issues such as"Where" clause with "!string.Contains" produces incorrect SQL #30493.
  • In addition, the case of an empty-string pattern - which in .NET returns true for any non-null string - now behaves consistently and returns false instead of true when the string being matched is null (this would throw in .NET).
• Optimize StartsWith/EndsWith/Contains when the pattern is a parameter, by performing escaping on it via our runtime parameter rewriting facility.
  • This required moving translations for these methods out of the method translators and into SqlTranslatingExpressionVisitor (for access to RegisterRuntimeParameter). We should allow translators to access the query compilation context, at which point the code can move back.
  • This means we now do client-side rewriting of both constants and parameters - generating optimal LIKE SQL; the only case where we don't is when the pattern is a column.

@maumar you'll probably want to take a look at this when you're back.

Closes#30493
Closes#11881
Closes#26735
Does part of#26634 (for Like)