Entity Framework Pitfalls: Concrete Table Inheritance and Identity Keys
When using the Concrete Table Inheritance / Table Per Concrete Type pattern for mapping entity inheritances, you cannot use IDENTITYs as primary keys. It is easy to understand why: because each entity of a concrete type is stored in its own table, and if these tables would be using IDENTITYs for generating the primary key, if we would issue a query on their base class looking for a record by its primary key, Entity Framework would generate lots of UNIONs, one for each table, where only one could possibly return a record.
For example, say we have this model:
A query such as:
1: var tool = ctx.Tools.Where(x => x.ToolId == new Guid("32C1BACF-5814-48DD-95E7-31855C7849CB")).SingleOrDefault();
Would generate the following SQL:
1: SELECT
2: CASE WHEN ([UnionAll2].[C6] = 1) THEN '0X0X' WHEN ([UnionAll2].[C7] = 1) THEN '0X1X' ELSE '0X2X' END AS [C1],
3: [UnionAll2].[C1] AS [C2],
4: [UnionAll2].[C2] AS [C3],
5: CASE WHEN ([UnionAll2].[C6] = 1) THEN [UnionAll2].[C3] WHEN ([UnionAll2].[C7] = 1) THEN CAST(NULL AS varchar(1)) END AS [C4],
6: CASE WHEN ([UnionAll2].[C6] = 1) THEN CAST(NULL AS bit) WHEN ([UnionAll2].[C7] = 1) THEN [UnionAll2].[C4] END AS [C5],
7: CASE WHEN ([UnionAll2].[C6] = 1) THEN CAST(NULL AS bit) WHEN ([UnionAll2].[C7] = 1) THEN CAST(NULL AS bit) ELSE [UnionAll2].[C5] END AS [C6]
8: FROM (SELECT
9: [UnionAll1].[ToolId] AS [C1],
10: [UnionAll1].[Name] AS [C2],
11: [UnionAll1].[C1] AS [C3],
12: [UnionAll1].[C2] AS [C4],
13: [UnionAll1].[Automated] AS [C5],
14: [UnionAll1].[C3] AS [C6],
15: [UnionAll1].[C4] AS [C7]
16: FROM (SELECT
17: [Extent1].[ToolId] AS [ToolId],
18: [Extent1].[Name] AS [Name],
19: CAST(NULL AS varchar(1)) AS [C1],
20: CAST(NULL AS bit) AS [C2],
21: [Extent1].[Automated] AS [Automated],
22: cast(0 as bit) AS [C3],
23: cast(0 as bit) AS [C4]
24: FROM [dbo].[TestingTool] AS [Extent1]
25: WHERE cast('32c1bacf-5814-48dd-95e7-31855c7849cb' as uniqueidentifier) = [Extent1].[ToolId]
26: UNION ALL
27: SELECT
28: [Extent2].[ToolId] AS [ToolId],
29: [Extent2].[Name] AS [Name],
30: CAST(NULL AS varchar(1)) AS [C1],
31: [Extent2].[CompatibleWithProject] AS [CompatibleWithProject],
32: CAST(NULL AS bit) AS [C2],
33: cast(0 as bit) AS [C3],
34: cast(1 as bit) AS [C4]
35: FROM [dbo].[ManagementTool] AS [Extent2]
36: WHERE cast('32c1bacf-5814-48dd-95e7-31855c7849cb' as uniqueidentifier) = [Extent2].[ToolId]) AS [UnionAll1]
37: UNION ALL
38: SELECT
39: [Extent3].[ToolId] AS [ToolId],
40: [Extent3].[Name] AS [Name],
41: [Extent3].[Language] AS [Language],
42: CAST(NULL AS bit) AS [C1],
43: CAST(NULL AS bit) AS [C2],
44: cast(1 as bit) AS [C3],
45: cast(0 as bit) AS [C4]
46: FROM [dbo].[DevelopmentTool] AS [Extent3]
47: WHERE cast('32c1bacf-5814-48dd-95e7-31855c7849cb' as uniqueidentifier) = [Extent3].[ToolId]) AS [UnionAll2]
If all tables used IDENTITY, there was no way to make all primary keys unique to each table, at least, not in a easy way.
The solution is to use a different identifier generation algorithm, such as Guids:
1: public abstract class Tool
2: {
3: public Tool()
4: {
5: this.ToolId = Guid.NewGuid();
6: }
7:
8: public String Name
9: {
10: get;
11: set;
12: }
13:
14: public Guid ToolId
15: {
16: get;
17: set;
18: }
19: }
For a complete mapping solution, please read this post.