CODEWITHSUNDEEP
entity-framework
r3plica
r3plica

Reputation: 13367

EF Many to Many cascading delete

I have an issue. I have some classes that look like this:

public class Question
{
    public int Id { get; set; }
    [Required] [MaxLength(255)] public string Text { get; set; }
    public int CriteriaId { get; set; }
    public int QuestionGroupId { get; set; }

    public QuestionGroup QuestionGroup { get; set; }
    public virtual IList<Answer> Answers { get; set; }
    public virtual Criteria Criteria { get; set; }
}

public class Answer
{
    public int Id { get; set; }
    [Required] [MaxLength(255)] public string Text { get; set; }
    public int QuestionId { get; set; }

    public Question Question { get; set; }
    public virtual IList<State> States { get; set; }
}

public class Criteria
{
    public int Id { get; set; }
    [Required] [MaxLength(100)] public string Name { get; set; }

    public virtual IList<State> States { get; set; }
    public IList<Question> Questions { get; set; }
}

public class State
{
    public int Id { get; set; }
    public int CriteriaId { get; set; }
    [Required] [MaxLength(100)] public string Name { get; set; }
    public int Score { get; set; }

    public virtual IList<Filter> Filters { get; set; }
    public Criteria Criteria { get; set; }
}

They have a many to many relationship, so I created this mapping:

modelBuilder.Entity<Answer>()
    .HasMany(m => m.States)
    .WithMany()
    .Map(m => {
        m.MapLeftKey("AnswerId");
        m.MapRightKey("StateId");
        m.ToTable("AnswerStates");
    });

When I tried to update my database I get an error about foreign keys. So I added this line (as a temporary fix):

modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

But I need the cascading delete. I can't seem to figure out why it won't let me have it. The error I get when trying to include cascading deletes is:

Introducing FOREIGN KEY constraint 'FK_dbo.AnswerStates_dbo.States_StateId' on table 'AnswerStates' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I hope I have provided enough information. Does anyone know what I can do to allow the cascading delete (I need to be able to delete a State or an Answer and it to remove the record from AnswerStates

Upvotes: 0

Views: 1387

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205619

This is pretty much the same as another question (Deleting only one entry from Many-to-Many relationship) I was answering today. Just your case is a bit more complicated.

The multiple cascade path in your case is from Criteria to AnswerStates. When deleting a Criteria record, the AnswerStates records can be deleted either by Criteria->States->AnswerStates or Criteria->Questions->Answers->AnswerStates relationships.

The solution is always one and the same - turn one of the relationships cascade delete off and handle deletion either manually or via trigger.

In this particular case, my suggestion is to turn Criteria->States cascade delete off:

modelBuilder.Entity<Criteria>()
    .HasMany(m => m.States)
    .WithRequired(d => d.Criteria)
    .HasForeignKey(d => d.CriteriaId)
    .WillCascadeOnDelete(false);

and use something like this before deleting a Criteria:

db.States.RemoveRange(db.States.Where(s => s.CriteriaId == criteriaId_to_be_removed));

Upvotes: 2

Related Questions