How to properly enable EF cascade delete with conventions

Question

I'm using EF 6.4.0 codefirst on a winforms app and Cascade delete is not working

below are my CLASSES

public class PLAYERS_M
{
    [Key,DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PM_ROWID { get; set; }

    public string PM_PLAYER_ID { get; set; }


    public string PM_FULLNAME { get; set; }

    public int? PM_COUNTRY { get; set; }

    public bool PM_IS_HOH  { get; set; }

    public string PM_QUOTE { get; set; }

    public byte[] PM_PHOTO  { get; set; }

    [ForeignKey("PM_COUNTRY")]
    public virtual COUNTRIES COUNTRIES { get; set; }
}

public class COUNTRIES
{
    [Key,DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CO_ROWID { get; set; }
    public string CO_CODE { get; set; }
    public string CO_NAME { get; set; }
}

I've added the following method to enable cascade delete on the dbcontext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
     modelBuilder.Conventions.Add<OneToManyCascadeDeleteConvention>();
     modelBuilder.Conventions.Add<ManyToManyCascadeDeleteConvention>();
     base.OnModelCreating(modelBuilder);
 }

however I get the following error

"The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.PLAYERS_M_dbo.COUNTRIES_PM_COUNTRY". The conflict occurred in database "MpContext", table "dbo.PLAYERS_M", column 'PM_COUNTRY'."

if you have observed the foreign key PM_COUNTRY is nullable int.

so I was expecting EF to delete the countries record and set PM_COUNTRY to null

am I doing something wrong?

The code you've shown here sets up cascading deletes. You're not going to get a cascading delete until you actually delete something. — Robert Harvey, Dec 19 '19 at 15:22
obviously it throws the above exception when I try to delete something.. — Asım Gündüz, Dec 19 '19 at 17:21

score 0 · Answer 1 · answered Dec 19 '19 at 20:09

I'm not sure why the convention isn't working, but you can configure it like this (after renaming the Entities):

modelBuilder.Entity<Player>()
    .HasOptional(c => c.Country)
    .WithMany()
    .HasForeignKey(p => p.CountryID)
    .WillCascadeOnDelete(true);

score 0 · Answer 2 · answered Dec 20 '19 at 10:57

OP wants to DELETE a COUNTRIES record and expects that all PLAYERS_M that have the corresponding PM_COUNTRY value will be set to null.

To achieve this, PLAYERS_M.PM_COUNTRY is correctly defined as nullable field (int?) but you still need to declare the relationship as optional in the model to enforce this behaviour.

OneToManyCascadeDeleteConvention does not help here, according to the documentation: Convention to enable cascade delete for any required relationships.

To get around this you can add the following Fluent Notation to your OnModelCreating method:

modelBuilder.Entity<PLAYERS_M>()
            .HasOptional(p => p.COUNTRIES) 
            .WithMany() 
            .HasForeignKey(p => p.PM_COUNTRY)
            .WillCascadeOnDelete(true); // this is where the magic is!

This behaviour is most likely by design, using Cascade Delete like this to null out optional references can very quickly create orphaned records, by using fluent notation you are forced to make the business decision for each relationship individually. Use this feature with care.

This works, however I wanted to enable cascade delete on a global context. — Asım Gündüz, Dec 20 '19 at 15:24
You could create your own convention to do this, but it's not recommended to do this globally. (Cascade on required relationships I have less issues with) the problem is that it will be too easy for a user to now delete the wrong record and wipe out a lot of data without realising. In my apps I would want to prevent countries from being deleted if they are in use, or force the user to manually reassign any linked records to another valid country before allowing them to delete the row. It's a style choice, but I've been down the other path and now I have learnt from those mistakes — Chris Schaller, Dec 20 '19 at 21:53

score 0 · Answer 3 · answered Dec 20 '19 at 15:23

I've solved my problem by adding the below property to the COUNTRIES class

[ForeignKey("PM_COUNTRY")]
public virtual List<PLAYERS_M> PLAYERS_M { get; set; } 

public class COUNTRIES
{
    [Key,DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CO_ROWID { get; set; }
    public string CO_CODE { get; set; }
    public string CO_NAME { get; set; }

    [ForeignKey("PM_COUNTRY")]
    public virtual List<PLAYERS_M> PLAYERS_M { get; set; }

}

How to properly enable EF cascade delete with conventions

3 Answers3