Entity Framework Recipe: Hierarchical Data Management

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:

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> Childrenproperty. The convention that Code First Migrations needs in order to generate the appropriate tables and keys is a foreign key property named "Parent" (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 theMenuItemId 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 theparentId 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:

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.

EF Code First: Add a Foreign Key relationship

Shows how to add foreign key constraints using Entity Framework Code First.

Introduction

We saw in the first article EF Code First: Let's try it, how to generate a table using EF Code First. In this article, we are going to see how to define a foreign key constraint.

Using the code

We want to add a new table to work with a foreign key. Let's add a new Project class like this:

Hide   Copy Code

public class Project
{
    public int ProjectId { get; set; }
    public string Name { get; set; }
    public int ManagerId { get; set; }
    public Person Manager { get; set; }
}

You can see the Manager property is of type Person and I added the ManagerId property to be the key between the two tables.
Now add the DbSet of Project as you did for Person:

Hide   Copy Code

public DbSet Projects { get; set; }

Run the application. You should get an InvalidOperationException: The model backing the 'MyContext' context has changed since the database was created. Either manually delete/update the database, or callDatabase.SetInitializer with an IDatabaseInitializer instance. For example, theDropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.
This is because the changes we made have too many effects on the database and it should be deleted. But the EF Context can't decide itself if it can drop the database to recreate it. What's the solution? There are two solutions:

• You can delete the database yourself and rerun the application.
• You can use SetInitializer using DropCreateDatabaseIfModelChanges.

I won't explain the first solution. About the second one, it's a feature allowed by Code First. You can ask the context to automatically drop and recreate the database if the model changes. You should be very careful with this option, because in a production environment, you can lose all your data. Because we don't want to drop the database ourselves, we will see how to tell the context to do it for us.
In the file MyContext.cs, add a new class: MyInitializer.

Hide   Copy Code

public class MyInitializer : DropCreateDatabaseIfModelChanges
{
}

As you can see, the database would be dropped and recreated each time the model changes. You can do more things in the initializer but we will see that later in this post.
Now we will set the initializer. Go back to Program.cs and add the following line at the beginning of the Mainmethod:

Hide   Copy Code

Database.SetInitializer(new MyInitializer());

Now you can run our application.
What has happened? The context has detected changes in the model and so, the database has been deleted and recreated. If you check the tables in the database, you should see two tables: People and Project. The person in the Main method has been created in the People table. The item appears only once because the table was recreated and so, the previous record was deleted and not backed up.
If we look closer, we can see a column named ManagerId and another one named Manager_PersonId.

The first one was created because of our property ManagerId. The second one was created because of our navigation property to the list of Persons.

EF Code First has created a foreign key for us and it's great, but what we'd want is to use the ManagerIdproperty. So let's modify our Project class to use this property vs. let Code First create it for us.
To configure Code First and the way it will generate our database, there are two methods: DataAnnotation andFluent API.
Let's use DataAnnotation. Modify your class like this:

Hide   Copy Code

public class Project
{
    public int ProjectId { get; set; }
    public string Name { get; set; }
    public int ManagerId { get; set; }
    [ForeignKey("ManagerId")]
    public Person Manager { get; set; }
}

You should add the System.ComponentModel.DataAnnotations namespace. Adding the ForeignKeyattribute, we say to Code First that we want the ManagerId properties to be used as Foreign Keys to the Person table.
Run the application and check your database schema. The Manager_PersonId column does not exist anymore, there is only the ManagerId column and it is declared as a foreign key to the People table.

We just created our first one to many relationship using EF Code First. As we have seen, we can declare the FK in the class or let Code First manage it for us. In the next article, we will talk about Data Annotation and Code Fluent.