Table splitting in entity framework with code first approach

 Table splitting we split one table into two tables.

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 StudentContactDetail StudentContactDetail { get; set; }     } }

Step 3: Add a another class to the project And give the  Name as StudentContactDetail.cs. And write the below code.

namespace EFTableSplitting {     public class StudentContactDetail     {         public int StudentID { get; set; }         public string Email { get; set; }         public string Mobile { get; set; }                  public Student Student { get; set; }     } }

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

using System.Data.Entity; namespace EFTableSplittings {     public class StudentDBContext : DbContext     {         public DbSet<Student> Student { get; set; }         protected override void OnModelCreating(DbModelBuilder _Modal)         {            _Modal.Entity<Student>()                 .HasKey(c => c.StudentID)                 .ToTable("Student");            _Modal.Entity<StudentContactDetail>()                 .HasKey(c => c.StudentID)                 .ToTable("Student");            _Modal.Entity<Student>()                 .HasRequired(c => c.StudentContactDetail)                 .WithRequiredPrincipal(c => c.Student);         }     } }

Step 5: 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 6: Now Add a web form And pest below code in HTML code portion.

<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> </div>

Step 7: Write the below code in code behind section

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 student in students)         {             DataRow dr = dt.NewRow();             dr["StudentID"] = student.StudentID;             dr["FirstName"] = student.FirstName;             dr["LastName"] = student.LastName;             dr["Gender"] = student.Gender;             dt.Rows.Add(dr);         }         return dt;     }     private DataTable FullDataWithContactDetails()     {         StudentDBContext studentDBContext = new StudentDBContext();         List<Student> students = 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 students)         {             DataRow dr = dt.NewRow();             dr["EmployeeID"] = student.StudentID;             dr["FirstName"] = student.FirstName;             dr["LastName"] = student.LastName;             dr["Gender"] = student.Gender;             dr["Email"] = student.StudentContactDetail.Email;             dr["Mobile"] = student.StudentContactDetail.Mobile;             dt.Rows.Add(dr);         }         return dt;     }     protected void Button1_Click(object sender, EventArgs e)     {                      GridView1.DataSource = FullDataWithContactDetails();                 GridView1.DataBind();     }   protected void Button2_Click(object sender, EventArgs e)     {         GetStudentData();      }  }

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

Step 8: Insert test 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)

Step 9:  Run your application and check your data that it is working properly or not.

Table splitting in entity framework

In this tutorial we will learn table splitting in entity Framework. Table Splitting is just opposite of Entity Splitting.

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

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

Why we Use Table Splitting in EF : One reason of this question is that when we want to delay of some property means Lazy loading of to load your objects.

How we use Table splitting in EF, we will understand with an example.

Step(1) : First of all create a table with name Student.

For this execute below 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','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')

After execute this script you will see a table in your database With the name Student.

Now go to your application and Right click on solution explorer and add a “ADO.Net Entity Data Modal” and select  database first approach. 

After that click on next and select database connection and select database name and then click on next and select your tables which is created now, And click FINISH

When you click on FINISH you will see one “Student” Entities

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

For Getting this there are some steps

(1)        :Right click on the Entity designer and click on “Add entity” .And Set some values 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 Student entity and pest it into StudentContactDetail.

Then entity will look like that

(3)        Again Right click on entity designer and Add a “Association” And fill the Some details

(4)        Now Right click on Association And click on properties , when you will click on property a window will open from there select “ Referential Constraint” and fill Details

(5)        Now right click on StudentContactsDetails entity and click on mapping.

(6)        Right click on solution explorer and add a Webform and Dragdown 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)        Write below code on this Webform(In 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 your data with contact detail and without contact detail.