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.

user978139 · Accepted Answer · 2015-12-11T10:10:43.337

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.

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

1 Answers1