Holistic approach to database collations #19866
Description
Differences in string comparison semantics in C#/.NET verses different databases has always been a a usability issue.
We decided some time ago that string comparisons that don't specify any StringComparison value will use the database semantics. This results in the most expected (and fast) queries for code written in the most common way.
One thing we want to avoid is a slow, index-missing query being generated without any explicit opt-in to this.
However, if we know the database collation, then we can potentially translate more queries with acceptable fidelity and perf, while at the same time throwing very specific messages for things we can't translate. See thread here: #1222 (comment)
This needs to involve migrations and model building as well as queries, so this issue is tracking a more holistic approach to this which covers all aspects.
Related:
- Ability to specify database collation #6577 (Ability to specify database collation)
- Specify collation on columns #19275 (Specify collation on columns)
- Support per-operation collation using EF.Functions.Collate() or equivalent #8813 (Support per-operation collation using EF.Functions.Collate() or equivalent)
- #673 (Compensate for different behavior of string comparisons in client and store)
- Query: Consider translating String.Equals(String, StringComparison) for selected values of StringComparison #1222 (Consider translating String.Equals(String, StringComparison) for selected values of StringComparison)
- Batching through a table variable may not work for non-default collations #7172 (Batching through a table variable may not work for non-default collations)
- Add type mapping support for additional store type postfix #11896 (Add type mapping support for additional store type postfix)