Oh wow, apparently it's not an EF or configuration issue.
Somehow the .razor page variable assigment is messing up the data.
Not sure how to proceed with the question now, but I at least edited the title to reflect current issue which is: Razor page is messing up the encoding when assigning special character to string.
No matter what I do the characters I insert into the MySQL 5.7.17 database are always displayed as �:
Although there are similar posts to this, none of the solutions worked for me and I've exhausted my research capabilities.
"charset=utf8mb4" on connection string and "SET character_set_connection" SQL command: When using Entity Framework with MySQL, Unicode characters are replaced with basic characters
Use "utf8mb4": MySQL charset=UTF-8 with ConnectionReset=True is not working
Data annotations like [MySqlCharset("utf8mb4")]: https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework-core-charset.html
Prefix N on query values: MySQL C# Text Encoding Problems
Connection String:
"MySQL": "server=localhost;user=root;database=sgn;charset=utf8mb4;"
UserModel.cs
namespace source.Data.Models
{
[MySqlCharset("utf8mb4")]
[MySqlCollation("utf8mb4_unicode_ci")]
public class UserModel
{
public UserModel() { }
[Key]
public long Id { get; set; }
[MySqlCharset("utf8mb4")]
[MySqlCollation("utf8mb4_unicode_ci")]
public string Username { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Language { get; set; }
public string StartPage { get; set; }
}
}
DatabaseContext.cs
namespace source.Data.Database
{
public class DatabaseContext : DbContext
{
private IConfiguration Configuration;
public DbSet<UserModel> Users { get; set; }
public DatabaseContext(DbContextOptions<DatabaseContext> options, IConfiguration configuration)
: base(options)
{
Configuration = configuration;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySQL(Configuration.GetSection("AppSettings").GetSection("Database").GetSection("ConnectionString")["MySQL"]);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<UserModel>().Property(p => p.Username).IsUnicode(true);
base.OnModelCreating(modelBuilder);
}
}
}
DesignTimeDatabaseContextFactory.cs
namespace source.Data.Database
{
public class DesignTimeDatabaseContextFactory : IDesignTimeDbContextFactory<DatabaseContext>
{
private IConfiguration Configuration;
private AppSettingsService AppSettings;
public DesignTimeDatabaseContextFactory()
{
}
public DesignTimeDatabaseContextFactory(IConfiguration configuration, AppSettingsService appSettings)
{
Configuration = configuration;
AppSettings = appSettings;
}
public DatabaseContext CreateDbContext(string[] args)
{
var builder = new DbContextOptionsBuilder<DatabaseContext>();
builder.UseMySQL(AppSettings.ConnectionString);
return new DatabaseContext(builder.Options, Configuration);
}
}
}
Create.razor - This is where I add the user to the database. Note that this project uses Blazor so don't be confused by the "@functions" tag. Neither function adds the data properly and commented is the prepared statement that I've tried.
<div class="login_form">
<h3>Create Account</h3>
<button @onclick="@(e => AddEntry())">Add DB User!</button><br />
<button @onclick="@(e => AddEntry2())">Add TEST!</button><br />
</div>
@functions {
void AddEntry2()
{
UserModel test = new UserModel();
test.Username = "Test çã";
test.FirstName = "Michel";
test.LastName = "Arendt";
test.Email = "[email protected]";
test.Password = "123";
test.Language = "pt-BR";
test.StartPage = "/Home/";
db.Database.ExecuteSqlCommand("SET NAMES utf8mb4");
db.Database.ExecuteSqlCommand("SET character_set_connection = utf8mb4");
db.Database.ExecuteSqlCommand("INSERT INTO Users (Username) VALUES (N'" + test.Username + "')");
db.SaveChanges();
//using (SqlConnection connection = new SqlConnection(AppSettings.DatabaseConnectionString()))
//{
// connection.Open();
// SqlCommand command = new SqlCommand(null, connection);
// // Create and prepare an SQL statement.
// command.CommandText =
// "INSERT INTO Users (Username) " +
// "VALUES (@username)";
// SqlParameter username = new SqlParameter("@username", SqlDbType.NVarChar, 100);
// username.Value = "Garçon";
// command.Parameters.Add(username);
// // Call Prepare after setting the Commandtext and Parameters.
// command.Prepare();
// command.ExecuteNonQuery();
//}
}
void AddEntry()
{
UserModel waitress = new UserModel();
waitress.Username = "Garçon";
waitress.FirstName = "Test";
waitress.LastName = "Test";
waitress.Email = "[email protected]";
waitress.Password = "123";
waitress.Language = "pt-BR";
waitress.StartPage = "/Home/";
db.Users.Add(waitress);
db.SaveChanges();
}
}
source.csproj (Dependencies reference)
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>netcoreapp3.0</TargetFramework>
<LangVersion>7.3</LangVersion>
<RestorePackages>false</RestorePackages>
<Version>0.10.0</Version>
<Authors>Michel Arendt</Authors>
<Product>SGN</Product>
</PropertyGroup>
<PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
<DefineConstants>DEBUG;TRACE</DefineConstants>
<DebugType>full</DebugType>
<DebugSymbols>true</DebugSymbols>
</PropertyGroup>
<ItemGroup>
<Content Remove="compilerconfig.json" />
</ItemGroup>
<ItemGroup>
<None Include="compilerconfig.json" />
</ItemGroup>
<ItemGroup>
<PackageReference Include="ElectronNET.API" Version="5.22.13" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.4" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.4" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.2.4">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="2.2.0" />
<PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="3.0.0-preview6.19304.6" />
<PackageReference Include="Microsoft.Extensions.Options.ConfigurationExtensions" Version="2.2.0" />
<PackageReference Include="MySql.Data.EntityFrameworkCore" Version="8.0.16" />
<PackageReference Include="System.Configuration.ConfigurationManager" Version="4.5.0" />
</ItemGroup>
<ItemGroup>
<Content Update="electron.manifest.json">
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
</Content>
</ItemGroup>
</Project>
Default collation and character set for the database and columns are utf8mb4_unicode_ci and utf8mb4 respectively:
Default database character set and collation
Columns character set and collation
Any ideas on what else I might try in order to fix this issue or what mistake I'm making?
Thanks in advance!
I switched to Pomelo.EntityFrameworkCore.MySql as suggested by Bradley Grainger (on comments) but that didn't help fixing the issue. My changed files are now:
Changed the build from Startup.cs and DesignTimeDatabaseContextFactory.cs to use:
builder
.UseMySql(AppSettings.ConnectionString,
mysqlOptions =>
{
mysqlOptions
.CharSetBehavior(CharSetBehavior.AppendToAllColumns)
.AnsiCharSet(CharSet.Utf8mb4)
.UnicodeCharSet(CharSet.Utf8mb4);
});
Create.razor
void AddEntry2()
{
UserModel test = new UserModel();
test.Username = "Test çã";
test.FirstName = "Michel";
test.LastName = "Arendt";
test.Email = "[email protected]";
test.Password = "123";
test.Language = "pt-BR";
test.StartPage = "/Home/";
using (MySqlConnection connection = new MySqlConnection(AppSettings.ConnectionString))
{
connection.Open();
MySqlCommand command = new MySqlCommand(null, connection);
// Create and prepare an SQL statement.
command.CommandText =
"INSERT INTO Users (Username) " +
"VALUES (@username)";
MySqlParameter username = new MySqlParameter("@username", MySqlDbType.LongText, 100);
username.Value = "Garçon";
command.Parameters.Add(username);
// Call Prepare after setting the Commandtext and Parameters.
command.Prepare();
command.ExecuteNonQuery();
}
}
Thought it would help so I've also set up the MySQL initialization (my.ini) on my local server to:
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
However, nothing worked. Any other ideas?
To sort out if it was a Database or EF Core issue I've set up a simple PHP script to insert the same entry on the database.
On this image entry 1 and 2 are inserted from EF Core while entry 3 was inserted from PHP
As can be seen from the image the database appears to be fine since the insert from PHP worked fine.
How to ensure that EF Core is using "utf8mb4" encoding when adding the entry?
As suggested by Thomas Schmidt on the comments of the OP, the issue was somewhere else I hadn't thought of which was the encoding that Visual Studio was saving the file with. The solution is described on this question:
razor view » character rendered as »
Select the file you wish to change the encoding for and hit the File menu, then Save "PAGE" As then select Save with Encoding... and select the desired encoding.
I've edited the question title for the post to reflect the current issue. Let me know if I should change it back as I'm not sure of how to proceed here.
