Entity Framework Recipe: Hierarchical Data Management

4.28 (18 votes)

This article explores how to use Entity Framework 6 to manage the retrieval and display of hierarchical data such as you might find in a menu system. There are a number of existing items on the Internet that cover the same topic, but most of them seem unnecessarily complex and don't include any information about displaying the data once it has been retrieved.

Hierarchical data is normally stored in a "self-referencing" table, where the table's primary key is also used as a foreign key to relate child elements to their parents. For example, a menu item might have a number of child menu items, each of which in turn might have one or more children, each of which also might have one or more children, ad infinitum. The database table looks like this:

Hierarchical Data  With EF6

Each menu item has a ParentMenuItemId which is nullable. If a specific item is a top level item, the value will be null. Otherwise the value will be the item to which it is related. The MenuId links the items to a specific menu. This is how the two entities are defined using C#:

The Menu Entity

using System.Collections.Generic;

public class Menu
{
    public int MenuId { get; set; }
    public string MenuName { get; set; }
    public ICollection<MenuItem> MenuItems { get; set; }
}

The MenuItem Entity

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class MenuItem
{
    public int MenuItemId { get; set; }
    [StringLength(50)]
    public string MenuText { get; set; }
    [StringLength(255)]
    public string LinkUrl { get; set; }
    public int? MenuOrder { get; set; }
    public int? ParentMenuItemId { get; set; }
    public virtual MenuItem Parent { get; set; }
    public virtual ICollection<MenuItem> Children { get; set; }
    public int MenuId { get; set; }
    public virtual Menu Menu { get; set; }
}

The self-referencing relationship is defined through the nullable ParentMenuItemId property paired with the virtual Parent property. Any children are taken care of through the virtual ICollection<MenuItem> Children property. The convention that Code First Migrations needs in order to generate the appropriate tables and keys is a foreign key property named "Parent<name of key field>" (ParentMenuItemId) paired with a property named "Parent", and a collection named "Children". Here's the Up method that is generated by Code First Migrations for the two entities:

