.Net Core2 EF MySQL with release when changing foreign key column to NULL

Question

I am working on an application in which I use .Net Core 2, EF Core, and MySQL as a database server using the Code First approach.

I have 2 tables:

• User
• Employee

The user table is the main table that contains information about the user, and the Employee table is the child table with the column identifier 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 fine when I use the above mapping, and I have enough data in both tables.

Now I want to make the ID_User column in the Employee table as with a null value

To implement this change, I made the following changes 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 the 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 it generated the following transition 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 gives me the following error:

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use near 'CONSTRAINT FK_Employee_User_ID_User ' on line 1

Please, help.

thanks

Answer 1

Try putting SQL queries directly in MySQL Workbench.

• Type "dotnet ef migrations script" at the command prompt.
• Copy the generated SQL script.
• Insert it into your Workbench.
• Check where errors occur.

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

I know this is not a specific solution, but I hope this helps you understand your problem and solve it :)