Set charset and collation EF Core 3 code first

Ehsan 41 Reputation points
2021-10-02T05:44:59.13+00:00

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.

Azure Database for MariaDB
Azure Database for MariaDB
An Azure managed MariaDB database service for app development and deployment.
55 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,553 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. AgaveJoe 28,291 Reputation points
    2021-10-02T10:48:45.303+00:00

    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()


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.