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

[SharmaHarsh7]

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; }
    }

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

[mguinness]

Can you generate the SQL? It appears that it's a SQL syntax error so that should provide some clues.

[SharmaHarsh7]

It is not generating any SQL, I have already tried it. It gives same error when I run 'dotnet ef migrations script' command

…

On Thu, Feb 8, 2018, 23:16 mguinness ***@***.***> wrote: Can you generate the SQL <https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/#generating-a-sql-script>? It appears that it's a SQL syntax error so that should provide some clues. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#483 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AIcy3L4ZQKlICMAtRFQJk8RuFTFBNWbfks5tSzLygaJpZM4R98p8> .

[caleblloyd]

Turn "Microsoft" logging to "Information" then run your migrations and it will log SQL. Example (change Microsoft: error to Microsoft: information)

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/master/test/EFCore.MySql.FunctionalTests/appsettings.json

[SharmaHarsh7]

@caleblloyd, Thanks man I got this fix.

Actually I was using MySQL official connector for this which was causing the issue.

Later I tried same scenario with Pomelo.EntityFrameworkCore.MySql and it worked exactly same as expected.

!!! Pomelo Rocks !!!