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's first approach is known 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.

[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 the "Logging SQL" of Entity Framework.

Let us consider the four scenarios of selecting, inserting, updating, and deleting and check the behavior of the Entity Framework.

Select scenario

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

Output

Querying on EmployeeDetails Entity

When we query 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

Context

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

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

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

Insert entity scenario

Update entity scenario

When we do an update operation on the employee entity or employee detail, 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

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

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

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.