How to apply cascade delete on entities which have several foreign keys with Entity Framework 6?

Question

I have the two entities below:

[Table("TimeZone")]
public class TimeZone
{
    [Required]
    [Column("Name"), Index("IX_Name", IsUnique = true)]
    [StringLength(50)]
    public string Name { get; set; }

    [Column("Description")]
    [StringLength(100)]
    public string Description { get; set; }

    [Column("IANAID"), Index("IX_IANAID", IsUnique = true)]
    [StringLength(50)]
    public string IANAId { get; set; }

    public ICollection<ApplicationUser> Users { get; set; }

    public TimeZone()
    {
        Users = new HashSet<ApplicationUser>();
    }
}

[Table("User")]
public class ApplicationUser : IdentityUser<string, IdentityUserLogin, ApplicationUserRole, IdentityUserClaim>
{ 
    // [...]

    [Column("TimeZoneID"), ForeignKey("TimeZone")]
    public string TimeZoneId { get; set; }
    [Column("RegionID"), ForeignKey("Region")]
    public string RegionId { get; set; }
    [Column("ManagerID"), ForeignKey("Manager")]
    public string ManagerId { get; set; }

    // One-to-One
    public virtual TimeZone TimeZone { get; set; }

    // One-to-One
    public virtual Region Region { get; set; }

    // One-to-Many
    public virtual ApplicationUser Manager { get; set; }

    // One-to-many
    public virtual ICollection<Appointment> Appointments { get; set; }

    // Many-to-many
    public virtual ICollection<OnCallGroup> OnCallGroups { get; set; }

    // One-to-many
    public virtual ICollection<ApplicationUser> Subordinates { get; set; }
}

When I delete a record from TimeZone table which is used in the User table, the related records should be deleted according to the Cascadde Delete convention.

However since there are other foreign keys, I get the following exception with Entity Framework:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.User_dbo.TimeZone_TimeZoneID". The conflict occurred in database "APPLICATIONDB_753c3d2ad2634cbf8cb62b098cdc6043", table "dbo.User", column 'TimeZoneID'. The statement has been terminated.

Is it normal that even though there is the cascade delete enabled by default on EF6 Code First, deleting a record from TimeZone does not delete all the related users?

Note: In my ApplicationDbContext I overrided the OnModelCreating method:

    protected override void OnModelCreating(DbModelBuilder dbModelBuilder)
    {
        base.OnModelCreating(dbModelBuilder);

        dbModelBuilder.Conventions.Add<OneToManyCascadeDeleteConvention>();
        dbModelBuilder.Conventions.Add<ManyToManyCascadeDeleteConvention>();
        dbModelBuilder.Conventions.Remove<PluralizingTableNameConvention>();


        // [...]
    }

https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server/6260736#answer-25228623 — Ankush Jain, Jan 08 '18 at 05:18
@AnkushJain it's not supposed to work regardless of the database... The database is created through the ApplicationDbContext in which I force / add the CascadeDeleteConvention. — Natalie Perret, Jan 08 '18 at 05:22

Ivan Stoev · Accepted Answer · 2018-01-08T09:59:42.387

The FK property (TimeZoneId) CLR type is string, which allows null, and no [Required] attribute has been applied to the FK or navigation property, thus making the relationship optional, i.e. ApplicationUser may exist without TimeZone. And by default convention EF does not turn cascade delete on optional relationships.

To turn the cascade delete on, you need either to make the relationship required by applying the [Required] attribute on either TimeZoneId or TimeZone property, or use fluent API in case you need to keep it optional by adding the following inside OnModelCreating override:

modelBuilder.Entity<TimeZone>()
    .HasMany(e => e.Users)
    .WithOptional(e => e.TimeZone)
    .HasForeignKey(e => e.TimeZoneId)
    .WillCascadeOnDelete(); // <--

The same applies to other relationships using string FK properties.

Update: Actually the second option is not really an option. Even if it sets cascade delete on, EF treats cascade delete on optional relationships differently - when principal entity is deleted, it disassociates the related records by setting FKs to null rather than deleting them. So you really have to make the relationship required by using the aforementioned attribute or changing the fluent API WithOptional to WithRequired.

How to apply cascade delete on entities which have several foreign keys with Entity Framework 6?

1 Answers1