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 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.



In Code First we will have the following two entities and DbContext configuration to accomplish this scenario.

[Table("EmployeeMaster")]
public partial class Employee
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int EmployeeId { get; set; }

    [StringLength(10)]
    public string Code { get; set; }

    [StringLength(50)]
    public string Name { get; set; }
    [ForeignKey("EmployeeId")]
    public virtual EmployeeDetails Details { get; set; }

}

[Table("EmployeeMaster")]
public partial class EmployeeDetails
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int EmployeeId { get; set; }
    [StringLength(25)]
    public string PhoneNumber { get; set; }
    [StringLength(255)]
    public string EmailAddress { get; set; }

    public virtual Employee Employee { get; set; }
}

DbContext Class

public partial class EntityModel : DbContext
{
    public EntityModel() : base("name=EntityModel")
    {
        Database.Log = Console.WriteLine;
    }

    public virtual DbSet<Employee> Employees { get; set; }
    public virtual DbSet<EmployeeDetails> EmployeeDetails { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
         modelBuilder.Entity<Employee>()
            .HasRequired(e => e.Details)
            .WithRequiredDependent(e => e.Employee);
    }
}

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
   

   using (EntityModel context = new EntityModel())
   {
      var employees = context.Employees.ToList();
   }

   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
   

   using (EntityModel context = new EntityModel())
   {
      var employees = context.EmployeeDetails.ToList();
   }

   Output
   
   
   
   
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
   

   using (EntityModel context = new EntityModel())
   {
      var employees = context.Employees.Include("Details").ToList();
   }

   Output
   
   

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

using (EntityModel context = new EntityModel())
{
    Employee employee = new Employee();
    employee.Code = "A0003";
    employee.Name = "Rakesh Trivedi";
    employee.Details = new EmployeeDetails { EmailAddress = "[email protected]", PhoneNumber = "895648552" };
    context.Employees.Add(employee);
    context.SaveChanges();
}

Output



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

using (EntityModel context = new EntityModel())
{
    Employee employee = context.Employees.Include("Details").Where(p => p.EmployeeId == 1).FirstOrDefault();
    employee.Details.PhoneNumber = "5689234556";
    context.SaveChanges();
}

Output



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 to be considered, that we must set the entity state to “deleted” for both of the entities.

Example Code

using (EntityModel context = new EntityModel())
{
    Employee employee = context.Employees.Include("Details").Where(p => p.EmployeeId == 2).FirstOrDefault();
    context.Entry<EmployeeDetails>(employee.Details).State = System.Data.Entity.EntityState.Deleted;
    context.Entry<Employee>(employee).State = System.Data.Entity.EntityState.Deleted;



    Console.WriteLine("Delete from details table");

    EmployeeDetails employeeDetails = context.EmployeeDetails.Include("Employee").Where(p => p.EmployeeId == 3).FirstOrDefault();
    context.Entry<Employee>(employeeDetails.Employee).State = System.Data.Entity.EntityState.Deleted;
    context.Entry<EmployeeDetails>(employeeDetails).State = System.Data.Entity.EntityState.Deleted;
    context.SaveChanges();
}

Output



Conclusion

The main advantage of Table splitting is that our entity model is very simple and straightforward and contains only the logical related fields. Table splitting can help us to improve the 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.