.Net Core2 EF MySQL having issue while changing foreign key column to nullable

Question

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:

1. User
2. 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; }
    }

Everything works perfectly when I use the above mapping and I have enough 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 following 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

Answer 1

Try putting the SQL statements directly into the MySQL Workbench.

1. Type "dotnet ef migrations script" in your commandprompt.
2. Copy the generated SQL script.
3. Paste it into your Workbench.
4. Check where the errors occur.

When I got similar errors using EF core 2 with MySQL this helped me understand the problem better and helped solve the problem. (for me it was a typing error). You can at least use this method to determine if it is an error in the migrations or in your SQL statements.

I know this is not a concrete solution, but I hope this will help you understand your problem and solve it :)