EntitySplitting: not so easy

EntitySplitting allows you to map an entity on two or more tables (see also my article on EDM).

However, there is a special case.

Imagine you have a table Employees: Id (PK), LastName, FirstName and a table Consultants: Id (PK and FK to Employees.Id).

Imagine you make software for consultants and only for them. It should be interesting to have only one entity type Consultant mapped on the Employees table and the Consultants table.

In this case, Context.Consultants returns all your employees (with the employees who aren’t consultants).

When you see the SQL generated query, Consultants table isn’t on the SELECT.

In fact, it’s because you will have the Id with Employees table, so the INNER JOIN is removed. After asking to Sahil from the ADO .Net Team, it’s the normal way.

I tried to map the Consultant entity on the Consultants table first and then on Employees table but the result is the same.

So in this case, you should use TPT.

Now imagine the following scenario: EntitySplitting with two tables with only one PK column.

In this case, we will have the content of the first mapped table.

Of course, there is no problem with INSERT which will do the insert on the two tables.


For information, this is the answer of Sahil:

In EF v1 the conceptual model is _not_ a view of the DB. It portrays subset of the states of the DB such that all states can be persisted loselessly. Your conceptual model + mappings build up such constraints on the shape and value of data (i.e. subset states of the store schema) that can exist in your conceptual model – and therefore in the store when data is inserted or updated through your EF model. A trivial example of this is having a condition on a column: seelct ID from EntitySet = select ID from Table where D=5.

But also consider another scenario; TPC splitting of a hierarchy (Type1, Type2) to Table1, Table2. PKs in Table1 will never overlap with PKs in table2.


For this to work we could  add additional predicates to the queries so that existing data in the DB is filtered appropriately to fit the shape.

Where clause in the first example, and ANti Semi join in the second. However, in larger models these conditions get quite expensive especially when there are JOINS and ASJs required across multiple tables just to retrieve rows from a one table.


So instead, EF follows the closed world-model, which essentially means all data is assumed to be in a shape that can exist by updating through the given EF mapping model. In the above example Table can never carry a row with d column other than 5 so there is no need to add that predicate. Table1 and Table2 will never overlap on the PK so there is no need to check for distinct values. In your model it is not possible to have a PK in T2 that would not exist in T1 so it is safe to ignore the Inner join provided you are not projecting any properties from T2.

In certain cases you store may carry data that is unfit for the conceptual model, in which case the bahavior is undefined. IN my TPC example if you had PKs that overlap between T1 and T2 you will get a runtime exception when you try to materialize those rows.

This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>