How to work with Hierarchal data in Entity Framework

INTRODUCTION

A couple of years back I blogged about how to retrieve the hierarchical data using T-SQL features. You can read that blog post here. Today life brought to me the same cross roads and I decided to take new route , so instead of using the T-SQL approach I preferred to go towards the Entity Framework. Microsoft has recently done some great enhancements in this data access framework especially with the release of version 4.1 , you can also take the advantage of code first approach. In this post I show you how you can get hierarchal data from your database using Entity Framework. So let us get started.

GETTING STARTED

In this post I am using the same example which I used for my earlier post. We have same employee table but this time it is self referencing table. The EmployeeId column, which is the primary key in the table is referencing to the ManagerId column (Allow null is set to true) in the same table. See the screen shot below.

Self referncing Employee table

The following is the sample data in this table

Employee table data

In the above screen shot you must observe that the ManagerId for the first row is null, which means this is the root in the hierarchy.

So now when you import this table into your model using the Entity Framework wizard , as this is self referencing table the wizard will create two navigational properties called Employee1 and Employee2 respectively. Refer to the screen shot below.

InitialModel

These two properties does not make any sense to the developers, so let us try to rename it. Right click on the Employee1 property and select properties from the context menu. In the properties window you will see that the multiplicity is set to multiple, which means that the association is of Many type. Check out the screen shot below.

Employee1 property

In the same manner let us also rename Employee2 property , if you right click this property and see the properties window you will find that multiplicity of this column is set to 0..1 , which means that the association end can be null or one. We will rename this column to Manager as seen in the screen shot below.

Employee2

Let me explain you further about the database relationships. Any relationship in the database has three characteristics.

  1. Degree : is the number of entity types that participates in the relationship. Unary and Binary relationship are most common.
  2. Multiplicity : is the number of entity types on each end of the relationship. Few examples are 0..1 (zero or one), 1 (one) and * (many).
  3. Direction : This can be unidirectional or bidirectional

In our example Degree is of unary type , as only Employee entity is involved in the relationship. Multiplicity is 0..1 and * and direction is bidirectional.

I hope that the above discussion have cleared your concepts about the database relationships, let us see now some code which displays the hierarchal data.

 Code

The above code loops through employee entity and checks whether the manager property is null and based on that writes the output . See the output for this code below.

ProgramOutput

CLOSURE

Anything new that comes out from Microsoft stable excites me. I always make a point to utilize the new technology whenever I get new project or opportunity. This blog post was also the result of the same passion. I encourage developers to keep the learning passion always alive because if that dies then consider your career to be finish. Let me know your thoughts on it in the form of comments.

KEEP LEARNING

Advertisements

8 thoughts on “How to work with Hierarchal data in Entity Framework

  1. I have been struggling with an efficient way to manage this type of data ensuring that there are no cycles created. I’m not sure if this should be done by the code or by some trigger at the database level. Do you have any thoughts on this subject?

    • Hi
      Well I have always preferred a .net approach to a T-SQL approach. For me the database is just a repository for storing data. This mindset also helps when you are moving to cloud databases where most of the offerings are NOSQL databases. I always tried to emphasize on the Middle tier which are .net components. And Data Access using EF is one of them. This team is doing amazing job with every new release.

      Hope this helps.

      Cheers.

  2. Hi,
    Is there a way for me, in EF, to get all employee’s managers with their managers (recursively)? Also, downward, getting all of the manager’s employees with their employees?

    Thanks,
    Sagy

    • Hi
      This is possible using EF. Create a function which accepts the employeeID has the argument. Retrieve the record and loop until you get manager property of that employee = null . This will get you the managers of the self employee till the highest level and the same funda will work for getting the subordinates. But here you need to check the subordinates property of given employee.
      Hope this helps

      Thanks

  3. I followed your method but I’m having issues saving to the database. I get the following exception: Unable to determine the principal end of the ‘RELATIONSHIP_Name’ relationship. Multiple added entities may have the same primary key

    • Hi Rafi
      This blog post code was written with Entity Framework 4.1 version. I’m not sure about its compatibility with version 5 of EF. Please check your version.

      Thanks
      Goldy

      • Thanks for the quick response. After while, I figured out the issue and it was unrelated to your code. Your demo does work in EF 5. My issue was that I had another foreign key requirement from the hierarchical table and I wasn’t adding the children rows to the other foreign key table but only to the parent items of the same table. Once I added them to the other table then it worked.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s