SQL Server UTF8 collations

[egbertn]

This does not behave as documented and expected.

If I have a entity for which I use Fluent API to define properties...
The SQL field (in my example) is varchar(255) using collation Latin1_General_100_BIN2_UTF8
in EF defined as
p.Property(prop => prop.Param).IsUnicode(false).UseCollation("Latin1_General_100_BIN2_UTF8").HasMaxLength(255);

However, unicode chars get's corrupted anyway on SQL both on Azure as on 2019 express.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: b27be469-3758-941c-0c5d-3a54b1545493
• Version Independent ID: d89924aa-668e-229c-8170-5731c3fff4c1
• Content: Collations and case sensitivity - EF Core
• Content Source: entity-framework/core/miscellaneous/collations-and-case-sensitivity.md
• Product: entity-framework
• Technology: entity-framework-core
• GitHub Login: @roji
• Microsoft Alias: avickers

[roji]

Can you post a runnable code sample which shows the corruption occurring?

[egbertn]

Can you post a runnable code sample which shows the corruption occurring?

Sure,
there you go. Thanks for your commitment.

https://github.com/egbertn/EFCore-5-UTF-8-support

[ajcvickers]

Note for triage: this is because the DbType is set to AnsiString when IsUnicode is false. Below is a repro without EF Core. Changing the DbType to String makes it work.

Not using IsUnicode(false) makes the test pass, but creates an nvarchar(255) column, which apparently isn't desirable for SQL Server's squirrely UTF8 support.

static class Program
{
	static void Main()
	{
		using (var connection = new SqlConnection(@"Server=localhost;Database=master;Trusted_Connection=True"))
		{
			connection.Open();

			using (var command = connection.CreateCommand())
			{
				command.CommandText = @"DROP DATABASE [UTF8];";
				command.ExecuteNonQuery();
				
				command.CommandText = @"CREATE DATABASE [UTF8];";
				command.ExecuteNonQuery();
			}
			
			connection.Close();
		}

		using (var connection = new SqlConnection(@"Server=localhost;Database=UTF8;Trusted_Connection=True"))
		{
			connection.Open();

			using (var command = connection.CreateCommand())
			{
				command.CommandText = @"
      CREATE TABLE [MyTable] (
      [Id] bigint NOT NULL IDENTITY,
      [Param] varchar(255) COLLATE Latin1_General_100_BIN2_UTF8 NULL,
      CONSTRAINT [PK_MyTable] PRIMARY KEY ([Id]) );";

				command.ExecuteNonQuery();

				command.CommandText = @"INSERT INTO [MyTable] ([Param]) VALUES (@p0);";
				
				var parameter = command.CreateParameter();
				parameter.DbType = DbType.AnsiString;
				parameter.Size = 255;
				parameter.ParameterName = "p0";
				parameter.Value = "Как дела?";
				command.Parameters.Add(parameter);

				command.ExecuteNonQuery();

				command.CommandText = @"SELECT TOP(1) [m].[Id], [m].[Param] FROM [MyTable] AS [m] ORDER BY [m].[Id] DESC";
				using (var reader = command.ExecuteReader())
				{
					reader.Read();
					var value = reader.GetFieldValue<string>(reader.GetOrdinal("Param"));

					if (value != "Как дела?")
					{
						Console.WriteLine($"Wrote {parameter.Value}, read {value}.");
					}
				}
			}
			
			connection.Close();
		}
	}
}

[ajcvickers]

Notes from triage:

• It's not clear how to fix this; it will likely require type mappings to understand collations, which is not trivial.
• For now, the workaround is to use IsUnicode(true) (which is the default) and to edit the migration to generate a varchar column.

[egbertn]

Thanks @ajcvickers

Notes from triage:

* It's not clear how to fix this; it will likely require type mappings to understand collations, which is not trivial.

* For now, the workaround is to use `IsUnicode(true)` (which is the default) and to edit the migration to generate a `varchar` column.

Thanks, but isn't it a solution, to see if the collation name contains or ends with _utf8, if so, all string operations must be prepended with N like in N'как лела?'

[roji]

@egbertn that could be a fix, but it would "require type mappings to understand collations, which is not trivial" as @ajcvickers wrote above. It's unfortunately too late to go into something like this for 6.0.