CODEWITHSUNDEEP
c#entity-framework
Halter
Halter

Reputation: 48

How do I configure Entity Framework cascade delete to work properly with a one to many relationship?

After researching cascade deletes and browsing issues here, I'm under the impression that the following scenario will work

Entity with many:

public partial class master
{
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public master()
        {
            analysis = new HashSet<analysis>();
        }

        [Key]
        public int id { get; set; }

        [StringLength(50)]
        public string description { get; set; }

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

Entity with one:

public partial class analysis
{
        [Key]
        public int id { get; set; }

        [StringLength(50)]
        public string description { get; set; }

        public int? master_id { get; set; }

        public virtual master master { get; set; }
}

The foreign key on analysis entity is nullable, so that cascade deletion will automagically set the FK to null when it tries to delete the master record.

That configuration is in my Context:

public class Context : DbContext
    {
        public Context() : base("Context")
        {
            this.Configuration.LazyLoadingEnabled = true;
            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<Context>());
        }

        public virtual DbSet<master> master { get; set; }
        public virtual DbSet<analysis> analysis { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<master>()
                .HasMany(e => e.analysis)
                .WithOptional(e => e.master)
                .HasForeignKey(e => e.master_id)
                .WillCascadeOnDelete(true);
        }
    }

Then when I attempt to delete a master record by passing the id into the following (generic repo snippet, this works fine when deleting a master record with no analyses):

public void Delete(int id)
{
        var entity = FindByKey(id);
        _dbSet.Remove(entity);
        _context.SaveChanges();
}

I get an error

Cannot delete or update a parent row: a foreign key constraint fails

on this record. What am I missing here?

Upvotes: 0

Views: 1162

Answers (1)

Steve Py
Steve Py

Reputation: 34653

The use of CascaseOnDelete should mirror cascade rules on the database. With CodeFirst when you set a relationship as Required, EF maps this both in the mapping and the schema as a delete cascade. With Optional relationships this isn't the case, it assumes deleting a parent will leave the child orphaned since the child accepts an existence without a parent due to it's null-able FK. However, without knowledge of the child records it cannot clear the ID out of the FK or delete the data so it relies on whatever the DB is set up to do.

You can still use .WillCascadeOnDelete(), though for it to work, the context needs to know about the children. For instance, on a new context:

var master = _db.Masters.Find(id);
_db.Masters.Remove(master);

This typically fails with a FK constraint error. (SQL Server) The error message differs from yours so I suspect like Tetsuya that is a different provider?

To resolve this you can use:

var master = _db.Masters.Include(x=>x.analysis).Find(id);
_db.Masters.Remove(master);

though that can be tedious for objects with larger graphs to include all children.

As a general rule if you want to use cascade deletes, you need to ensure that the database schema is set up with a cascade on delete behaviour. With a null-able FK and a cascade delete rule the original code should behave as expected. Alternatively, Setting a cascade rule to "Set to Null" will leave the child records orphaned with a null FK. (no error from EF)

Upvotes: 1

Related Questions