Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Tue, 02-Sep-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
37300
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

How to work with Hierarchal data in Entity Framework

Goldytech
Posted by under ADO.NET category on for Intermediate level | Points: 250 | Views : 8309 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
Rating: 5 out of 5
1 vote(s)
In this article I teach you how you can get hierarchal data using Entity Framewwork with self referencing table

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.

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

Page copy protected against web site content infringement by Copyscape
About the Author

Goldytech

Full Name: Muhammad Afzal Qureshi
Member Level: Bronze
Member Status: Member
Member Since: 8/4/2009 10:58:17 PM
Country: India

http://goldytech.wordpress.com

Hello Everyone Myself Muhammad Afzal , aka GoldyTech. Thats my pen name. I reside in India and work as a solution Architect on .NET platform. I hope you must have enjoyed reading my blog. Please leave your comments or suggestions good or bad and help me to improve
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)