Self Referencing Tables in Entity Framework

Self-referencing table in entity framework means in order to store some hierarchical data or 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 table.

Now we will understand this with an example . First to 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 as 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.

Now insert some data to Table but remember that 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 be look like this with data

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

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

Same you do with Student1 Means go to properties and set multiplicity as Zero or One So, this navigation property returns the HOD of an Student

. 

Now we want to retrieve some data from table , For example we have 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.

Conditional Mapping in entity framework with code first

I Explained before that Conditional mapping in Entity Framework means When we want only a portion of data From database or table according to a condition then for fulfil this situation we use Conditional Mapping in EntityFramework.

Here we will learn Conditional mapping in Entity Framework with code first Approach.

Step 1: Create a new web application And install Entity framework..

Step 2: Add a class to the project. Give the Name as Student.cs. Write Below code.

namespace EFTableSplitting {     public class Student     {         public int StudentID { get; set; }         public string FirstName { get; set; }         public string LastName { get; set; }         public string Gender { get; set; }           public bool IsDiscard s{ get; set; }     } }

Step 3: Add a class to the project For the DBContext and give the name  it asStudentDBContext.cs. And Write below code.

using System.Data.Entity; namespace EFTableSplitting {     public class StudentDBContext : DbContext     {         public DbSet<Student> Students { get; set; }         protected override void OnModelCreating(DbModelBuilder _Modal)         {            _Modal.Entity<Student>()                 .Map(c => c.Requires("IsDiscard")                 .HasValue(false))                 .Ignore(c => c.IsDiscard);             base.OnModelCreating(_Modal);         }     } }

Step 4: Add the database connection string in web.config file.

<connectionStrings>   <add name="StudentDBContext"       connectionString="server=.; database=entitysplitting; integrated security=SSPI;"       providerName="System.Data.SqlClient" /> </connectionStrings>

Step 5: Add a webform to the project. Go to Tool Box and Drag and drop a GridView control. 

<asp:GridView ID="GridView1" runat="server">     </asp:GridView> s

Step 6: Write below code at page load event.

protected void Page_Load(object sender, EventArgs e) {     StudentDBContext studentDBContext = new StudentsDBContext();     GridView1.DataSource = studentDBContext.Students.ToList();     GridView1.DataBind(); }

Step 7: If you already have entitysplitting database in SQL Server. Delete it first

And then Run the application.

Step 8: At this point, run the application. entitysplitting database and Student table should be created by the entity framework.

Step 9: Insert data using the following SQL script

Insert into Students values ('Munesh', 'Sharma', Male',m@g.com,5555555555) Insert into Students values ('Rahul', 'Sharma', 'Male',R@g.com,333333333) Insert into Students values ('Sara', 'vilium', 'Female',S@g.com,111111111) Insert into Students values ('Mark', 'hash', 'Female',M1@g.com,2222222222) Insert into Students values ('ABC', 'EFG', 'Male',A@g.com,6666666666)