Advanced mapping scenarios: Entity Splitting and Table Splitting

What is entity splitting in the Entity Framework?

Entity splitting allows you to take a single entity in your model and split it into multiple physical database tables, and it is essentially the opposite of table splitting. When you query this entity in Entity Framework, it will create a query that automatically joins to the tables.

Entity splitting allows us 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).

Table Per Type is an inheritance type that 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. The database has two tables that store Employee basic information (Code and Name) and additional employee information (email address and phone number).

Select scenario

The Entity Framework generates a join query between the employee and employeedetails tables when we query on the employee entity.

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

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. I have an update email address column; Entity Framework generates an update query only for the employee details table.

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

Table Splitting

To use table splitting, the entity types need to be mapped to the same table, have the primary keys mapped to the same columns and have at least one relationship configured between the primary key of one entity type and another in the same table.

Optional dependent entity

If all of the columns used by a dependent entity are NULL in the database, then no instance for it will be created when queried. This allows modelling an optional dependent entity, where the relationship property on the principal would be null. This would also happen if all of the dependent's properties are optional and set to null, which might not be expected.

Concurrency tokens

If any of the entity types sharing a table has a concurrency token, it must be included in all other entity types. This is necessary to avoid a stale concurrency token value when only one of the entities mapped to the same table is updated.