Efcore: Holistic approach to database collations

[ajcvickers]

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: https://github.com/dotnet/efcore/issues/1222#issuecomment-582662058

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:

• [x] #6577 (Ability to specify database collation)
• [x] #19275 (Specify collation on columns)
• [x] #8813 (Support per-operation collation using EF.Functions.Collate() or equivalent)
• [ ] #673 (Compensate for different behavior of string comparisons in client and store)
• [ ] #1222 (Consider translating String.Equals(String, StringComparison) for selected values of StringComparison)
• [ ] #7172 (Batching through a table variable may not work for non-default collations)
• [x] #11896 (Add type mapping support for additional store type postfix)

[ajcvickers]

Also see high-level collations issue #19866

[roji]

@ajcvickers this is #19866 :trollface:

[smitpatel]

Circular references.

[ajcvickers]

Stack overflow.

[roji]

Here are the docs for PostgreSQL collations. tl;dr a collation can be specified at the database level, at the column level (when creating it), or explicitly in the query on. See also this note for why handling collation as part of the type mapping doesn't seem to make sense (for PostgreSQL).

[roji]

Following our design discussion, here's what we plan to do for 5.0:

• Allow users to specify a collation at the database level (#6577) and at the column level (#19275). This are purely metadata which affect migrations.
• Create a new EF.Functions.Collate() method which allows specifying an explicit collation in queries (#8813).

At the moment, we don't plan to translate any string equality/comparison which accept the StringComparison enum (#1222) - see https://github.com/dotnet/efcore/issues/1222#issuecomment-611113142.

[roji]

@ajcvickers I think we've done everything here that we want to do for 5.0 - the issues that remain are in the backlog and I'm not sure we need this issue to track them.

We may also consider closing #673 as we don't intend to do it (instead the decision was to point at the docs).