public override void Up()
{
    CreateTable(
        "dbo.MenuItems",
        c => new
            {
                MenuItemId = c.Int(nullable: false, identity: true),
                MenuText = c.String(maxLength: 50),
                LinkUrl = c.String(maxLength: 255),
                MenuOrder = c.Int(),
                ParentMenuItemId = c.Int(),
                MenuId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.MenuItemId)
        .ForeignKey("dbo.MenuItems", t => t.ParentMenuItemId)
        .ForeignKey("dbo.Menus", t => t.MenuId, cascadeDelete: true)
        .Index(t => t.ParentMenuItemId)
        .Index(t => t.MenuId);
            
    CreateTable(
        "dbo.Menus",
        c => new
            {
                MenuId = c.Int(nullable: false, identity: true),
                MenuName = c.String(),
            })
        .PrimaryKey(t => t.MenuId);
            
}

The ParentMenuItemId is nullable, which is what enables top level elements to exist. Here's some code to be executed in the Seed method that creates a Menu object and adds some MenuItems to it:

var menuItems = new List<MenuItem>{
    new MenuItem{MenuText = "First Link", LinkUrl = "#", MenuOrder = 1},
    new MenuItem{MenuText = "Second Link", LinkUrl = "#", MenuOrder = 2},
    new MenuItem{MenuText = "Third Link", LinkUrl = "#", MenuOrder = 3},
    new MenuItem{MenuText = "Fourth Link", LinkUrl = "#", MenuOrder = 4},
    new MenuItem{MenuText = "Fifth Link", LinkUrl = "#", MenuOrder = 5},
    new MenuItem{MenuText = "First Child Link", LinkUrl = "#", MenuOrder = 1, ParentMenuItemId = 1},
    new MenuItem{MenuText = "Second Child Link", LinkUrl = "#", MenuOrder = 2, ParentMenuItemId = 1},
    new MenuItem{MenuText = "Third Child Link", LinkUrl = "#", MenuOrder = 3, ParentMenuItemId = 1},
    new MenuItem{MenuText = "First Grandchild Link", LinkUrl = "#",  MenuOrder = 1, ParentMenuItemId = 7},
    new MenuItem{MenuText = "Second Grandchild Link", LinkUrl = "#", MenuOrder = 2, ParentMenuItemId = 7},
    new MenuItem{MenuText = "Third Grandchild Link", LinkUrl = "#", MenuOrder = 3, ParentMenuItemId = 7}
};
menu.MenuItems = menuItems;
context.Menus.AddOrUpdate(m => m.MenuName, menu);
context.SaveChanges();

The code creates five parent menu items. Then it creates three further items with a ParentMenuItemId specified. These become children of the first parent item. A further three items are created and related to the item with the MenuItemId of 7, which will actually be the second child item. The LINQ required to retrieve the menu items is very simple:

public ActionResult Index()
{
    var model = new List<MenuItem>();
    using (var context = new EFRecipeContext())
    {
        model = context.MenuItems.Where(m => m.MenuId == 1).ToList();
    }
    return View(model);
}

And the SQL that this generates is also very simple:

SELECT 
    [Extent1].[MenuItemId] AS [MenuItemId], 
    [Extent1].[MenuText] AS [MenuText], 
    [Extent1].[LinkUrl] AS [LinkUrl], 
    [Extent1].[MenuOrder] AS [MenuOrder], 
    [Extent1].[ParentMenuItemId] AS [ParentMenuItemId], 
    [Extent1].[MenuId] AS [MenuId]
    FROM [dbo].[MenuItems] AS [Extent1]
    WHERE 1 = [Extent1].[MenuId]

You could choose to explicitly include the Children navigational property:

model = context.MenuItems.Include(m => m.Children).Where(m => m.MenuId == 1).ToList();

This will alter the SQL to the following:

SELECT 
    [Project1].[MenuItemId] AS [MenuItemId], 
    [Project1].[MenuText] AS [MenuText], 
    [Project1].[LinkUrl] AS [LinkUrl], 
    [Project1].[MenuOrder] AS [MenuOrder], 
    [Project1].[ParentMenuItemId] AS [ParentMenuItemId], 
    [Project1].[MenuId] AS [MenuId], 
    [Project1].[C1] AS [C1], 
    [Project1].[MenuItemId1] AS [MenuItemId1], 
    [Project1].[MenuText1] AS [MenuText1], 
    [Project1].[LinkUrl1] AS [LinkUrl1], 
    [Project1].[MenuOrder1] AS [MenuOrder1], 
    [Project1].[ParentMenuItemId1] AS [ParentMenuItemId1], 
    [Project1].[MenuId1] AS [MenuId1]
    FROM ( SELECT 
        [Extent1].[MenuItemId] AS [MenuItemId], 
        [Extent1].[MenuText] AS [MenuText], 
        [Extent1].[LinkUrl] AS [LinkUrl], 
        [Extent1].[MenuOrder] AS [MenuOrder], 
        [Extent1].[ParentMenuItemId] AS [ParentMenuItemId], 
        [Extent1].[MenuId] AS [MenuId], 
        [Extent2].[MenuItemId] AS [MenuItemId1], 
        [Extent2].[MenuText] AS [MenuText1], 
        [Extent2].[LinkUrl] AS [LinkUrl1], 
        [Extent2].[MenuOrder] AS [MenuOrder1], 
        [Extent2].[ParentMenuItemId] AS [ParentMenuItemId1], 
        [Extent2].[MenuId] AS [MenuId1], 
        CASE WHEN ([Extent2].[MenuItemId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[MenuItems] AS [Extent1]
        LEFT OUTER JOIN [dbo].[MenuItems] AS [Extent2] ON [Extent1].[MenuItemId] = [Extent2].[ParentMenuItemId]
        WHERE 1 = [Extent1].[MenuId]
    )  AS [Project1]
    ORDER BY [Project1].[MenuItemId] ASC, [Project1].[C1] ASC

It enables population of the Children properties and is only necessary if you needed to reference them in your code. You might want to do this if you prefer to take a strictly object oriented approach to coding. However, all you really need to display this data is a recursive helper like the following BuildMenu method:

@helper BuildMenu(IEnumerable<MenuItem> data, int? parentId = null)
{
    var items = data.Where(d => d.ParentMenuItemId == parentId).OrderBy(i => i.MenuOrder);
    if (items.Any())
    {
        <ul>
            @foreach (var item in items)
            {
                <li>
                    <a href="@item.LinkUrl">@item.MenuText</a>
                    @BuildMenu(data, item.MenuItemId)
                </li>
            }
        </ul>
    }
}

The menu data is passed in to the helper the first time with a parentId value being omitted and therefore defaulting to null. The menu items are queried for all those that have a ParentMenuItemid that matches the parentId value, which on the first iteration yields all those that are root items. If there are any matches, an unordered list is created and each item is displayed as a list item with a link. As each item is rendered, the entire collection is passed back into the BuildMenu method with the parentId parameter set to the id of the current item, so that a check for any children belonging to the current item can be performed. The resulting output looks like this:

Hierarchical Data With EF6

Summary

This article introuced the simplest way to model a self-referencing table in SQL Server using Entity Framework Code First Migrations, and looked at how to store and retrieve data that represents a hierarchy. Finally, the data was retrieved from the database and a recursive function was used to manage the display of the hierarchical data.

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 53339

9 Comments

- Satyabrata Mohapatra

Great article.New website looking beautiful and very professional.Just wanted to know how you change the look and feel of the website(http://www.mikesdotnetting.com)?Have you just modified the CSS?Recent comment section is now not showing?Why?Plz add that.

- Mike

@Satyabrata,

I upgraded the site to MVC 5 and while I was doing that, I decided to use Bootstrap for the UI. I chose the Flatly theme from Bootswatch: http://bootswatch.com/flatly/.

I might add the recent comments panel back in soon.

- Prateek

Good Job @Mikey
I's vImportant stuff in this blog.....
KeepItUp...

- Sulev

Thank you for the post!

Why is the Menu Entity needed? Is it a root item for navigation purposes only?

- Alexandros L

You are a live saver.. thank you so much...

- René Kåbis

Just curious -- in the “MenuItemEntity” above, how crucial is it to use the term “Parent” in the actual column name and the virtual reference? I have been looking everywhere, but nothing explains why we are forced to use “Parent” instead of another term such as “HeadOffice”[Id] which is a nullable foreign key value meant to contain the “CompanyId” primary key

- Mike

@René

The use of the Parent and Children properties in the C# class is important. It is a convention that EF expects. However, you can map those to any column in the database that you like. So if you want to use HeadOfficeId in your database, you can.

- gholamreza rabbal

thanks, EF can Create Tree Without Join !
just retrieve one list like following way:
model = context.MenuItems.Where(m => m.MenuId == 1).ToList().Where(m=>m.Parent==null).ToList();

the second Where(m=>m.Parent).ToList() is Linq to object that create Tree .

- Hassan, Lahore, Pakistan

Dear and Respected
OMG, you are Just awesome.

you made the concept so clean and so deep.

Probably, I can go to any level deep? using this strategy, please correct me if I am wrong.

I have been your fan for so long now, probably since my first sql query learning some 3 years back.

Hopefully, I would share my Website menu where I gona apply this



If I can have your email too :), I good fan should have it :)

Stay Bless Dear Mike,

Recent Comments

Pam 30/08/2017 11:30
In response to Sending Email in Razor Pages
Mike, RazorPages sound like a nice choice for somebody still working in ASP classic who wants to to...

Robby Robson 15/08/2017 00:43
In response to Routing in Razor Pages
Mike: great stuff. Now that .Core Standard 2.0 is formally out, how soon will you rewrite your book...

Satyabrata Mohapatra 28/07/2017 08:59
In response to Sending Email in Razor Pages
Bit off topic, but congratulation sir for your MVP award. You deserve it !!!...

Satyabrata Mohapatra 23/07/2017 16:43
In response to Razor Pages - The Elevator Pitch
@Dale Severin You can continue to build apps using asp.net web pages....

Satyabrata Mohapatra 23/07/2017 16:40
In response to Sending Email in Razor Pages
Thanks for sharing...learned a lot...

Gfw 22/07/2017 11:53
In response to Sending Email in Razor Pages
Question... Does System.Net.Mail support SSL?...

Dale Severin 20/07/2017 03:38
In response to Razor Pages - The Elevator Pitch
I work with razor web pages extensively. I appreciate the rapid development it permits me to I am as...

Obinna Okafor 14/07/2017 01:19
In response to Routing in Razor Pages
Thank you, Mike. Good post....

Satyabrata Mohapatra 11/07/2017 16:02
In response to Routing in Razor Pages
Very powerful routing system!!...

Cyrus 05/07/2017 03:41
In response to Razor Pages - Getting Started With The Preview
How can I trim packages and services as much as possible to use just razor pages? I don’t want to to...