How to work with Hierarchal data in Entity Framework

INTRODUCTION

A couple of years back I blogged about how to retrieve the hierarchal 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.

The following is the sample data in this table

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.

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.

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.

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.

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.

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