Disable cascade delete with EF?

Question

I have 2 entities linked by a one-to-many relationship as follows (I used code first):

public class Computer
{
    [Key]
    public int ComputerId { get; set; }

    [Required]
    public int ComputerIdInventory { get; set; }

    [DataType(DataType.Date)]
    public DateTime? AcquisitionDate { get; set; }

    [DataType(DataType.Date)]
    public DateTime? LastUpdate { get; set; }

    public string Comment { get; set; }

    //Foreign keys
    public int? ComputerModelId { get; set; }

    public int? EmployeeId { get; set; }

    //Navigation properties
    public virtual ICollection<Screen> Screens { get; set; }
}

public class Screen
{
    [Key]
    public int ScreenId { get; set; }

    [Required]
    public int ScreenIdInventory { get; set; }

    public string Comment { get; set; }

    //Foreign keys
    public int? ComputerId { get; set; }

    //Navigation properties
    public virtual Computer Computer { get; set; }
}

When I delete a computer that is linked to one or many screens, I have the following error:

[SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Screen_dbo.Computer_ComputerId". The conflict occurred in database "CPInventory", table "dbo.Screen", column 'ComputerId'.

I have read many posts, and I tried 2 things that seemed to have worked for others. I changed the "OnModelCreating" method and added :

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

And I tried this too:

modelBuilder.Entity<Computer>()
            .HasMany<Screen>(c => c.Screens)
            .WithOptional(s => s.Computer)
            .WillCascadeOnDelete(false);

But none of the solutions worked... Am I doing something wrong ? I also update the database but nothing has changed. Do I have to completely drop my database and recreate it in order for those changes to be taken into account?

Thank a lot !

Edit: Here is the delete code

public ActionResult DeleteConfirmed(int id)
    {
        Computer computer = db.Computers.Find(id);
        db.Computers.Remove(computer);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

The fluent configuration is ok, the problem is different. Can you share the delete code? — Ivan Stoev, Mar 31 '17 at 18:43

score 0 · Answer 1 · answered Mar 31 '17 at 15:45

0

The problem is you have a foreign key constraint in the database that says you can't delete a parent row (computer) if there are child rows (screens) in the database.

This is the reason you have cascade delete function in the first place, so deleting the computer deletes the screens as well.

Your only other option is to delete all the screens with the same ComputerID before deleting the Computer. (or why not just turn on cascade deletes and let the framework do it for you)

answered Mar 31 '17 at 15:45

David

1,743
1
18
25

Thank you David. Problem is that I want to keep the screens even if I delete the the computer, and cascade is not even turned on. I just cannot delete the computer. – Pookye Apr 01 '17 at 17:04
I would suggest using soft deletes (http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea) where you don't actually delete the Computer but instead a bit column Deleted to the table and set this to True for 'deleted' computers. – David Apr 03 '17 at 15:18
Alternatively, this answer looks like what you are wanting: http://stackoverflow.com/a/33914071/532616 – David Apr 03 '17 at 15:23

score 0 · Accepted Answer · answered Mar 31 '17 at 15:49

0

You can't delete a computer where a screen has a ComputerId that matches the computer's ComputerId.

So update the screen:

screen.ComputerId = null;

then delete your computer

db.Set<Computer>().Remove(computer);

then save changes

db.SaveChanges();

answered Mar 31 '17 at 15:49

Erik Philips

53,428
11
128
150

Thank you @Erik ! I'll try this as soon as I get back to work on Monday! :) – Pookye Apr 01 '17 at 17:08
Thank you ! Actually I used the answer suggest by @David: stackoverflow.com/a/33914071/532616 I think it's approximately the same as what you suggested !! Thank you ! – Pookye Apr 05 '17 at 06:47

Disable cascade delete with EF?

2 Answers2