EF Core 3.1 DeleteBehavior.SetNull with Optional Relationship fires Constraint Exception

Question

I'm using EF Core 3.1 DB First, trying to set DeleteBehavior.SetNull for an optional relationship, but I'm still getting an exception on SaveChanges.

The entities are: Patient which has a nullable realtionship to Address in a field called Address. The entities were generated by scaffold-DbContext cmd like this (I removed properties irrelevant for the question):

 public partial class Address
    {
        public Address()
        {
            Patient = new HashSet<Patient>();
        }

        public int Id { get; set; }
        ...

        public virtual ICollection<Patient> Patient { get; set; }
    }

And Patient class:

public partial class Patient
    {
        public int Code { get; set; }          
        public int? Address { get; set; }
        ...

        public virtual Address AddressNavigation { get; set; }
    }

In OnModelCreating function I have the following piece of code:

modelBuilder.Entity<Patient>(entity =>
        {
            ...

        entity.HasOne(d => d.AddressNavigation)
            .WithMany(p => p.Patient)
            .HasForeignKey(d => d.Address)
            .HasConstraintName("FK__Patient__Address__2F10007B")
            .OnDelete(DeleteBehavior.SetNull);
    });

When I try to remove an Address, with the following lines of code:

using (ClinicContext ctx = new ClinicContext())
{
    var address = ctx.Address.Find(addressId);
    ctx.Remove(address);
    ctx.SaveChanges();
}

I get the following exception:

The DELETE statement conflicted with the REFERENCE constraint "FK__Patient__Address__2F10007B". The conflict occurred in database "C:\USERS\USER\SOURCE\REPOS\CLINIC\DB\CLINIC.MDF", table "dbo.Patient", column 'Address'. The statement has been terminated

What am I missing here?

Answer 1

As @Jerry said, since you are using DB First, Add DeleteBehavior.SetNull in the generated DbContext will have no effect to your database after you do the scaffold-DbContext commond.

You'd better recreate your table with the below statement:

CREATE TABLE [dbo].[Patient] (
    [Code]    INT         IDENTITY (1, 1) NOT NULL,
    [Id]      VARCHAR (9) NOT NULL,
    [Address] INT         NULL,
    PRIMARY KEY CLUSTERED ([Code] ASC),
    FOREIGN KEY ([Address]) REFERENCES [dbo].[Address] ([Id]) ON DELETE SET NULL
);