Add Foreign Key Column In EF Core

Question

I have an existing table Projects that I would like to add a UserId column to, where UserId is a foreign key. Projects right now has a list of names, but I would like for each user to manage her own projects. I am OK with having "orphans" initially as the list is small enough that I can manually clean those up.

I have updated my model to include UserId and a navigational property User (Probably not relevant, but Entity here is base class with Id and DateModified)

public class Project : Entity
{
    public string Name { get; set; }
    public Guid? UserId { get; set; } //tried this as nullable and non nullable
    public User User { get; set; }
}

And my related mapping file is

public class ProjectMap : IEntityTypeConfiguration<Project>
{
    public void Configure(EntityTypeBuilder<Project> builder)
    {
        builder.Property(x => x.Name).IsRequired();
        builder.Property(x => x.UserId).IsRequired();

        builder.HasOne(x => x.User)
            .WithMany(x => x.Projects)
            .HasForeignKey(x => x.UserId)
            .OnDelete(DeleteBehavior.SetNull);  //I have tried numerous combinations of things here....
    }
}

I have also added a navigational property to the User entity for projects, but have made no changes to the mapping class.

public class User : Entity
{
    public string EmailAddress { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<Task> Tasks { get; set; }
    public List<Project> Projects { get; set; }
}

The migration that gets generated from this:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<Guid>(
            name: "UserId",
            table: "Projects",
            nullable: false,
            defaultValue: new Guid("00000000-0000-0000-0000-000000000000"));

        migrationBuilder.CreateIndex(
            name: "IX_Projects_UserId",
            table: "Projects",
            column: "UserId");

        migrationBuilder.AddForeignKey(
            name: "FK_Projects_Users_UserId",
            table: "Projects",
            column: "UserId",
            principalTable: "Users",
            principalColumn: "Id",
            onDelete: ReferentialAction.SetNull);
    }

Which translates to this SQL when running update-database

ALTER TABLE [Projects] ADD CONSTRAINT [FK_Projects_Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id]) ON DELETE SET NULL;

And fails with this error

Cannot create the foreign key "FK_Projects_Users_UserId" with the SET NULL referential action, because one or more referencing columns are not nullable.

What am I doing wrong?

Answer 1

The line below is causing the problem, since you want a nullable FK on Project

builder.Property(x => x.UserId).IsRequired();

Also, your generated migration has the hint already on it:

 nullable: false,

Just delete that line on your Configure method and it should work. You may also need to remove the migration by running Remove-Migration and then running the add-migration again. This time you should have this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<string>(
        name: "Name",
        table: "Projects",
        nullable: false,
        oldClrType: typeof(string),
        oldNullable: true);

    migrationBuilder.AddColumn<Guid>(
        name: "UserId",
        table: "Projects",
        nullable: true);

    migrationBuilder.CreateIndex(
        name: "IX_Projects_UserId",
        table: "Projects",
        column: "UserId");

    migrationBuilder.AddForeignKey(
        name: "FK_Projects_Users_UserId",
        table: "Projects",
        column: "UserId",
        principalTable: "Users",
        principalColumn: "Id",
        onDelete: ReferentialAction.SetNull);
}

If you want, you can be more specific by explicit setting the IsRequired while configuring your relationship:

private static void ConfigureProject(EntityTypeBuilder<Project> b)
{
    b.Property(x => x.Name).IsRequired();

    b.HasOne(x => x.User)
        .WithMany(x => x.Projects)
        .HasForeignKey(x => x.UserId)
        .IsRequired(false)
        .OnDelete(DeleteBehavior.SetNull);
}

I like this even though the property is already nullable and EF will use its conventions to create it correctly, it's still nice for someone reading the configuration and knowing about it without having to go to the actual Project class.