When using Entity Framework with MySQL, Unicode characters are replaced with basic characters

Question

Background

I noticed that when saving data from my MVC website through Entity Framework, if I had something like the Greek "α" it would be converted to "a".

Actions Taken

I overrode OnModelCreating in the database context and added the following code.

modelBuilder.Properties<string>().Configure(x => { x.HasColumnType("NVARCHAR"); x.IsUnicode(true); });

This initially looked promising as the newly generated migration had this structure.

AlterColumn("dbo.Item", "Name", c => c.String(maxLength: 800, storeType: "nvarchar"));

And after running the migration I saw the relevant columns had collation utf8_general_ci.

Persisting Problems

This changed nothing when saving data through my application. When passing Greek characters down from the website it still downgrades to a basic equivalent.

If I try to add these letters directly through MySQL Workbench however, it stores them just fine and the website will display correctly when retrieving the data.

Other Information

Using the database logging code below, I was able to see the SQL Entity Framework is using.

dbContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

The seemingly okay SQL.

SET SESSION sql_mode='ANSI';INSERT INTO `Item`(
`Name`, 
`Owner_Id`) VALUES (
@gp1, 
@gp2);

-- @gp1: 'The_α_1' (Type = String, IsNullable = false, Size = 7)

-- @gp2: '7a897e05-cc87-410b-bc80-70c75abae95b' (Type = String, IsNullable = false, Size = 36)

Any ideas? Thanks for any help.

Answer 1

MySQL allows for configuring several aspects of the client-server communication (according to the 10.4 Connection Character Sets and Collations documentation):

• Source (i.e. client) encoding: character_set_client
• Destination (i.e. server) encoding: character_set_connection
• Returned data and meta-data: character_set_results

I am guessing that it is assumed that the source encoding, coming from a Microsoft technology, is UTF-16 Little Endian.

As for the other two, the Connector/NET Connection-String Options Reference documentation states:

CharSet , Character Set

Specifies the character set that should be used to encode all queries sent to the server. Results are still returned in the character set of the result data.

The connection to MySQL needs to be told that the target encoding is UTF-8 (which is what your MySQL columns are using). MySQL is currently assuming that you are sending non-Unicode strings, effectively doing the same thing as converting to VARCHAR in SQL Server, assuming that the code page specified by the default Collation of the current Database is 1252 (Windows Code Page 1252 is commonly referred to as "ANSI", even if that is a technically inaccurate name).

The following shows the behavior in SQL Server by not prefixing the string with an upper-case "N":

SELECT 'α'; -- Database's default Collation = Latin1_General_100_CI_AS_SC
-- a

SELECT 'α'; -- Database's default Collation = Hebrew_100_BIN2
-- ?

Try the following to fix this:

1. First attempt should be to add the following to your connection string to send character data as UTF-8 to MySQL (this should just set character_set_connection):

   CharSet=utf8;
   

   Full Connection String example here

2. Second attempt should be to send a SQL command, upon initial connection, to set the session-level variable that controls the destination encoding:

   SET character_set_connection = utf8;
   

For more information, please see the following:

MySQL Charset/Collate

According to the "utf8 Collations" section of that page, it would be far better to use utf8_unicode_ci for the Collation instead of utf8_general_ci (to be clear, this recommendation has nothing to do with the character conversion issue being dealt with here).

---

P.S. This question / answer has a companion Q & A on DBA.StackExhange:

Why do I get incorrect characters when decoding a Base64 string to NVARCHAR in SQL Server?