Table Splitting in ASP.Net

In Entity Splitting means splitting this entity into multiple database tables  but in Table Splitting we split one table into two tables.

Entity Splitting refers to mapping an entity to two or more tables when the tables have a common key (common column).

Why to use Table Splitting in EF? One reason would be to delay some property, in other words Lazy Loading of your objects.

We will explain how we use Table Splitting in EF with an example.

Step 1: First create a table named Student.

For this execute the following script in SQL Server.

Create table Students
(
   StudentID int primary key identity,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   Gender nvarchar(50),
   Email nvarchar(50),
   Mobile nvarchar(50)
)
Insert into Students values ('Munesh', 'Sharma', 'Male','[email protected]','5555555555')
Insert into Students values ('Rahul', 'Sharma', 'Male','[email protected]','333333333')
Insert into Students values ('Sara', 'vilium', 'Female','[email protected]','111111111')
Insert into Students values ('Mark', 'hash', 'Female','[email protected]','2222222222')
Insert into Students values ('ABC', 'EFG', 'Male','[email protected]','6666666666')

After executing that script you will see a table in your database with the name Student.

Now go to your application and right-click on the Solution Explorer and add an “ADO.Net Entity Data Model” and select the Database First Approach.

Then click on "Next" and select the database connection and select the database name and then click on "Next" and select your tables that you created now and click Finish.

When you click on Finish you will see a “Student” entity.

Now in this entity we have Mobile and email properties. We are not using these properties everywhere, like Firstname , Lastname and Gender properties. If we load the Student entity then all these entities will be loaded automatically, so we will now create 2 entities (Student and StudentContactDetails).

The following is the procedure to do that.

1. Right-click on the Entity Designer and click on “Add entity”. Set some values as in here:

a: Entity Name = StudentContactDetail

b: Base type = None

c: Entity Set = StudentContactDetail

d: Key Property check Box = checked

e: Property name : StudentId

f: Property type : Int32

2. Now cut Mobile and Email from the Student entity and paste it into StudentContactDetail.

Then the entity will look like:

3. Again right-click on the Entity Designer and add an “Association” and fill in the following details.

4. Now right-click on the association and click on properties. When you click on properties a window will open and from there select “Referential Constraints” and fill in the details.

 






5. Now right-click on the StudentContactsDetails entity and click on mapping.

 

 

6. Right-click on the Solution Explorer and add a Webform and Drag down a GridView and 2 buttons.

<div style="font-family:Arial">
    <asp:Button ID="FullDataWithContactDetail" runat="server" Text="GetStudent Data with contact detail"
onclick="Button1_Click" />
    <br />
    <asp:Button ID="Button2" runat="server" Text="Get Student Data"
onclick="Button2_Click" />
    <asp:GridView ID="GridView1" runat="server"></asp:GridView>
</div>

7. Use the following code in this Webform (in the code behind file).

public partial class WebForm1: System.Web.UI.Page
{
    private DataTable GetStudentData()
    {
        StudentDBContext _studentDBContext = new StudentDBContext();
        List < Student > _students = studentDBContext.Student.ToList();
        DataTable dt = new DataTable();
        DataColumn[] columns =
        {
            new DataColumn("StudentID"),
            new DataColumn("FirstName"),
            new DataColumn("LastName"),
            new DataColumn("Gender")
        };
        dt.Columns.AddRange(columns);
        foreach(Student students in _students)
        {
            DataRow dr = dt.NewRow();
            dr["StudentsID"] = students.StudentsID;
            dr["FirstName"] = students.FirstName;
            dr["LastName"] = students.LastName;
            dr["Gender"] = students.Gender;
            dt.Rows.Add(dr);
        }
        return dt;
    }

private DataTable GetStudentDatawithContactDetails()
{
    StudentDBContext _studentDBContext = new StudentDBContext();
    List < Student > _student = _ studentDBContext.Student.Include("StudentContactDetail").ToList();
    DataTable dt = new DataTable();
    DataColumn[] columns = {
        new DataColumn("StudentID"),
        new DataColumn("FirstName"),
        new DataColumn("LastName"),
        new DataColumn("Gender"),
        new DataColumn("Email"),
        new DataColumn("Mobile")
        dt.Columns.AddRange(columns);
        foreach(Student student in _student)
        {
            DataRow dr = dt.NewRow();
            dr["StudentID"] = student.StudentID;
            dr["FirstName"] = student.FirstName;
            dr["LastName"] = student.LastName;
            dr["Gender"] = student.Gender;
            dr["Email"] = student.StudentContactDetail.Email;
            dr["Mobile"] = student.StudentContactDetail.Mobile;
            dataTable.Rows.Add(dr);
        }
        return dt;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        GridView1.DataSource = GetStudentDatawithContactDetails();
        GridView1.DataBind();
    }

protected void Button2_Click(object sender, EventArgs e)
{
   GetStudentData();
}

8. Now run your application and check that your data is with contact detail and without contact detail.

You can go to my blog_munesh.