Entity Framework 6 Code First - tree structure comments

Question

I am trying to implement heterogeneous communication in my data model ( Entity Framework 6, Code-First approach ).

I have an existing class structure, let's call them Tree , Branch and Leaf . A Tree can have many Branch objects, and Branch can contain many Leaf objects. The relationships between the three levels have the cascade-delete behavior (delete a branch, and also delete leaves, etc.).

Now I'm trying to allow users to add a comment-like object at each of these levels. I had several problems with data modeling , since I want each of the three types of entities to have many comments and each comment belong to one and only one record. I would also like all comments to be in the same table. I tried two different approaches:

---

Alt 1

Introduce inheritance so that Comment (abstract) can be TreeComment , BranchComment or LeafComment , following the approach to the hierarchy table (TPH) (as seen, for example, here ) of having an abstract class ( Comment ) for comments, and then output it to TreeComment , BranchComment etc. This is achieved by encoding such models as follows:

 public abstract class Comment { // ID [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public Guid ID { get; set; } } public class TreeComment: Comment { // Foreign Keys public Guid TreeID { get; set; } // Navigation Properties public virtual Tree Tree { get; set; } } (... BranchComment and LeafComment ...) (... add virtual ICollection<TreeComment> to Tree, virtual ICollection<BranchComment> to Branch, etc.) 

... which can be expressed using this diagram:

The problem with this approach is that the relationship between the comment table and the other 3 does not contain ON DELETE CASCADE or ON DELETE SET NULL . If I try to change this to multiple tables, I get:

Representing the FOREIGN KEY constraint 'FK_Comment_Branch_BranchID' on the Comment table can cause loops or multiple cascading paths. Indicate ON DELETE NO ACTION or DO NOT UPDATE NO ACTION, or change another FOREIGN KEY of restriction.

I understand that this is because SQL Server "does not know" that at any time it is supposed to use only one of the FKs in the comment table.

---

Alt 2

Summarize the Tree / Branch / Leaf trio in CommentableEntity using the Table for Type (TPT) approach and join the Comment table with this abstract. This can be achieved by implementing inheritance in model classes (as before) and adding the annotations [Table("Tree")] , [Table("Branch")] and [Table("Leaf")] to each of the subclasses so that make sure that we get a table for each (and not one table, as in TPH). The model is as follows:

This approach has two problems:

• Deleting a specific object (for example, a branch) will not delete the base record in the abstract table , leaving behind “garbage” (abstract entities and their comments).

• FK relationships between abstract and concrete classes do not have cascade delete . Therefore, I cannot delete the underlying object. If I try to add it, I get another complaint about how introducing such a rule will cause cycles of several cascading paths.

---

I also tried to use DB triggers ( CREATE TRIGGER ... INSTEAD OF DELETE... ) for both approaches, but they seem to be big, no, because EF cannot track their changes.

This is disappointing, and I'm sure this (comments on the tree structure) is a very typical scenario in web development; but I cannot find a way to resolve this. I am looking for all the tips I can get about how to effectively model this relationship (EF 6 Code First) without giving too much weight to the Business Logic level.

EDIT:

I believe that this particular user @Deepak Sharma mentioned in his comment: TPH inheritance in node classes. If so, this also does not work for the same reason: loops of multiple cascading paths.

Answer 1

So, here is how I solve the problem now:

I chose the second alternative - to generalize the Tree / Branch / Leaf trio (let’s call these "nodes" for simplicity) into CommentableEntity (base class) using TPT - as seen above. I get one table for each of the three node classes + one base class that contains a relation to the Comment table.

Then in the InitializeDatabase(MuDbContext context) I added one Stored Procedure and a Trigger . > for each of the three tables in the database using the context.Database.ExecuteSqlCommand() method.

1) The stored procedure should be mapped to EF as follows:

 this.MapToStoredProcedures(s => s.Delete(d => d.HasName("TriggerName").Parameter(b => b.ID, "parameter_name"))); 

... for each of the three models and basically replaces the default deletion. In my case, I wrote it so that it first deletes the actual node in its table ( Tree / Branch / Leaf ), and then the corresponding base object ( CommentableEntity ).

2) The trigger is launched after the node is deleted and ensures that the corresponding base object is also deleted.

If you're wondering why I have such redundancy (Trigger and Stored Proc., Which do pretty much the same thing), because whenever a node is deleted (say, a tree), EF calls it saved Proc. to remove it. Then the nested nodes (tree branches) are deleted through DB cascade-delete , which does not delete the base objects, and not through Stored Proc. So the trigger. On the other hand, if I only had a trigger (without Proc. Saved), EF will run out after deletion, because it will not be able to track its changes.

I could, of course, just change each of the Stored Proc. for each of the tables so that they also cascade-delete all nested objects and cascade-delete parameter. But the current solution seems to work and is good enough for me.

I will check this and delete this answer if I find out that this is actually not working. If you see flaws in this approach (and know how to avoid them), leave a comment.

Answer 2

Perhaps the answer is to increase Alt 1 by declaring some rules in the OnModelCreating method. It is also assumed that the Tree, Branch, and Leaf classes have a collection of comments against them.

Inside your DbContext you can do the following ...

 public class YourDbContext : DbContext { ... your DbSet properties protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Tree>() .HasMany<TreeComment>(o => o.Comments) .WithRequired(com => com.Tree) .HasForeignKey(com => ds.TreeID) .WillCascadeOnDelete(false); } }