Managing Hierarchical, Parent/Child Data With Database/Code First Entity Framework And Recursion

You may be asking yourself, "hierarchical vs. Parent/Child...What is the difference?". Well in my opinion, hierarchical data is the same as data you may see as a treeview format that does not follow a predefined set of levels. I would argue to say that a parent/child relationship can also resemble the same data structure as Hierarchical, where child records can also be parents and have additional children as to mimic human procreation. Simply put, I think the difference is only a formality. Regardless, this post will help you understand how to handle data that has "x" number of levels.

So if you have been searching the web on how to model hierarchal data, you probably came across links like the following...

http://stackoverflow.com/questions/7935100/define-relationships-for-hierarchical-data-in-entity-framework-code-first

http://goldytech.wordpress.com/2011/06/27/how-to-work-with-hierarchal-data-in-entity-framework/

 Table of Contents Table

In this post, I am going to focus on a "table of contents". We can both agree that there can be any combination of levels, for items in a table of contents, and each "table of contents" is usually associated to one and only one book. Let's look at how that might look as a database table.

 CREATE TABLE [dbo].[TableOfContent] (
    [ContentId]       INT           IDENTITY (1, 1) NOT NULL,
    [ParentContentId] INT           NULL,
    [Title]           VARCHAR (100) NOT NULL,
    [ContentOrder]    INT           NULL,
    CONSTRAINT [PK_TableOfContent] PRIMARY KEY CLUSTERED ([ContentId] ASC),
    CONSTRAINT [FK_TableOfContent_TableOfContent] FOREIGN KEY ([ParentContentId]) REFERENCES [dbo].[TableOfContent] ([ContentId])
);  

The script above shows that content for a table of contents has

  1. A primary key
  2. Nullable parent content id, because not all content has to have parent content, like the root of the TOC.
  3. Title of the content
  4. Order of the content (this will set the order for content that has sibling records)
  5. Foreign key which maps to the primary key, so a child record has to an existing record within the table.

One thing that is missing from the TableOfContent table might be another foreign key as a bookId, pointing to a book table, so we know which table of contents goes with a particular book, however there is no reason for the TableOfContent table to have a mapping for the BookId, since it would be easier to map the root contentId of the TOC within a Book table instead. Now we are associating the TOC root's content ID to a book, instead of needing to associate each content record to a bookId. Just to make sure you follow, we can do this because there is a parent/child relationship with the content records, that map up to one content record that is essentially the root of the content.

Entity Framework - Database Code First

Entity Framework has some power tools that are remarkably still in beta(beta 4). It is a visual studio extension and after it is installed, you can create code-first mappings and entities from an existing database. To me, this is the way to go, instead of using the table graphs or model-first with EF. Once the extension is installed in visual studio, you can see how to use it here.

Once you have reversed engineered the TableOfContent database table, EF does it's best job to map the relationship for the self referencing foreign key ParentContentId to the primary key ContentId.

You get the following based on mapping the TableOfContent table scripted above...

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;

namespace PWC.Viewer.Data.Models.Mapping
{
    public class TableOfContentMap : EntityTypeConfiguration<TableOfContent>
    {
        public TableOfContentMap()
        {
            // Primary Key
            this.HasKey(t => t.ContentId);

            // Properties
            this.Property(t => t.Title)
                .IsRequired()
                .HasMaxLength(100);

            // Table & Column Mappings
            this.ToTable("TableOfContent");
            this.Property(t => t.ContentId).HasColumnName("ContentId");
            this.Property(t => t.ParentContentId).HasColumnName("ParentContentId");
            this.Property(t => t.Title).HasColumnName("Title");
            this.Property(t => t.ContentOrder).HasColumnName("ContentOrder");

            // Relationships
            this.HasOptional(t => t.TableOfContent2)
                .WithMany(t => t.TableOfContent1)
                .HasForeignKey(d => d.ParentContentId);

        }
    }
}

The TableofContent entity will look like this...

using System;
using System.Collections.Generic;

namespace PWC.Viewer.Data.Models
{
    public partial class TableOfContent
    {
        public TableOfContent()
        {
            this.Engagements = new List<Engagement>();
            this.TableOfContent1 = new List<TableOfContent>();
        }

        public int ContentId { get; set; }
        public Nullable<int> ParentContentId { get; set; }
        public string Title { get; set; }
        public Nullable<int> ContentOrder { get; set; }
        public virtual ICollection<TableOfContent> TableOfContent1 { get; set; } //change to TableOfContents
        public virtual TableOfContent TableOfContent2 { get; set; } //change to ParentContent
    }
}

 This is where EF does it's best to understand the relationship, by building a TableOfContent1 collection & TableOfContent2 properties. TableOfContent1 can be changed to "TableOfContents" and TableOfContent2 can be changed to "ParentContent". Now that the properties have changed in the entities, the code-first, mapping file associating relationships of the propertiese also need to be updated, so change the TableOfContentMap file's relationship from

// Relationships
this.HasOptional(t => t.TableOfContent2)
.WithMany(t => t.TableOfContent1)
.HasForeignKey(d => d.ParentContentId); 

 to

this.HasOptional(t => t.ParentContent)
                .WithMany(t => t.TableOfContents)
                .HasForeignKey(d => d.ParentContentId);

Recursion

The EF call to use for loading a book and it's table of contents is as follows...

var book = entityContext.Books
                            .Where(b=>b.BookId==1)
                            .Include("TableOfContent")
                            .FirstOrDefault();

This will us the EF object entityContext that inherits from dbContext(, to get the book that has a BookId of 1. EF will handle the relationship based on the mapping, and pull the data in the Book object's TableOfContent  property, but in order to read the data based on it's hierarchy, recursion has to take place. If you are not familiar with recursion in programming, it is simply calling a method or function from within the same method or function. In order to read the data that is returned from the code above, the following recursive method is required.

private void DisplayTOCData(TableOfContent TOC)
        {
            if (TOC.TableOfContents.Count > 0)
            {
                foreach (var child in TOC.TableOfContents.OrderByDescending(o => o.ContentOrder))
                {
                    DisplayTOCData(child);
                    Console.WriteLine(". {0} is the root of {1}",TOC.Title,child.Title);
                }
            }
        }

This method will read the TableOfContent parameter and check to see if it has any child records. It will then loop through the child records by first ordering them by their ContentOrder. The method then recursively calls itself by passing in the child record, to see if it also has any child records.

It has been awhile since I have had to do this and at first I was not sure the direction, however after careful thinking, I was able to make good decisions that took very little code to implement.

 

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Posted by: BayerWhite
Posted on: 1/30/2014 at 2:23 PM
Tags: ,
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed