Wrong collation in Migrations after updating to Pomelo 5.0.3

Question

We have updated from Pomelo 5.0.0-alpha.2 to 5.0.3. We are facing some issues now when creating new migrations with foreign keys:

migrationBuilder.AddColumn<Guid>(
                name: "UserId",
                table: "MyTable",
                type: "char(36)",
                nullable: true,
                collation: "ascii_general_ci");

This is our entity configuration:

public class MyTableEntityConfiguration : 
{
    public override void Configure(EntityTypeBuilder<MyTableEntity> builder)
    {
        base.Configure(builder);

        builder.HasOne(x=> x.User)
            .WithMany()
            .HasForeignKey(x=> x.UserId)
            .HasPrincipalKey(user => user.Id);
    }
}

And our entity:

public class MyTable
{
        public Guid? UserId { get; set; }
        public UserEntity User { get; set; }
}

A wrong collation ("ascii_general_ci") is now being added causing the migration to fail when it's executed:

Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE MyTable ADD CONSTRAINT FK_MyTable_Users_UserId FOREIGN KEY (UserId) REFERENCES Users (Id) ON DELETE RESTRICT;

The only solution we have found so far is to remove the "collation: "ascii_general_ci" in the migration. Is there a better solution to not add the collation (or only the correct one) in the migrations automatically? Our database is setup to use the "Default charset".

Answer 1

As mentioned by bricelam it was an issue/change in Pomelo provider and already reported in their github https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1477. Since version 5.0.3 you can add the following to your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.UseGuidCollation(string.Empty);
}

This will tell the provider to not add a default collation to the new migration scripts.