Table Splitting In Entity Framework Core 2.0

Introduction
 
One of the major benefits of the Entity Framework is that our entity model does not need to match our database model. With Table Splitting, it is possible to map multiple entities to the single database table. It means that we can split our database table to multiple entities but primary column(s) will be shared between entities.
 
Example
 
I have the below table in database and in entity model. I have two entities called Employee and EmployeeDetails. The Employee entity is used to store some basic information of employee and EmployeeDetails entity is used to store some additional information of employee.
  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. SET ANSI_PADDING ON  
  6. GO  
  7. CREATE TABLE [dbo].[Employee](  
  8.     [Id] [intNOT NULL,  
  9.     [Name] [varchar](50) NULL,  
  10.     [IsDeleted] [bitNULL,  
  11.     [EmailAddress] [varchar](255) NULL,  
  12.     [MobileNumber] [varchar](13) NULL,  
  13.     [AlternateNumber] [varchar](13) NULL,  
  14.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  15. (  
  16.     [Id] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  18. ON [PRIMARY]  
  19.   
  20. GO  
  21. SET ANSI_PADDING OFF  
  22. GO  
  23. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted], [EmailAddress], [MobileNumber], [AlternateNumber]) VALUES (1, N'Jignesh', 0, N'[email protected]', N'9898985864'NULL)  
  24. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted], [EmailAddress], [MobileNumber], [AlternateNumber]) VALUES (2, N'Rakesh', 0, N'[email protected]', N'9879456987'NULL)  
  25. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted], [EmailAddress], [MobileNumber], [AlternateNumber]) VALUES (3, N'Tejas', 0, N'[email protected]', N'9879458632'NULL)  
  26. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted], [EmailAddress], [MobileNumber], [AlternateNumber]) VALUES (4, N'Rajesh', 1, N'[email protected]', N'9426897523'NULL)  
 
 
Following is entities definition:
  1. using System.ComponentModel.DataAnnotations;  
  2. using System.ComponentModel.DataAnnotations.Schema;  
  3.   
  4. namespace TableSplittingExample.Model  
  5. {  
  6.     [Table("Employee")]  
  7.     public class Employee  
  8.     {  
  9.         [Key]  
  10.         public int Id { getset; }  
  11.         public string Name { getset; }  
  12.         public bool IsDeleted { getset; }  
  13.         [ForeignKey("Id")]  
  14.         public EmployeeDetail EmployeeDetail { getset; }  
  15.     }  
  16.   
  17.     [Table("Employee")]  
  18.     public class EmployeeDetail  
  19.     {  
  20.         [Key]  
  21.         public int Id { getset; }  
  22.         public string EmailAddress { getset; }  
  23.         public string MobileNumber { getset; }  
  24.         public string AlternateNumber { getset; }  
  25.         public Employee Employee { getset; }  
  26.     }  
  27. }  
It must be a configured relationship between all the entity types that are sharing the table. We can configure the relation between entities using HasOne (EntityTypeBuilder class) and WithOne method (ReferenceNavigationBuilder class).
  1. protected override void OnModelCreating(ModelBuilder modelBuilder)  
  2. {  
  3.     modelBuilder.Entity<Employee>().HasOne(p => p.EmployeeDetail).WithOne(p => p.Employee).HasForeignKey<Employee>(p => p.Id);  
  4.     modelBuilder.Entity<EmployeeDetail>().HasOne(p => p.Employee).WithOne(p => p.EmployeeDetail).HasForeignKey<EmployeeDetail>(p => p.Id);  
  5.     modelBuilder.Entity<Employee>().ToTable("Employee");  
  6.   
  7.     base.OnModelCreating(modelBuilder);  
  8. }  
When we query the employee and employee details entity, Entity Framework will automatically generate the query. To analyze the query, I just added console logging provider to DbContextOptionsBuilder.
  1. protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
  2. {  
  3.     optionsBuilder.UseSqlServer(@"Server=(local);Database=Test;user Id=sa; password=Passwd@12;");  
  4.   
  5.     LoggerFactory loggerFactory = new LoggerFactory();  
  6.     loggerFactory.AddConsole();  
  7.     optionsBuilder.UseLoggerFactory(loggerFactory);  
  8. }  
Let's consider the CRUD (Create, Read, Update and Delete) scenario and check what type of query is generated by entity framework.
 
Select scenario
 
Querying Employee Entity

When we query employee entity, it will retrieve only columns related to the Employee entity (Id, Name and IsDeleted).
  1. using (EntityModelContext context = new EntityModelContext())  
  2. {  
  3.     Console.WriteLine("------------- Employee Entity -------------");  
  4.     var data1 = context.Employees.ToList();  
  5.     Console.ReadLine();  
  6. }  

 
Querying on EmployeeDetails Entity
 
When we query the employee details entity, it will retrieve the columns related to the EmployeeDetail entity (Id, EmailAddress, MobileNumber, AlternateNumber).
  1. using (EntityModelContext context = new EntityModelContext())  
  2. {  
  3.     Console.WriteLine("------------- Employee Entity -------------");  
  4.     var data2 = context.EmployeeDetails.ToList();  
  5.     Console.ReadLine();  
  6. }  
 
Both the entities together
 
When we query both entities (Employee and EmployeeDetail), it will retrieve the columns related to both entities.
  1. using (EntityModelContext context = new EntityModelContext())  
  2. {  
  3.     Console.WriteLine("------------- Employee Entity -------------");  
  4.     var data3 = context.Employees.Include(p => p.EmployeeDetail).ToList();  
  5.     Console.ReadLine();  
  6. }  
 
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.
  1. using (EntityModelContext context = new EntityModelContext())  
  2. {  
  3.     Employee emp = new Employee  
  4.     {  
  5.         Id = 5,  
  6.         Name = "Vishal",  
  7.         IsDeleted = false,  
  8.         EmployeeDetail = new EmployeeDetail { EmailAddress = "[email protected]", MobileNumber = "895648552" }  
  9.     };  
  10.   
  11.     context.Add(emp);  
  12.     context.SaveChanges();  
  13. }  
 
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.
  1. using (EntityModelContext context = new EntityModelContext())  
  2. {  
  3.     Employee emp = context.Employees.Include(p => p.EmployeeDetail).Where(p => p.Id == 5).FirstOrDefault();  
  4.     emp.Name = "Meera";  
  5.     emp.EmployeeDetail.EmailAddress = "[email protected]";  
  6.     context.SaveChanges();  
  7. }  
 
Delete entity scenario
 
When we do a delete operation on an employee or employee details entity, the Entity Framework generates a delete query.
  1. using (EntityModelContext context = new EntityModelContext())  
  2. {  
  3.     Employee emp = context.Employees.Include(p => p.EmployeeDetail).Where(p => p.Id == 5).FirstOrDefault();  
  4.     context.Entry<Employee>(emp).State = EntityState.Deleted;  
  5.     context.SaveChanges();  
  6. }  
 
Summary

Table splitting is one of most important features of Entity framework core that was introduced in EF core 2.0. This feature is also present in an earlier version of entity framework. The 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 the performance of select query.

You can view or download the source code from the following GitHub link.
https://github.com/jignesht24/EntityFrameworkCore/tree/master/Table%20Splitting


Similar Articles