Set charset and collation EF Core 3 code first

Question

question

Ehsan-9412 asked Oct 2, '21 AgaveJoe answered Oct 26, '21

Set charset and collation EF Core 3 code first

Using ASP.Net core and Identity 3.1, I have a web app and using Pomelo package to connect to mysql/MariaDB as my databsae.

I can alter my tables and columns after database is created to change my collation and charset but looking for a way to set my favorite collation and char set on model creating (I mean at the same time the update-database is running from my web-app and during the creation of my database, I need to set the correct charset and collation).
I can set my collation/charset for a column like this but doing like this for all columns and tables is frustrating and also I have tables of Identity which shoud be set for collation/charset too.

 protected override void OnModelCreating(ModelBuilder modelBuilder)
         {
             base.OnModelCreating(modelBuilder);
    
             modelBuilder.Entity<Table1>().Property(obj => obj.Column1).HasCharSet("utf8mb4").HasCollation("utf8mb4_unicode_ci");    
    
 .  .  .

I need a code to have all my databse, tables and columns be set for an specific charset and collation.

I searched around and tried something like this :

 MySqlPropertyBuilderExtensions.HasCharSet(CharSet.???,"utf8mb4").HasCollation("utf8mb4_unicode_ci");

 modelBuilder.HasCharSet(CharSet.???).UseCollation("utf8mb4_general_ci");

But I can't manage it.

dotnet-aspnet-core-mvc azure-database-mariadb

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-10-02T10:48:45.303Z

AgaveJoe answered Oct 2, '21

Simply open your favorite MySql editor and execute a script to set the collation for the database. It is a one time script.

if you want the script as part of a migration, create an empty migration and add the script to the Up method.

Using MigrationBuilder.Sql()

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ehsan-9412 · Oct 02, 2021 at 12:47 PM

 ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The upper code does not change tables and columns, but only effects on database.

Thanks for replying

0 ·

AgaveJoe Ehsan-9412 · Oct 02, 2021 at 03:56 PM

I don't understand your response. According to the MySQL documentation, the default collation is used if table or column is not set.

10.3.2 Server Character Set and Collation
10.5 Configuring Application Character Set and Collation
10.3.3 Database Character Set and Collation
10.3.4 Table Character Set and Collation
10.3.5 Column Character Set and Collation

0 ·

question