Monday 13 April 2015

Table Per Type inheritance in entity framework

The concept of Table Per Type (TPT) is that For every entity it will create new table. For example in below screen there are 3 different entity (Student, Collage Student, School Student) it will create 3 new tables for each entity.

In Table Per Hierarchy we saw that one database table store all the data for all the entity types.
With this there is a problem that when we store Collage student entity data into table then the columns “CollageStudentName” and “CollageStudentbranch” will be left NULL and Same goes to School student.
For removing this problem we use Table Per Type inheritance In this we will create 3 tables for each entity.
3 Tables name is “Student table”, “CollageStudent Table”, “SchoolStudent table” The common thing in all the tables is that “StudentID” will be Same.
This StudentID will be primary key in Student table and will be foreign key in CollageStudent and SchoolStudent table.
Create Table Student
(
     StudentID int primary key,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
)
GO

Create Table CollageStudents
(
     StudentID int foreign key references
     Student(StudentID) not null,
     CollageName nvarchar(50),
         CollageBranch nvarchar(50)
)
GO

Create Table SchoolStudents
(
     StudentID int foreign key references
     Student(StudentID) not null,
     SchoolName nvarchar(50),
         SchoolClass nvarchar(50)
)
GO


Insert into Student values (1, 'Munesh', 'Sharma','Male')
Insert into Student values (2, 'Rahul', 'Sharma','Male')
Insert into Student values (3, 'Sara', 'vilium','Female')
Insert into Student values (4, 'Rani', 'hash','Female')
Insert into Student values (5, 'XYZ', 'ABC','Female')
Insert into Student values (6, 'Anshuman', 'EFG','Male')


Insert into CollageStudents values (1, 'VIT','IT')
Insert into CollageStudents values (4, 'MIT','ECE')
Insert into CollageStudents values (6, 'BTC','Mechenical')



Insert into SchoolStudents values (2, 'KVM','Seven')
Insert into SchoolStudents values (3,'Aadharsh','Eight')
Insert into SchoolStudents values (5, 'Ravat','Tenth')


Now go to your application and right click on solution explorer and select Ado.Net Entity data Modal and select your table and generate Entity , 3 following entity will generate like this.

Now Delete the association b/w Student entity and CollageStudent entity and this also automatically delete CollageStudent navigation property from student entity and student navigation property from CollageStudent entity.
Same thing you do with SchoolStudent entity.


Now add the inheritance relationship b/w Student entity and collageStudent entity.
(1)Right click on the designer surface and click on Add-Inheritance option.
(2)Select Student at base entity and collageStudent as the derived entity.


(3)When you will click on inheritance your screen will look like that and you put base entity and derived entity.

(4)Same you do with Student entity and SchoolStudent Entity means generate inheritance for these entities.

(5)After creating inheritance b/w these entities your entity modal will look like this.


(6)Lets Understand this with an example.
<div style="font-family: Arial">
       <asp:Button ID=" Button1" runat="server" Text="All Student Information"
        onclick="Button1_Click" />
   <asp:Button ID=" Button2" runat="server" Text="Collage Student Information"
        onclick="Button2_Click" />

   <asp:Button ID=" Button3" runat="server" Text="School Student Information"
        onclick="Button3_Click" />

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

protected void Button1_Click(object sender, EventArgs e)
    {
 GridView1.DataSource = ConvertListToDataTable(
                        studentDBContext.Students.ToList());
                    GridView1.DataBind();


}
protected void Button2_Click(object sender, EventArgs e)
    {
 GridView1.DataSource = studentDBContext.Students
                        .OfType<CollageStudent>().ToList();
                    GridView1.DataBind();


}
protected void Button3_Click(object sender, EventArgs e)
    {
  GridView1.DataSource = studentDBContext.Students
                        .OfType<SchoolStudent>().ToList();
                    GridView1.DataBind();


}


private DataTable ConvertListToDataTable (List<Student> students)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("FirstName");
            dt.Columns.Add("LastName");
            dt.Columns.Add("Gender");
            dt.Columns.Add("SchoolName");
            dt.Columns.Add("SchoolClass");
            dt.Columns.Add("CollageName");
            dt.Columns.Add("CollageBranch");
            dt.Columns.Add("Type");

            foreach (Student _student in students)
            {
                DataRow dr = dt.NewRow();
                dr["ID"] = _student.ID;
                dr["FirstName"] = _student.FirstName;
                dr["LastName"] = _student.LastName;
                dr["Gender"] = _student.Gender;

                if (_student is CollageStudent)
                {                     dr["CollageName"] = ((CollageStudent) _student). CollageStudentName;
                    dr["CollageBranch"] = ((CollageStudent) _student).CollageStudentBranch;

                   
                    dr["Type"] = "CollageStudent";
                }
                else
                {
                   dr["SchoolName "] = ((SchoolStudent) _student).SchoolStudentName;
dr["SchoolClass   "] = ((SchoolStudent) _student).SchoolStudentBranch;
                    dr["Type"] = "SchoolStudent";
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }
    }
}



Run your application and see the output.

No comments:

Post a Comment

C# program Selection Sorting

Selection sort is a straightforward sorting algorithm. This algorithm search for the smallest number in the elements array and then swap i...