[SOLVED] EF Core view derived from table unexpected results

EF Core view derived from table unexpected results

I have a database view, based on a table with an additional column.

My classes first looked like this:

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

public class PersonView
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    public string Additional { get; set; }
}

In my context, I've added them like this:

modelBuilder.Entity<Person>().ToTable("Person", "dbo");
modelBuilder.Entity<PersonView>().ToView("PersonView", "dbo");

This all worked like expected, I can query the Person table or the ViewPerson view like

ctx.Set<Person>().Where ....
ctx.Set<PersonView>().Where ....

Now I tried to derive the class for the view from the table, only extending it with the additional properties. Now my classes look like this:

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

public class PersonView : Person
{
    public string Additional { get; set; }
}

From here, I am struggling around, cause leaving the context as is, the query to ctx.Set<Person>().Where(..) suddenly returns a mix of Person and ViewPerson types.

So I dove into EF Core and handling hierarchy, reading about TPH, TPT and TPC and learned that TPH is the default used by EF Core.

I think, that is not what I want it to be, so I changed the strategy in my context like this:

modelBuilder.Entity<Person>().UseTpcMappingStrategy().ToTable("Person", "dbo");
modelBuilder.Entity<PersonView>().ToView("PersonView", "dbo");

This indeed makes my query returning only objects of type Person if I query the table, but unfortunately the records are now doubled up.

Can anyone bring some light into this hierarchy for me? The database already exists and all I want is to query the table OR the view, and don't want any interaction between them but using the same base class for the table and the view.

Is this not possible with a derived class, and I have the classes for the table and the view to define separately as I had it at the beginning (and this was working).

Thanks for any hints or links to tips!

As Gert Arnold noted I tried the following:

public class PersonBase
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Person : PersonBase {}

public class PersonView : PersonBase
{
    public string Additional { get; set; }
}

I'm leaving my context untouched:

modelBuilder.Entity<Person>().ToTable("Person", "dbo");
modelBuilder.Entity<PersonView>().ToView("PersonView", "dbo");

Seems to work, I wonder why and how? :-)

Solution

Putting a comment into an answer. The problem is that EF knows about the class inheritance (because it knows both Person and PersonView) and therefore automatically infers an inheritance mapping, which is TPH by default. However, in this case you don't want EF to map any inheritance.

The solution is to create a base class, for example PersonBase, and derive Person and PersonView from it, without mapping PersonBase itself.