Entity Splitting in Entity Framework 6 (Code First Approach)

Introduction

 
Entity splitting gives us the ability to take an entity in our model and split this entity into multiple database tables. When we query an entity, Entity Framework will create a query that automatically joins the related physical tables for us. Earlier, the database first approach is known as Table Per Type (TPT). In this article, I am explaining it with the Code First Approach.
 
Table Per Type is an inheritance type and uses a separate table in the database to maintain the data and uses a single entity in the Entity Framework. In other words, entity splitting involves mapping a single entity in a conceptual layer to multiple tables in a store (database) layer. Suppose we have a high normalized database structure in which the database has two tables that store Employee basic information (Code and Name) and employee additional information (email address and phone number).
 
emloyee detail
 
Consider the following entity in the Code First model.

 

  1. public partial class Employee  
  2. {  
  3. // These fields come from the “Employee” table  
  4.    public int EmployeeId { getset; }  
  5.   
  6.    [StringLength(10)]  
  7.    public string Code { getset; }  
  8.   
  9.    [StringLength(50)]  
  10.    public string Name { getset; }  
  11.   
  12.    // These fields come from the “EmployeeDetails” table  
  13.    [StringLength(25)]  
  14.    public string PhoneNumber { getset; }  
  15.   
  16.    [StringLength(255)]  
  17.    public string EmailAddress { getset; }  
  18. }  
The preceding model stores information about an employee in our system. The Employeeid, Code, and Name properties will be stored in the employee table and email address and phone number properties will be stored in the Employee details table. In the default configuration, Entity Framework generates five columns in a single table (employee). In the store (database) we have two tables to store this information, so we need to change this configuration by overriding the "OnModelCreating" method of the DbContext Class.
  1. public partial class Model : DbContext  
  2. {  
  3.    public Model() : base("name=EntityModel")  
  4.    {  
  5.       Database.Log = Console.WriteLine;  
  6.    }  
  7.    public virtual DbSet<Employee> Employees { getset; }  
  8.   
  9.    protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  10.    {  
  11.       modelBuilder.Entity<Employee>()  
  12.       .Map(map =>  
  13.       {  
  14.       map.Properties(p => new  
  15.       {  
  16.          p.EmployeeId,  
  17.          p.Name,  
  18.          p.Code  
  19.       });  
  20.       map.ToTable("Employee");  
  21.       })  
  22.       // Map to the Users table  
  23.       .Map(map =>  
  24.       {  
  25.       map.Properties(p => new  
  26.       {  
  27.          p.PhoneNumber,  
  28.          p.EmailAddress  
  29.       });  
  30.       map.ToTable("EmployeeDetails");  
  31.       });  
  32.    }  
  33. }  
As said earlier, when we query the employee entity, the Entity Framework automatically generates a join query between the two tables. To analyze the query, we just turn on "Logging SQL" of Entity Framework.
 
Let us consider the four scenarios: select, insert, update, and delete and check the behavior of the Entity Framework.
 
Select scenario
 
When we query on the employee entity, the Entity Framework generates a join query between the employee and employeedetails tables.
 
Example code:
  1. using (Model context = new Model())  
  2. {  
  3.    var Employee = context.Employees.Where(o => o.EmployeeId == 1).ToList();  
  4. }  
Output:
 
model context
 
Insert entity scenario
 
When we perform an insert operation on an employee entity, Entity Framework generates an insert query for both tables (employee and employeedetails).
 
Example code:
  1. using (Model context = new Model())  
  2. {  
  3.    Employee employee = new Employee();  
  4.    employee.Code = "A0001";  
  5.    employee.Name = "Jignesh Trivedi";  
  6.    employee.EmailAddress = "test@gmail.com";  
  7.    employee.PhoneNumber = "9865238955";  
  8.   
  9.    context.Employees.Add(employee);  
  10.    context.SaveChanges();  
  11. }  
Output:
 
 
Update entity scenario
 
When we do an update operation on an employee entity, Entity Framework generates an update query for the table that the column has been updated. In the following example, I have an update email address column, Entity Framework generates an update query only for the employee details table.
 
Example code:
  1. using (Model context = new Model())  
  2. {  
  3.    Employee employee = context.Employees.Where(p => p.EmployeeId == 1).FirstOrDefault();  
  4.    employee.EmailAddress = "test1.yahoo.co.in";  
  5.    context.SaveChanges();  
  6. }  
Output:
 
console output using model
Delete entity scenario
 
When we do a delete operation on an employee entity, Entity Framework generates a delete query for both tables (employee and employeedetails).
 
Example code:
  1. using (Model context = new Model())  
  2. {  
  3.    Employee employee = context.Employees.Where(p => p.EmployeeId == 1).FirstOrDefault();  
  4.    context.Employees.Remove(employee);  
  5.    context.SaveChanges();  
  6. }  
Output:
 
console output
 

Conclusion

 
The main advantage of Entity Splitting is that the SQL schema is normalized as we want and the entity looks very simple. In addition, model enhancement is very straightforward.