Self Referencing Tables In Entity Framework

Self-referencing table in entity framework means in order to store some hierarchical data you can say that a self referencing table is a table where the primary key (PK) of a table is also known a foreign key.

Basically in this topic we will cover how to deal with hierarchical data in entity framework. This hierarchical data is stored in self referencing tables.

Now we will understand this with an example. Firstly, go to SQL Server Studio and create a table, ensure that in this table we will have one primary key and other foreign key references of this table’s primary key so we can say this table is a self referencing table.

Create table Students
(
StudentID int primary key identity,
StudentName nvarchar(50),
HODID int foreign key references Students(StudentID)
)
GO

After creating your table your table design will be like this.

table

Now insert some data to Table but remember that in one row you put NULL for HODID . Initially you will table with NULL HODID.

Insert into Students values ('Munesh', NULL)
Insert into Students values ('Rahul', NULL)
Insert into Students values ('Micale', NULL)
Insert into Students values ('Tom', NULL)
Insert into Students values ('Ram', NULL)
Insert into Students values ('Lick', NULL)
Insert into Students values ('ABC', NULL)

Now Update your table For HODID and execute this below query.

Update Students Set HODID = 5 Where StudentName IN ('Ram', 'Rahul')
Update Students Set HODID = 3 Where StudentName IN ('Tom','Micale')
Update Students Set HODID = 5 Where StudentName IN ('Lick')
Update Students Set HODID = 4 Where StudentName IN ('ABC')
GO

After that your table will look like this with data,

table

Now go to your application and add a ADO.Net Entity DataModal and give the connection when your entity will create and add automatic navigation Property (Students1 and Student1) and one self-referencing association
table

Right click on Students1 navigation property and click properties. In this property window Multiplicity is set to Many. So, that navigation property returns Students who are subordinates.

table

Do the same with Student1 which means go to properties and set multiplicity as Zero or OneSo, this navigation property returns the HOD of an Student.

table

.
Now we want to retrieve some data from table , For example we have a default table with some studentName and HODName.

StudentName	HODName
Munesh	Principle
Rahul	R1
Micale	M1
Tom	T1
Ram	R1
Lick	L1

Now drag and drop a GridView control From tool box and write below cde in the code-behind file at Page_Load event.

protected void Page_Load(object sender, EventArgs e)
{
StudentDBContext _student = new StudentDBContext();
GridView1.DataSource = _student.Students.Select(c => new
{
StudentName = c.StudentName,
HODName = c.Student == null ? "Principle" : c.Student1.StudentName
}).ToList();
GridView1.DataBind();
}

Now run your application you will see this data in Gridview.

5 Comments

Join the conversation! Your thoughts help the community grow.

Rajeev Punhani10y ago
Nice Article .Could you please explain what are these navigation properties Students1 and Student1 and what is the use.
Munesh Sharma10y ago
thank you all
Bhavik Patel10y ago
nice
Sonu Chaudhary10y ago
good one...
Debasis Saha10y ago
Nice One..