Entity Framework and Table per Type: You’ve been warned!!!

by Admin 7. April 2012 16:23

Recently I was tasked with writing an application that uses a decorator pattern for its data model.  Since the model I worked on is proprietary I will use a simple “Contact” Data model to mimic this pattern.  In my “Contact” model Contact is the main table while Person, Employee, ThirdParty and Customer are the decorator tables.  The best possible choice for the implementation (or so I thought) of this model in Entity Framework (my preferred ORM) is a pattern called Table per Type.  

This seemed like the magic bullet, but being a developer I am always skeptical of magic bullets, so I did some performance testing. I noticed performance degraded exponentially for each decorator table I added to my model.  So I took a look at the generated SQL; that is when the magic bullet went through my conceptual model and shattered my design.  Ok that was a bit over dramatic.

Here is the crux of the problem:

A one table model works just fine.  I started with the Contact table.  The Lambda Expression used to select a list of items from the “Contact” table is simply:

IEnumerable<Contact> contactList = this._context.Contacts.ToList();

Which generates the following SQL:

SELECT 
[Extent1].[ContactId] AS [ContactId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[ContactType] AS [ContactType]
FROM [dbo].[Contact] AS [Extent1]

Pretty simple, but when I add one table pictured below watch what happens to the SQL:

 

To use table per type you need to adjust some properties.  First, change the properties of the “Person” entity so that its bas type is “Contact”:

Second remove the ContactId property from the “Person” entity.  Now the “Person” decorator table is fully realized in the object model.  The model is now:

 

But look at what happens to the generated SQL (remember I changed nothing in the code):

SELECT 
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN '0X' ELSE '0X0X' END AS [C1], 
[Extent1].[ContactId] AS [ContactId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[ContactType] AS [ContactType], 
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN CAST(NULL AS datetime2) ELSE [Project1].[Birthday] END AS [C2], 
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) THEN CAST(NULL AS varchar(1)) ELSE [Project1].[Title] END AS [C3]
FROM  [dbo].[Contact] AS [Extent1]
LEFT OUTER JOIN  (SELECT 
[Extent2].[ContactId] AS [ContactId], 
[Extent2].[Birthday] AS [Birthday], 
[Extent2].[Title] AS [Title], 
cast(1 as bit) AS [C1]
FROM [dbo].[Person] AS [Extent2] ) AS [Project1] ON [Extent1].[ContactId] = [Project1].[ContactId]

Ok a bit more overhead but still not horrible, watch what happens when I add all of the items to the object model:

 

Associated SQL:

SELECT 

CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN '0X' WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN '0X0X' WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN '0X1X' WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN '0X2X' ELSE '0X3X' END AS [C1], 
[Extent1].[ContactId] AS [ContactId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[ContactType] AS [ContactType], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS datetime2) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN [UnionAll3].[C2] WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS datetime2) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS datetime2) END AS [C2], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN [UnionAll3].[C3] WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) END AS [C3], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN [UnionAll3].[C4] WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) END AS [C4], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS tinyint) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS tinyint) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN [UnionAll3].[C5] WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS tinyint) END AS [C5], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN [UnionAll3].[C6] END AS [C6], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN [UnionAll3].[C7] END AS [C7], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [UnionAll3].[C8] END AS [C8], 
CASE WHEN (( NOT (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL))) AND ( NOT (([UnionAll3].[C11] = 1) AND ([UnionAll3].[C11] IS NOT NULL))) AND ( NOT (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL))) AND ( NOT (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C10] = 1) AND ([UnionAll3].[C10] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C13] = 1) AND ([UnionAll3].[C13] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C12] = 1) AND ([UnionAll3].[C12] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [UnionAll3].[C9] END AS [C9]
FROM  [dbo].[Contact] AS [Extent1]
LEFT OUTER JOIN  (SELECT 
[UnionAll2].[C1] AS [C1], 
[UnionAll2].[C2] AS [C2], 
[UnionAll2].[C3] AS [C3], 
[UnionAll2].[C4] AS [C4], 
[UnionAll2].[C5] AS [C5], 
[UnionAll2].[C6] AS [C6], 
[UnionAll2].[C7] AS [C7], 
[UnionAll2].[C8] AS [C8], 
[UnionAll2].[C9] AS [C9], 
[UnionAll2].[C10] AS [C10], 
[UnionAll2].[C11] AS [C11], 
[UnionAll2].[C12] AS [C12], 
[UnionAll2].[C13] AS [C13]
FROM  (SELECT 
[UnionAll1].[ContactId] AS [C1], 
[UnionAll1].[Birthday] AS [C2], 
[UnionAll1].[Title] AS [C3], 
[UnionAll1].[C1] AS [C4], 
[UnionAll1].[C2] AS [C5], 
[UnionAll1].[C3] AS [C6], 
[UnionAll1].[C4] AS [C7], 
[UnionAll1].[C5] AS [C8], 
[UnionAll1].[C6] AS [C9], 
[UnionAll1].[C7] AS [C10], 
[UnionAll1].[C8] AS [C11], 
[UnionAll1].[C9] AS [C12], 
[UnionAll1].[C10] AS [C13]
FROM  (SELECT 
[Extent2].[ContactId] AS [ContactId], 
[Extent2].[Birthday] AS [Birthday], 
[Extent2].[Title] AS [Title], 
CAST(NULL AS varchar(1)) AS [C1], 
CAST(NULL AS tinyint) AS [C2], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS varchar(1)) AS [C4], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
cast(1 as bit) AS [C7], 
cast(0 as bit) AS [C8], 
cast(0 as bit) AS [C9], 
cast(0 as bit) AS [C10]
FROM [dbo].[Person] AS [Extent2]
UNION ALL
SELECT 
[Extent3].[ContactId] AS [ContactId], 
CAST(NULL AS datetime2) AS [C1], 
CAST(NULL AS varchar(1)) AS [C2], 
[Extent3].[CustomerId] AS [CustomerId], 
[Extent3].[CustomerRating] AS [CustomerRating], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS varchar(1)) AS [C4], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
cast(0 as bit) AS [C7], 
cast(0 as bit) AS [C8], 
cast(0 as bit) AS [C9], 
cast(1 as bit) AS [C10]
FROM [dbo].[Customer] AS [Extent3]) AS [UnionAll1]
UNION ALL
SELECT 
[Extent4].[ContactId] AS [ContactId], 
CAST(NULL AS datetime2) AS [C1], 
CAST(NULL AS varchar(1)) AS [C2], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS tinyint) AS [C4], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
[Extent4].[CompanyName] AS [CompanyName], 
[Extent4].[Location] AS [Location], 
cast(0 as bit) AS [C7], 
cast(1 as bit) AS [C8], 
cast(0 as bit) AS [C9], 
cast(0 as bit) AS [C10]
FROM [dbo].[ThirdParty] AS [Extent4]) AS [UnionAll2]
UNION ALL
SELECT 
[Extent5].[ContactId] AS [ContactId], 
CAST(NULL AS datetime2) AS [C1], 
CAST(NULL AS varchar(1)) AS [C2], 
CAST(NULL AS varchar(1)) AS [C3], 
CAST(NULL AS tinyint) AS [C4], 
[Extent5].[EmployeeId] AS [EmployeeId], 
[Extent5].[SSN] AS [SSN], 
CAST(NULL AS varchar(1)) AS [C5], 
CAST(NULL AS varchar(1)) AS [C6], 
cast(0 as bit) AS [C7], 
cast(0 as bit) AS [C8], 
cast(1 as bit) AS [C9], 
cast(0 as bit) AS [C10]
FROM [dbo].[Employee] AS [Extent5]) AS [UnionAll3] ON [Extent1].[ContactId] = [UnionAll3].[C1]

Now imagine this on a larger scale decorator implementation.  This issue is supposed to be addressed in the entity framework 5.0.

Hopefully this will prevent others from running into the same refactoring nightmare I ran into.

 

Tags: ,

Entity Framework | SQL Server

Calendar

<<  August 2017  >>
MoTuWeThFrSaSu
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910

View posts in large calendar

Page List

RecentComments

None