Pomelo.entityframeworkcore.mysql: Error applying migrations: uniqueidentifier, datetime2

[aramirezh-dev]

Steps to reproduce

/***   Guid   ***/
modelBuilder.Entity("EntityFrameworkDataAccess.Entities.Account", b =>
                {
                    b.Property<Guid>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid>("CustomerId")
                        .HasColumnType("uniqueidentifier");

                    b.HasKey("Id");

                    b.ToTable("Account");
                });

/***   datetime2   ***/
 modelBuilder.Entity("EntityFrameworkDataAccess.Entities.Credit", b =>
                {
                    b.Property<Guid>("Id")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("uniqueidentifier");

                    b.Property<Guid>("AccountId")
                        .HasColumnType("uniqueidentifier");

                    b.Property<double>("Amount")
                        .HasColumnType("float");

                    b.Property<DateTime>("TransactionDate")
                        .HasColumnType("datetime2");

                    b.HasKey("Id");

                    b.ToTable("Credit");
                });

The issue

When I try to apply a migration create from SQL Server to MySQL, it fails when it is changing the column type from uniqueidentifier (GUID) to char(36)

Exception message:
Stack trace:
MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uniqueidentifier(36) NOT NULL,

another

MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datetime2(6) NOT NULL....

Further technical details

MySQL 5.7.3:
Operating system: Windows
Pomelo.EntityFrameworkCore.MySql version: 3.0.0-rc2.final
Microsoft.AspNetCore.App version: 3.0.0

[lauxjpn]

Guid

Your code:
```c#
b.Property("AccountId")
.HasColumnType("uniqueidentifier");

MySQL does not support a type named `uniqueidentifier`. But Pomelo *does* supports multiple options to save a `Guid` in a MySQL table.

See `GuidFormat` on the [MySQL ConnectionString Options](https://mysqlconnector.net/connection-options/) page of MySqlConnector (the underlying library we are using).

The default mapping for `System.Guid` is `char(36)`, which will work out-of-the box without explicitly specifying the `GuidFormat`.

Working code:
```c#
b.Property<Guid>("AccountId")
    .HasColumnType("char(36)");

DateTime

Your code:
```c#
b.Property("TransactionDate")
.HasColumnType("datetime2");

MySQL does not support a type named `datetime2`. It supports the follow date/time related data types:
- `time`
- `date`
- `datetime`
- `timestamp`

The closest data type MySQL >= 5.6.4 supports to a MSSQL `datetime2` is `datetime(6)`, which will automatically be chosen for a given `datetime`, if your MySQL version supports it.

Working code:
```c#
b.Property<DateTime>("TransactionDate")
    .HasColumnType("datetime");

Supported types in general

See the MySQL docs for all supported data types.

[aramirezh-dev]

We need support both database engines (SQL Server and MySQL).
I reviewed GuidFormat on the MySQL ConnectionString Options, and it still failed.

After compare Pomelo v2.2.6 with the latest 3.0.0-rc, we have to remove .HasColumnType("uniqueidentifier") and .HasColumnType("datetime2") from Migrations.cs and Migrations.Designer.cs, also the ContextModelSnapshot.cs and it works, but the idea is prevent the customization of Up and Down methods by migration

[lauxjpn]

Take a look at the following two MSDN articles for using multiple providers:

• Migrations with Multiple Providers
• Alternating between multiple models with the same DbContext type

Also, as you figured out yourself, you can fall back on a provider's default type mapping implementation by just removing the HasColumnType() call all together.

---

I reviewed GuidFormat on the MySQL ConnectionString Options, and it still failed.

You need to use GuidFormat in conjunction with your model definition. My previous samples should work without issues. If they don't, please post the exact model definition code you tried and the corresponding GuidFormat connection string option you used.