Explanation about Cascade on Delete in Entity Framework

Question

I need a thorough explanation on cascade on delete because it is giving me unnecessary headache. I have a class News.cs and a class Comment.cs. News has a collection of comments and a comment must belong to a News, so I set up my class like below

public class News
{
    public int NewsId { get; set; }

    [Display(Name = "Title")]
    public string Title { get; set; }

    [Display(Name = "Details")]
    public string Details { get; set; }

    public DateTime DateCreated { get; set; }

    public int AppUserId { get; set; }

    [ForeignKey("AppUserId")]
    public virtual AppUser AppUser { get; set; }

    public ICollection<Comment> Comment { get; set; }

}

public class Comment
{
    public int CommentId { get; set; }

    public string CommentText { get; set; }

    public DateTime DateCreated { get; set; }

    public int AppUserId  { get; set; }

    public int NewsId { get; set; }

    [ForeignKey("AppUserId")]
    public virtual AppUser User { get; set; }

    [ForeignKey("NewsId")]
    public virtual News News { get; set; }

}

The behaviour I'm expecting is that if I delete a comment it shouldn't affect the parent news but if I delete a news I don't see any reason to retain the children comments so comments should be deleted. I ran an update database command in package manager console and I kept getting this error

Introducing FOREIGN KEY constraint 'FK_dbo.Comments_dbo.News_NewsId' on table 'Comments' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors. How do I solve this problem?

Since News links to Comment and Comment to News you get a 'loop' when deleting. You need to specify the ON DELETE NO ACTION option on Comments. So that when you delete News the Comments get deleted, but no action is done for the link back to News (blocking the loop). — Sugarel, Aug 03 '15 at 11:49
You can remove cascade deletion using Fluent API as noted here: http://stackoverflow.com/questions/17127351/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths — Felipe Garcia, Aug 03 '15 at 12:01
@JustFogMaxi No, the bidirectional relationship is only a class model artifact. In the database it's one and the same foreign key, having *one* cascaded delete action defined (from parent to child). This must be caused by other foreign keys with cascaded deletes, I suspect `Comment.AppUser`. — Gert Arnold, Aug 03 '15 at 12:01
@GertArnold should i update the question with other entity or what should i do? — ibnhamza, Aug 03 '15 at 12:27

score 1 · Answer 1 · answered Aug 03 '15 at 12:48

This error is caused when by deleting one entity, another entity will be deleted more that once.

In your scenario, if you delete AppUser with cascade delete turned on, this will attempt to delete the dependent entities, News and Comment. As Comment is also dependent on News, When News is deleted, Comment will be deleted as a dependent (again). As it may have already been deleted due to the dependency on AppUser, SQL cannot guarantee that the entity now exists, so SQL Server will prevent you from implementing this in the first place.

To resolve, the simplest thing is to turn off cascade delete on one or more of the dependents via the fluent api:

modelBuilder.Entity<AppUser>().HasMany(au => au.Comments)
.WithRequired(c => c.AppUser)
.HasForeignKey(c => c.AppUserID)
.WillCascadeOnDelete(false);

modelBuilder.Entity<AppUser>().HasMany(au => au.News)
.WithRequired(n => n.AppUser)
.HasForeignKey(n => n.AppUserID)
.WillCascadeOnDelete(false);

i tried this and i was able to run update database but on getting to the application i tried to delete a news item and i got this error The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Comments_dbo.News_NewsId". The conflict occurred in database "Intranet", table "dbo.Comments", column 'NewsId'. The statement has been terminated. — ibnhamza, Aug 03 '15 at 13:14

Explanation about Cascade on Delete in Entity Framework

1 Answers1