How to enable cascading delete

Question

I have two tables employee and degree. Between those two tables there is a one to many relation. (An employee can have multiple degrees) When I delete an employee I want to delete all degrees of this employee too. I found in other post that I can use cascade delete for this. Tried to add cascade delete, but I get an error when I try to delete an employee. I tried the following:

My two tables:

public partial class Degree
{
    public int DegreeId { get; set; }
    public string Course { get; set; }
    public string Level { get; set; }
    public string School { get; set; }
    public int Employee_BSN { get; set; }

    public virtual Employee Employee { get; set; }
}

public partial class Employee
{
    public Employee()
    {
        this.Degrees = new HashSet<Degree>();
    }

    public int BSN { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }    
    public virtual ICollection<Degree> Degrees { get; set; }
}

After reading a few posts I found this. But this didn't fix my problem.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Degree>()
            .HasRequired(t=>t.Employee)
            .WithMany(t=>t.Degrees)
            .HasForeignKey(d=>d.Employee_BSN)
            .WillCascadeOnDelete(true);
        base.OnModelCreating(modelBuilder);
    }

In my employee controller I have the following delete method

    // GET: Employees/Delete/5
    public ActionResult Delete(int? id)
    {
        if (id == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }
        Employee employee = db.Employees.Find(id);
        if (employee == null)
        {
            return HttpNotFound();
        }
        return View(employee);
    }

    // POST: Employees/Delete/5
    [HttpPost, ActionName("Delete")]
    [ValidateAntiForgeryToken]
    public ActionResult DeleteConfirmed(int id)
    {
        Employee employee = db.Employees.Find(id);
        db.Employees.Remove(employee);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

Error

The DELETE statement conflicted with the REFERENCE constraint "FK_Degree_Employee". The conflict occurred in database "\APP_DATA\DATABASE.MDF", table "dbo.Degree", column 'Employee_BSN'. The statement has been terminated.

You probably haven't configured cascaded delete in the database. EF assumes it's there. — Gert Arnold, Dec 21 '16 at 20:13

score 1 · Accepted Answer · edited May 23 '17 at 12:24

Gert is right, most likely you haven't configured cascading delete.

The problem is that when you delete an employee, Entity Framework is trying to set the employee foreign key for all associated degrees to null. And this is probably not allowed by your database design -- the employee foreign key cannot be null.

Therefore you have to enable cascading delete for the employee FK in your degree table.

Here is a good answer on how you can do that: https://stackoverflow.com/a/35886878/4227836

How to enable cascading delete

1 Answers1