Use case-insensitive string key comparisons on SQL Server

[ajcvickers]

For example, on SQL Server.

See #27467

[leppie]

Also remember about how some databases deal with (and basically ignore) trailing whitespace.

[Timovzl]

Reading the change, I'm trying to understand if this will cause any string key to be compared in a case-insensitive fashion. That seems like a risky assumption. I have used alphanumeric keys in the past, and they are very much case-sensitive. It's a dangerous assumption that casing can be ignored, even more so in the presence of a case-sensitive collation.

[ajcvickers]

@Timovzl This changes the comparisons done by EF by default to be consistent with the comparisons done by SQL Server by default. If you have configured SQL Server to be case-sensitive in general or in this particular case, then switching back to case-sensitive in EF would make sense, but that is rare in my experience.

[Timovzl]

@ajcvickers Thanks for clearing that up!

To switch EF back to case-sensitive, would the sensible approach be to configure a convention for string and pass a case-sensitive comparer to it using HaveConversion()?

[ajcvickers]

@Timovzl I would do this, with whatever comparer makes sense for the key: https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#use-case-insensitive-string-keys

[shaofing]

A global or table level switch should be set to address compatibility issues during upgrades

[houbi56]

Would it be possible for EF to control this behaviour depending on collation?

[Timovzl]

Would it be possible for EF to control this behaviour depending on collation?

@houbi56 I wholeheartedly agree with this suggestion.

In fact, I currently use a custom convention that checks the collation for the substring "_CI" and uses it to decide between an Ordinal vs. an OrdinalIgnoreCase ValueComparer. It passes that to SetProviderValueComparer() and, if the ClrType is string, also to SetValueComparer(). I'll include the code below in case it's of use to anyone.

Ignoring casing by default is risky. With it, case-sensitive string IDs can collide in-memory, even if the developer has correctly chosen a case-sensitive collation for their column.

Side note: When using DDD-style custom ID types, with conversions and comparisons appropriately mapped, the default convention for strings is more of a just-in-case configuration than a necessity.

Convention for Collation-Based String Casing

/// <summary>
/// A convention that uses string comparisons with case-sensitivity matching each relevant column's collation, for every property mapped to a string column.
/// This helps align comparisons with those made by the database.
/// </summary>
internal sealed class StringCasingConvention : IModelFinalizingConvention
{
	private static readonly ValueComparer OrdinalComparer = new ValueComparer<string>(
		equalsExpression: (left, right) => String.Equals(left, right, StringComparison.Ordinal),
		hashCodeExpression: value => String.GetHashCode(value, StringComparison.Ordinal),
		snapshotExpression: value => value);

	private static readonly ValueComparer OrdinalIgnoreCaseComparer = new ValueComparer<string>(
		equalsExpression: (left, right) => String.Equals(left, right, StringComparison.OrdinalIgnoreCase),
		hashCodeExpression: value => String.GetHashCode(value, StringComparison.OrdinalIgnoreCase),
		snapshotExpression: value => value);

	public void ProcessModelFinalizing(IConventionModelBuilder modelBuilder, IConventionContext<IConventionModelBuilder> context)
	{
		foreach (var property in modelBuilder.Metadata.GetEntityTypes().SelectMany(entityBuilder => entityBuilder.GetProperties()))
		{
			// Either a plain string property or a property mapped to string
			if (property.ClrType != typeof(string) && property.GetValueConverter()?.ProviderClrType != typeof(string))
				continue;

			var collation = property.FindAnnotation(RelationalAnnotationNames.Collation) ??
				modelBuilder.Metadata.FindAnnotation(RelationalAnnotationNames.Collation);

			// Use case-sensitive comparisons unless ignore-case is explicitly used by the collation
			var comparer = collation?.Value is string collationName && collationName.Contains("_CI", StringComparison.OrdinalIgnoreCase)
				? OrdinalIgnoreCaseComparer
				: OrdinalComparer;

			// We already confirmed that the provider type is string
			if (property.GetProviderValueComparer() is null)
				property.SetProviderValueComparer(comparer, fromDataAnnotation: true); // Using fromDataAnnotation=true allows explicit configuration to override ours

			// The model type COULD be string
			if (property.ClrType == typeof(string) && property.GetValueComparer() is null)
				property.SetValueComparer(comparer, fromDataAnnotation: true); // Using fromDataAnnotation=true allows explicit configuration to override ours
		}
	}
}