Bug #89586 .Net Core2 EF MySQL having issue while changing foreign key column to nullable
Submitted: 8 Feb 2018 9:45 Modified: 13 Feb 2018 6:13
Reporter: Harshvardhan Sharma Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.10.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: .NetCore, connector, MySQL, MySql.Data.EntityFrameworkCore

[8 Feb 2018 9:45] Harshvardhan Sharma
Description:

I am working on an application where I am using .Net Core 2, EF Core and MySQL as database server via Code First approach.

I have 2 tables:

    User
    Employee

User table is the main table which contains the user information and Employee table is the child table which has a column ID_User as shown below:

 public class User : BaseEntity
    {
        public int ID_User { get; set; }
        public string Name { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }

        public virtual ICollection<Employee> Employees{get;set;}
    }

 public class Employee : Entity
    {
        public int ID_Employee { get; set; }
        public string Name { get; set; }

        public int ID_User { get; set; }

        public virtual User User { get; set; }
    }

Everythihg works perfectly when I use the above mapping and I have enaugh data in both the tables.

Now, I want to make the column ID_User in Employee table as nullable

To implement this change I made folloeing change to my model:

public class Employee : Entity
    {
        public int ID_Employee { get; set; }
        public string Name { get; set; }

        public int? ID_User { get; set; }

        public virtual User User { get; set; }
    }

and in mapping file:

builder.HasOne(x=>x.User).WithMany(y=>y.Employees).HasForeignKey(z=>z.ID_User).IsRequired(false);

After running the dotnet ef migrations add empuser command it generated the following migration code:

  migrationBuilder.DropForeignKey(
            name: "FK_Employee_User_ID_User",
            table: "Employee");

        migrationBuilder.AlterColumn<int>(
            name: "ID_User",
            table: "Employee",
            nullable: true,
            oldClrType: typeof(int));

        migrationBuilder.AddForeignKey(
            name: "FK_Employee_User_ID_User",
            table: "Employee",
            column: "ID_User",
            principalTable: "User",
            principalColumn: "ID_User",
            onDelete: ReferentialAction.Restrict);

Now when I run dotnet ef database update it is giving me the following error:

    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 'CONSTRAINT FK_Employee_User_ID_User' at line 1

Please help.

Thanks

How to repeat:
create a 2 tables
User
Employee

User table contains the primary key and Employee contains a column ID_User which refer to the ID_User column of the User table.

in first case ID_User is mandatory in Employee table and will work good as expected.

But when you want to change it from mandatory to the nullable field and try to migrate it. 

The error will happen:

MySql.Data.MySqlClient.MySqlException (0x80004005): 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 'CONSTRAINT `FK_Employee_User_ID_User`' at line 1

Suggested fix:
Looks like the issue is with the SQL query generated against the migration. So this area can be looked into
[8 Feb 2018 9:47] Harshvardhan Sharma
I have also reported this issue on StackOverFlow:

https://stackoverflow.com/questions/48679013/net-core2-ef-mysql-having-issue-while-changin...
[9 Feb 2018 5:53] Harshvardhan Sharma
One more thing I would like share that when I use the Pomelo connecter for the same , it is working fine.

But I want to use the MySQL default connector as the project is very large and long-term, so cannot trust on any third party tool.
[13 Feb 2018 6:13] Chiranjeevi Battula
Hello Harshvardhan,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.