Entity Splitting Strategies

In this lesson, you will learn how to map a single C# entity class to two separate database tables to improve performance and data security.

The Concept of Entity Splitting

Sometimes, an entity contains a large amount of data—such as a long description, a large BLOB, or sensitive personal information—that isn't needed for every query. By default, Entity Framework Core maps an entity to one table. Entity Splitting allows you to break that entity into two tables while keeping the object model unified in your code.

This is particularly useful when you want to avoid loading "heavy" columns (like large text blocks) during standard list views, effectively separating "frequently accessed data" from "rarely accessed data" at the storage level.

Implementation

To map one entity to two tables, you use the ToTable method within your OnModelCreating override. You must ensure both tables share the same Primary Key.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(entity =>
    {
        // Map common properties to the main table
        entity.ToTable("Products");
        
        // Map secondary properties to the extended table
        entity.Property(p => p.ExtendedDescription).ToTable("ProductDetails");
        
        // Define the split: Primary Key must be shared
        entity.HasKey(p => p.Id);
    });
}

When you query for a Product, EF Core automatically performs an INNER JOIN between Products and ProductDetails to reconstruct the entity.

When to Use This

• Separation of Concerns: Keep core data (like Name and Price) in one table and descriptive/administrative metadata in another.
• Performance: If you frequently query the "Core" table but rarely need the "Details," splitting the tables can improve performance because the database engine doesn't have to scan wide rows containing large, unused columns.
• Security: You can restrict database access permissions for the secondary table to specific roles, even though the application uses a single class to represent the full object.

Key Takeaway: Entity Splitting lets you keep your domain model clean while mapping a single entity to multiple database tables to optimize performance or handle sensitive data storage.