EF Linq to entities query generating UNION for TPC CTP5 code-first entity

Question

I have the following entities:

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

public class HappyUser : User
{
    public bool IsHappy { get; set; }
}

I am configuring the entities using Table Per Concrete Type (TPC) in order to generate a User table and a HappyUser table. I want the HappyUser table to include the properties of the User class, and I don't want any relationship between the two tables.

I configure the entities as follows:

public class UserTest : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<HappyUser> HappyUsers { get; set; }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<HappyUser>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("HappyUser");
        });
    }
}

The tables are generated correctly, but when I query the tables, EF generates a UNION on the User and HappyUser table. The query is as follows:

        UserTest db = new UserTest();

        var users = from u in db.Users
                    select u;

        var happyUsers = from u in db.Users.OfType<HappyUser>()
                         select u;

The SQL for the Users generates a UNION. This is not what I expect or want. I'd like to simply retrieve the rows from the Users table.

SELECT 
CASE WHEN ([UnionAll1].[C2] = 1) THEN '0X' ELSE '0X0X' END AS [C1], 
[UnionAll1].[Id] AS [C2], 
[UnionAll1].[Name] AS [C3], 
CASE WHEN ([UnionAll1].[C2] = 1) THEN CAST(NULL AS bit) ELSE [UnionAll1].[C1] END AS [C4]
FROM  (SELECT 
 [Extent1].[Id] AS [Id], 
 [Extent1].[Name] AS [Name], 
 CAST(NULL AS bit) AS [C1], 
 cast(1 as bit) AS [C2]
 FROM [dbo].[User] AS [Extent1]
UNION ALL
 SELECT 
 [Extent2].[Id] AS [Id], 
 [Extent2].[Name] AS [Name], 
 [Extent2].[IsHappy] AS [IsHappy], 
 cast(0 as bit) AS [C1]
 FROM [dbo].[HappyUser] AS [Extent2]) AS [UnionAll1]

The SQL for the HappyUsers works as expected.

SELECT 
'0X0X' AS [C1], 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[IsHappy] AS [IsHappy]
FROM [dbo].[HappyUser] AS [Extent1]

Any ideas what I am doing wrong? Or is this a defect in EF?

Answer 1

HappyUsers are Users. Hence, db.Users should return both. The EF is correct, here.

However, the EF does have a limitation: There is no way to (in L2E, anyway) return results of only one type. Due to the Liskov Substitution Principal, you don't generally want to do this. But if you do, you can do it in ESQL, and there are (somewhat tedious) workarounds for L2E.

Bottom line: If you find yourself wanting to do this, rethink your design. Inheritance is probably not the right relationship in this case.

Answer 2

@Craig Stuntz is right, since both types are related, EF will keep that relation intact.

If you want to decouple both User types, then you could use an abstract base class.

public abstract class AbstractUser
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class User : AbstractUser
{
}

public class HappyUser : AbstractUser
{
    public bool IsHappy { get; set; }
}

EF will now treat both entities as seperate and there's no need to call MapInheritedProperties() anymore.

Your select statements would then look like:

var users = db.Users.Where(...);
var happyUsers = db.HappyUsers.Where(...);