Post
Ask Question

Table Splitting in Entity Framework 6 (Code First Approach)

Introduction

 
One of the major benefits of the Entity Framework is that our entity model does not need to match our stored model (database model). Table splitting gives us the ability to map multiple entities to a single database table. Table splitting is just the opposite of entity splitting. In other words, two or more entities of our model are mapped to the same physical database table. Earlier, the database first approach is knowns as Table Per Hierarchy (TPH). In this article, I am explaining it with the Code First approach.
 
Table Per Hierarchy is one of the inheritance types and uses a single table in the database to maintain the data and uses multiple entities in Entity Framework. In other words, table splitting involves mapping multiple entities in a conceptual layer to a single table in a store (database) layer.
 
Suppose we have a table in the database called “EmployeeMaster” to store the employee information and in the entity model there are two entities, one for the stored basic information (code and name) and another for storing additional information (phone number and email address). Here the database table stores all the information in a single physical table.
 
employee
 
In Code First, we will have the following two entities and DbContext configuration to accomplish this scenario.
  1. [Table("EmployeeMaster")]  
  2. public partial class Employee  
  3. {  
  4.     [Key]  
  5.     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
  6.     public int EmployeeId { get; set; }  
  7.   
  8.     [StringLength(10)]  
  9.     public string Code { get; set; }  
  10.   
  11.     [StringLength(50)]  
  12.     public string Name { get; set; }  
  13.     [ForeignKey("EmployeeId")]  
  14.     public virtual EmployeeDetails Details { get; set; }  
  15.   
  16. }  
  17.   
  18. [Table("EmployeeMaster")]  
  19. public partial class EmployeeDetails  
  20. {  
  21.     [Key]  
  22.     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
  23.     public int EmployeeId { get; set; }  
  24.     [StringLength(25)]  
  25.     public string PhoneNumber { get; set; }  
  26.     [StringLength(255)]  
  27.     public string EmailAddress { get; set; }  
  28.           
  29.     public virtual Employee Employee { get; set; }  
  30. }  
DbContext Class
  1. public partial class EntityModel : DbContext  
  2. {  
  3.     public EntityModel() : base("name=EntityModel")  
  4.     {  
  5.         Database.Log = Console.WriteLine;  
  6.     }  
  7.   
  8.     public virtual DbSet<Employee> Employees { get; set; }  
  9.     public virtual DbSet<EmployeeDetails> EmployeeDetails { get; set; }  
  10.   
  11.     protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  12.     {  
  13.          modelBuilder.Entity<Employee>()  
  14.             .HasRequired(e => e.Details)  
  15.             .WithRequiredDependent(e => e.Employee);  
  16.     }  
  17. }  
When we query the employee and employee details entity, Entity Framework will automatically generate the query. To analyze the query, we just turn on "Logging SQL" of Entity Framework.
 
Let us consider the four scenarios of select, insert, update, and delete and check the behavior of the Entity Framework.
 

Select scenario 

  1. Querying on Employees Entity
     
    When we query on employee entity, it will retrieve the columns related to the Employee entity (EmployeeId, Code, and Name).
     
    Example code
    1. using (EntityModel context = new EntityModel())  
    2. {  
    3.    var employees = context.Employees.ToList();  
    4. }  
    Output
     
    Output
     
  2. Querying on EmployeeDetails Entity
     
    When we query on the employee details entity, it will retrieve the columns related to the Employee Detail entity (EmployeeId, Phone number, and email address).
     
    Example Code
    1. using (EntityModel context = new EntityModel())  
    2. {  
    3.    var employees = context.EmployeeDetails.ToList();  
    4. }  
    Output
     
    context
     
  3. Both the entities together
     
    When we query on both entities, employee and employee details, it will retrieve the columns related to both entities (EmployeeId, Code, Name, Phone number, and email address).
     
    Example Code
    1. using (EntityModel context = new EntityModel())  
    2. {  
    3.    var employees = context.Employees.Include("Details").ToList();  
    4. }  
    Output
     
    Example

Insert entity scenario

 
 When we do an insert operation on employee and employee details entities, Entity Framework generates a single insert query for inserting the data.
 
Example Code
  1. using (EntityModel context = new EntityModel())  
  2. {  
  3.     Employee employee = new Employee();  
  4.     employee.Code = "A0003";  
  5.     employee.Name = "Rakesh Trivedi";  
  6.     employee.Details = new EmployeeDetails { EmailAddress = "[email protected]", PhoneNumber = "895648552" };  
  7.     context.Employees.Add(employee);  
  8.     context.SaveChanges();  
  9. }  
Output
 
Insert entity scenario
 

Update entity scenario

 
When we do an update operation on the employee entity or employeedetail, the Entity Framework generates a single update query with which the column has been updated.
 
Example Code
  1. using (EntityModel context = new EntityModel())  
  2. {  
  3.     Employee employee = context.Employees.Include("Details").Where(p => p.EmployeeId == 1).FirstOrDefault();  
  4.     employee.Details.PhoneNumber = "5689234556";  
  5.     context.SaveChanges();  
  6. }  
Output
 
EntityMode
 

Delete entity scenario

 
When we do a delete operation on an employee or employee details entity, the Entity Framework generates a delete query. Here one point must be considered, that we must set the entity state to “deleted” for both of the entities.
 
Example Code
  1. using (EntityModel context = new EntityModel())  
  2. {  
  3.     Employee employee = context.Employees.Include("Details").Where(p => p.EmployeeId == 2).FirstOrDefault();  
  4.     context.Entry<EmployeeDetails>(employee.Details).State = System.Data.Entity.EntityState.Deleted;  
  5.     context.Entry<Employee>(employee).State = System.Data.Entity.EntityState.Deleted;  
  6.  
  7.     Console.WriteLine("Delete from details table");  
  8.   
  9.     EmployeeDetails employeeDetails = context.EmployeeDetails.Include("Employee").Where(p => p.EmployeeId == 3).FirstOrDefault();  
  10.     context.Entry<Employee>(employeeDetails.Employee).State = System.Data.Entity.EntityState.Deleted;  
  11.     context.Entry<EmployeeDetails>(employeeDetails).State = System.Data.Entity.EntityState.Deleted;  
  12.     context.SaveChanges();  
  13. }  
Output
 
Delete from details table
 

Conclusion

 
The main advantage of Table splitting is that our entity model is very simple and straightforward and contains only the logically related fields. Table splitting can help us to improve our performance. It might help us in a business scenario where we do not access some of the columns of the database table as frequently as others or we might have some columns that have confidential information and we do not want to create a separate table for these columns and using table splitting we can keep these columns in a different entity.


Similar Articles