Razor page is messing up the encoding when assigning special character to string

c# entity-framework-core mysql razor special-characters

Question

UPDATE 4:

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.


The issue:

No matter what I do the characters I insert into the MySQL 5.7.17 database are always displayed as �:

Database lookup

Although there are similar posts to this, none of the solutions worked for me and I've exhausted my research capabilities.

What I've tried:

The code:

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 = "mail@gmail.com";
        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 = "mail@gmail.com";
        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>

Database Configuration

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!


UPDATE:

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 = "mail@gmail.com";
    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?


UPDATE 2:

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?


1
2
7/11/2019 2:56:23 PM

Accepted Answer

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 »

Solution:

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.

1
7/11/2019 1:16:34 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow