update 5.0.0,guid collation: "ascii_general_ci" foreign key association

[zhangzw218]

Steps to reproduce

1.add Pomelo.EntityFrameworkCore.MySql (>= 5.0.0-alpha.2) 2.create entity TestRole

public class TestRole{
public Guid Id{get;set;}
public string Name{get;set;}
}

3.Add-Migration Initial 4.update Pomelo.EntityFrameworkCore.MySql (>= 5.0.0) 5.create entity TestUser

public class TestUser{
public Guid Id{get;set;}
public string Name{get;set;}
public TestRole TestRole {get;set;}
}

6.Add-Migration AddTestUser

I've posted it elsewhere and was finally directed to this side: https://github.com/abpframework/abp/issues/17728#issue-1912525090

github demo

The issue

Pomelo 5.0.0-alpha.2 add-migration（Should this behavior be consistent with 3.1.1?） Id = table.Column(type: "char(36)", nullable: false), Name = table.Column(type: "varchar(40) CHARACTER SET utf8mb4", maxLength: 96, nullable: true),

Pomelo 5.0.0 add-migration Id = table.Column(type: "char(36)", nullable: false, collation: "ascii_general_ci"), Name = table.Column(type: "varchar(40)", maxLength: 40, nullable: true).Annotation("MySql:CharSet", "utf8mb4")

This causes two problems: 1.one serious problem is that when foreign keys are associated, the old table Id and the new table foreign key RoleId cannot be associated:

Create table 'migrationdemo/apptempusers' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

我在这里看到了可能的解决方案: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1477#issue-951860230

modelBuilder.UseGuidCollation(string.Empty)

After the addition, a large number of migration scripts appeared:

            migrationBuilder.AlterColumn<Guid>(
                name: "Id",
                table: "IdentityServerPersistedGrants",
                type: "char(36)",
                nullable: false,
                oldClrType: typeof(Guid),
                oldType: "char(36)")
                .OldAnnotation("Relational:Collation", "ascii_general_ci");

At this point, the migration script was successfully submitted to the database, which solved my problem, but I wondered if there were any hidden dangers, and what impact would this sentence from OldAnnotation have?

Also, is there any way not to generate these migration statements? I want to keep the same behavior as the previous version, you know?

2. a minor problem: a large number of string encoding remigrations:

            migrationBuilder.AlterColumn<string>(
                name: "Value",
                table: "AbpSettings",
                type: "varchar(2048)",
                maxLength: 2048,
                nullable: false,
                oldClrType: typeof(string),
                oldType: "longtext CHARACTER SET utf8mb4(2048)",
                oldMaxLength: 2048)
                .Annotation("MySql:CharSet", "utf8mb4")
                .OldAnnotation("MySql:CharSet", "utf8mb4");

These migration scripts do not appear to have any real impact on the database: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1437#issue-910800193 See here, these migration scripts are definitely generated, so don't bother?

Further technical details

MySQL version: Operating system: win10 Pomelo.EntityFrameworkCore.MySql version:5.0.4 Microsoft.AspNetCore.App version:5.0

Other details about my project setup: