0

Although there are multiple solutions for such problems, but they are not allowing multiple cascade path but just disabling on delete cascade option from one reference.

But I want to allow on delete cascade from both references

For example in following case, Performance table information should be deleted whether Student is deleted or Course is deleted

    public class Course
    {
       public int Id { get; set; }
       public string Name { get; set; }
       public string Type { get; set; }
       public string Department { get; set; }
       public int TeacherId { get; set; }
       public virtual Teacher Teacher { get; set; }
       public virtual ICollection<Performance> Performances { get; set; }
    }

    public class Student
    {
       public int Id { get; set; }
       public string Name { get; set; }
       public int StudentDetailId { get; set; }
       public virtual StudentDetail StudentDetailId { get; set; }
       public virtual ICollection<Performance> Performances { get; set; }
    }  

    public class Performance
    {
       public int Id { get; set; }
       public string Grade { get; set; }
       public int Attendance { get; set; }
       public int Activity { get; set; }
       [Required]
       public int StudentId { get; set; }
       [ForeignKey("StudentId")]
       public virtual Student Student { get; set; }
       [Required]
       public int CourseId { get; set; }
       [ForeignKey("CourseId")]
       public virtual Course Course { get; set; }
    }

I also tried to modify database manually by adding ON DELETE CASCADE ON UPDATE CASCADE in both foreign keys, but SQL server does not allow me to do this as well. With above code I get following message:

Introducing FOREIGN KEY constraint 'FK_dbo.Performance_dbo.Course_CourseId' on table 'Performance' 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.

Problem goes away if I keep one cascade delete and other reference optional: 

public class Performance
{
    public int Id { get; set; }
    public string Grade { get; set; }
    public int Attendance { get; set; }
    public int Activity { get; set; }
    [Required]
    public int StudentId { get; set; }
    [ForeignKey("StudentId")]
    public virtual Student Student { get; set; }
    // [Required]
    public int? CourseId { get; set; }
    [ForeignKey("CourseId")]
    public virtual Course Course { get; set; }
 }

Please suggest a way of cascade delete from both references.

Muffin
  • 781
  • 4
  • 10
  • 26

1 Answers1

1

I don't think that you can actually do that automatically with Entity Framework or SQL Server for that matter (see Alan's answer to this question). This has been an ongoing problem with SQL Server for years now, and apparently is still not fixed in SQL Server 2014.

You have, from what I can see, 2 options:

1) Create a trigger that will delete the Performance information automatically when a Student or a Course is deleted.

2) Do a manual delete with EF, where you loop through the Performance records when you are deleting a Student or a Course and delete them. (Not the best option, since EF runs on the application side of things)

Community
  • 1
  • 1
Corey Adler
  • 15,897
  • 18
  • 66
  • 80
  • Hi Thank you for your answer. Is it possible to do the trigger in SQL, Because in some cases I don't really delete Student or Course but there parent objects or grandparent objects that casacades students or courses, I think you understand the problem I mean :/ – Muffin Oct 27 '14 at 14:33
  • I'm pretty sure that the trigger would be run anytime a record would be deleted, even if it is was done by a cascade. – Corey Adler Oct 27 '14 at 14:35
  • http://www.mssqltips.com/sqlservertip/2733/solving-the-sql-server-multiple-cascade-path-issue-with-a-trigger/ – Corey Adler Oct 27 '14 at 14:42