EF Mapping with Discriminator Column and Concrete Tables for Derived Types

A problem I recently ran into with Entity Framework 6 was where I wanted to use two features at the same time, specifically the Table-Per-Hierarchy mapping and Table Splitting. However, when using both of then at the same time it caused a conflict with the framework which it was unable to handle.

The following database schema was used for mapping to my domain model where I wanted the Person.PersonType column as a discriminator for my derived types.



My domain model was as follows (PersonType property has been omitted as discriminators cannot be mapped to properties):

class Person
{
 public int PersonId { get; set; }
 public string Name { get; set; }
}

class Customer : Person
{
 public string CustomerNumber { get; set; }}

class Operator : Person
{
 public string OperatorNumber { get; set; }
}

The model mapping I had in place was as follows:

  modelBuilder
    .Entity<Person>()
    .HasKey(n => n.PersonId)
    .Map(map =>
    {
        map.Properties(p => new { p.Name });
        map.ToTable("dbo.Person");
    })
    .Map<Customer>(map =>
    {
        map.Requires("PersonType").HasValue("C");
        map.Properties(p => new { p.CustomerNumber });
        map.ToTable("dbo.Customer");
    });

Which caused the following SQL exception to be thrown:

EntityCommandExecutionException
An error occurred while executing the command definition. See the inner exception for details
Invalid column name 'PersonType'.Invalid column name 'PersonType'.

The SQL which was generated by the entity framework was as follows:

SELECT 
    CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN '0X' WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN '0X0X' ELSE '0X1X' END AS [C1], 
    [Extent1].[PersonId] AS [PersonId], 
    [Extent1].[Name] AS [Name], 
    CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[CustomerNumber] END AS [C2], 
    CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [Project2].[OperatorNumber] END AS [C3]
    FROM   [dbo].[Person] AS [Extent1]
    LEFT OUTER JOIN  (SELECT 
        [Extent2].[PersonId] AS [PersonId], 
        [Extent2].[CustomerNumber] AS [CustomerNumber], 
        cast(1 as bit) AS [C1]
        FROM [dbo].[Customer] AS [Extent2]
        WHERE [Extent2].[PersonType] = N'C' ) AS [Project1] ON [Extent1].[PersonId] = [Project1].[PersonId]
    LEFT OUTER JOIN  (SELECT 
        [Extent3].[PersonId] AS [PersonId], 
        [Extent3].[OperatorNumber] AS [OperatorNumber], 
        cast(1 as bit) AS [C1]
        FROM [dbo].[Operator] AS [Extent3]
        WHERE [Extent3].[PersonType] = N'O' ) AS [Project2] ON [Extent1].[PersonId] = [Project2].[PersonId]

As you can see from the generated SQL it is trying to use the discriminator column on our satellite tables (dbo.Operator and dbo.Customer) rather than on our base table (dbo.Person).  As of yet I haven’t found a way to change the way in which the entity framework mapping works for the discriminator column to achieve both TPH mappings and Table Splitting.  To get around this problem I had to create a view on top of all of the table schemas involved in the hierarchy mapping:

create view dbo.vw_PersonExtended
as

    select
        p.Name, p.PersonId, p.PersonType, c.CustomerNumber, o.OperatorNumber
    from
        dbo.Person p
        left join dbo.Customer c on c.PersonId=p.PersonId
  left join dbo.Operator o on c.PersonId=o.PersonId

And mapping this view to the base class type Person and removing the derived class table mapping as follows:

   modelBuilder
    .Entity<Person>()
    .HasKey(n => n.PersonId)
    .Map(map =>
    {
        map.Properties(p => new { p.Name });
        map.ToTable("dbo.vw_PersonExtended");
    })
    .Map<Customer>(map =>
    {
        map.Requires("PersonType").HasValue("C");
        map.Properties(p => new { p.CustomerNumber });
    });

The derived types Person and Operator are no longer mapped to their individual concrete tables, but instead their values come from the mapping in the view.

Additionally, we have to create either an INSTEAD OF INSERT trigger on top of this view or create a stored procedure to allow our DB context to support persisting new entities.

The stored procedure would be:

create procedure dbo.usp_InsertCustomer
    @Name varchar(50),
    @CustomerNumber varchar(50)
as
begin

        set nocount on
        declare @id int

        insert into dbo.Person (Name, PersonType)
        values (@Name, 'C')
        set @id = scope_identity()

        insert into dbo.Customer (PersonId, CustomerNumber)
        values (@id, @CustomerNumber)

        select @id as PersonId

end

And mapped as follows:

modelBuilder
        .Entity<Customer>()
        .MapToStoredProcedures(map => map.Insert(i => i.HasName("usp_InsertCustomer")));

The obvious drawback to this approach is that you have to create views and stored procedures on the database side when you want to implement the discriminator hierarchy pattern in Entity Framework as well as the plumbing work in the database context mapping. This however shouldn't impede the performance of the implementation, it'll just make the implementation more time consuming.