EF Core error when applying update-database

Question

I’m creating the table AspNetUSers while applying update-database

The following error occurs

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 '(6) NULL, LockoutEnabled bit NOT NULL, AccessFailedCount int NOT N' at line 14

I’m using a mysql database And from what I understand the problem occurs on this line LockoutEnd = table.Column<DateTimeOffset>(nullable: true),

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "AspNetUSers",
            columns: table => new
            {
                Id = table.Column<string>(nullable: false),
                UserName = table.Column<string>(nullable: true),
                NormalizedUserName = table.Column<string>(nullable: true),
                Email = table.Column<string>(nullable: true),
                NormalizedEmail = table.Column<string>(nullable: true),
                EmailConfirmed = table.Column<bool>(nullable: false),
                PasswordHash = table.Column<string>(nullable: true),
                SecurityStamp = table.Column<string>(nullable: true),
                ConcurrencyStamp = table.Column<string>(nullable: true),
                PhoneNumber = table.Column<string>(nullable: true),
                PhoneNumberConfirmed = table.Column<bool>(nullable: false),
                TwoFactorEnabled = table.Column<bool>(nullable: false),
                LockoutEnd = table.Column<DateTimeOffset>(nullable: true),
                LockoutEnabled = table.Column<bool>(nullable: false),
                AccessFailedCount = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AspNetUSers", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "AspNetUSers");
    }

I pulled the field //LockoutEnd = table.Column<DateTimeOffset>(nullable: true), and ends up creating the tables. But how do I make this field compatible with mysql

Answer 1

When creating the entity, the Mysql Provider looks at the property you pass in the mapping Type Constraints, such as DateTimeOffSet is not a guy Nullable<T> then the mapping breaks, try to change the quoted line to:

LockoutEnd = table.Column<DateTimeOffset?>(nullable: true)

UPDATE

After the comments below, it was seen that the problem is of the MYSQL file that does not correctly interpret the data type Datetimeoffset because it is a data type unique to SQL Server, see question by Soen. In the Provider Pomelo.Mysql documentation there is a Workarround to work with Datetimeoffset.

Basically there are two alternatives to fix the problem:

Convert Datetimeoffset to Datetime UTC and store the data.

• Pros:
  1. Comparison and ordering will be worked by the database itself;
  2. You will store only 8 bytes of space for each information in the database.
• Cons:
  1. Timeoffset (SQL) context can be broken;
  2. Timezone (SQL) context can be broken.

Store a String representation of the date in the database and use an uncharted property with the conversion to the desired data type.

• Pros:
  1. Timeoffset (SQL) context will be preserved;
  2. Timezone (SQL) context will be preserved.
• Cons:
  1. Ordering and comparison will not be the ordering of the database mechanism;
  2. The size of the information in the database can be up to 33 bytes ("* 2016-10-10T13:49:58.2065722+00:00").

The documentation (at this link) this clearer and offers reference material:

Datetime vs Datetimeoffset

Storing Datetime and Datetimeoffset