EF Mapping Properties of an Entity Type to Multiple Tables with TPH Inheritance

Question

I would like to use Mapping Properties of an Entity Type to Multiple Tables in the Database (Entity Splitting) whilst as the same time using Mapping the Table-Per-Hierarchy (TPH) Inheritance, therefore my model mapping code is 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");
    });

Based upon the following underlying database schema:

create table dbo.Person
(
    PersonId int not null identity(1,1) primary key,
    PersonType char(1) not null,
    Name varchar(50) not null
)

create table dbo.Customer
(
    PersonId int not null references dbo.Person (PersonId),
    CustomerNumber varchar(10) not null
)

However, when the EF tries to execute my query:

ctx.People.ToList();

The following exception message is thrown:

Invalid column name 'PersonType'.

Running a SQL profile it would appear that its trying to use a predicate on the field PersonType with value C on the dbo.Customer table, rather than on the dbo.Person table where my discriminator really is.

If I use one or the other feature, i.e. only the inheritance or only the additional table mapping then it works but then I forfeit some of my requirements.

Can what I'm doing be done with the EF Fluent API?

Thanks for your time.

Answer 1

This can be achieved by creating a view on all the table schemas involved in the mapping:

create view dbo.vw_PersonExtended
as

    select
        p.Name, p.PersonId, p.PersonType, c.CustomerNumber
    from
        dbo.Person p
        left join dbo.Customer c on c.PersonId=p.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 });
    });

This would fail on inserting new entities as the view has more than one base table, therefore you'd have to use an INSTEAD OF TRIGGER or map the insertion to a stored procedure with Fluent code as:

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

And insert stored procedure example as:

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

Obviously the drawback with this approach is all of the plumbing work involved with getting this working.