Entity Framework 6 Code First - Comments on a Tree Structure

Question

I'm attempting to implement heterogeneous association in my Data Model (Entity Framework 6, Code-First approach).

I have an existing structure of classes, let us call them Tree, Branch and Leaf. A Tree may have many Branch objects, and a Branch may hold many Leaf objects. The relationships between the three levels have a cascade-delete behavior (delete a branch and you also delete the leaves, etc.).

Now I'm trying to let users add a comment-like object on each of those levels. I had a few problems related to data-modelling, as I want each of the 3 entity types to be able to have many comments and each comment to belong to one and only one entry. I'd also like for all comments to be in the same table. I've tried two different approaches:

---

Alt. 1

Implement inheritance so that the Comment (abstract) can be a TreeComment, BranchComment or LeafComment, following the Table per Hierarchy (TPH) approach (as seen, for example, here) of having an abstract class (Comment) for comments and then derive it to TreeComment, BranchComment, etc. That is achieved by coding the models like this:

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 with this diagram:

The problem with this approach is that the relationship between the Comment table and the other 3 doesn't have ON DELETE CASCADE or ON DELETE SET NULL set. If I try to change that to more than one table, I get a:

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

I understand that this is because SQL Server "doesn't know" that only one of the FK's in the Comment table is supposed to be used at any time.

---

Alt. 2

Generalize the Tree/Branch/Leaf trio into a CommentableEntity using the Table per Type (TPT) approach and connect the Comment table to that abstract one. This can be achieved by implementing inheritance in the model classes (just like I did before) and adding the annotations [Table("Tree")], [Table("Branch")] and [Table("Leaf")] to each of the subclasses to make sure we get a table for each (and not a single table like in TPH). The Model, then looks like this:

This approach has two problems:

1. Deleting a concrete object (e.g. a branch) will not delete the base entry in the abstract table, leaving "garbage" (abstract entities and their comments) behind.

2. The FK relationship between the abstract and concrete classes lacks a cascade delete. So I can't really delete the base object. If I try to add one I get another complaint on how introducing such rule would cause cycles of multiple cascade paths.

---

I've also tried using DB triggers (CREATE TRIGGER ... INSTEAD OF DELETE...) on both approaches but they seem to be a big no-no as EF can't track the changes done by them.

This is frustrating and I'm sure this (comments on a tree structure) is a very typical scenario in Web development; but I can't seem to find a way to allow it. I'm looking for all advice I can get on how to effectively model these relationships (EF 6 Code First) without placing too much weight on the Business Logic layer.

EDIT:

I believe this is what user @Deepak Sharma mentioned in his comment: TPH inheritance in the node classes. If so, this also doesn't work for the same reason: cycles of multiple cascade paths.

Answer 1

Ok, so here's how I'm currently solving the problem:

I chose the second alternative - generalize the Tree/Branch/Leaf trio (let's call these "nodes" for simplification) into a CommentableEntity (the base class) using the TPT approach - as seen above. I end up with one table for each of the three node classes + one base class that holds the relationship to a Comment table.

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

1) The Stored Procedure has to be mapped in EF like this:

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

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

2) The Trigger fires after a node is deleted and makes sure that the corresponding base object is also deleted.

In case you're wondering why do I have such redundancy (a Trigger and a Stored Proc. that do almost the same thing) it's because whenever a node is deleted (say, a tree), EF calls its Stored Proc. in order to delete it. Then, the nested nodes (the tree's branches) are deleted via the DB's cascade-delete, which doesn't delete the base objects, and not via the Stored Proc.. Thus, the Trigger. On the other hand, if I only had the trigger (no Stored Proc.), EF would freak out after the deletion because it wouldn't 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 delete all of the nested objects as well and remove the cascade-delete setting. But the current solution seems to be working and good enough for me.

I will test this out and delete this answer if I find out that this doesn't actually work. If you see any disadvantages in this approach (and know how to avoid them) please leave a comment.

Answer 2

Possibly the answer is to augment Alt 1 by declaring some rules within the OnModelCreating Method. Also this assumes that the the Tree, Branch and Leaf classes have a Comments collection against them.

Within your DbContext you could 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);
     }
}