Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

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:

image

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.

Comments

Jamie said:

Why not make the main table have an identity and the child tables have both a primary and foreign key that is the id of the parent table?

# August 30, 2013 8:25 AM

Ricardo Peres said:

Jamie:

Because there is no connection between the tables, that is, no foreign key! That's what Concrete Table Inheritance is about, if you want to have foreign keys, there is Class Table Inheritance (see weblogs.asp.net/.../entity-framework-code-first-inheritance.aspx)

# August 30, 2013 9:56 AM