Entity Framework - Splitting table to multiple entities WITH AN OVERLAPPING CONDITION

Question

Is there a way to preform the following mappings (using database-first approach):

Tables: (Defining tables with C#-like syntax for readability purposes only)

table MainItems
{
    column PK not-null unique int MainItemKey;
    column string Name;
    column string AspectAInfo;
    column string AspectBInfo;

    // 0 for A, 1 for B, 2 for both (Could be replaced with 2 boolean columns)
    column not-null int AspectABOrBoth;
}

table AspectAMoreInfo
{
    column PK not-null unique in AspectAMoreInfoKey;
    column FK not-null int MainItemKey;
    column string PayLoadA;
}

table AspectBMoreInfo
{
    column PK not-null unique in AspectBMoreInfoKey;
    column FK not-null int MainItemKey;
    column double PayLoadB;
}

Entities:

// Map to MainItems table if column AspectABOrBoth is 0 or 2
class TypeAItem
{
    // Map to MainItemKey column
    int TypeAItemKey { get; set; }

    string Name { get; set; } // Map to Name column

    // Navigation property to AspectAMoreInfo rows
    List<TypeAMoreInfo> MoreInfo { get; set; }

    // Navigation property to MainItems row when AspectABOrBoth is 2
    TypeBItem OptionalInnerItemB { get; set; }
}

// Map to MainItems table if column AspectABOrBoth is 1 or 2
class TypeBItem
{
    // Map to MainItemKey column
    int TypeBItemKey { get; set; }

    string Name { get; set; } // Map to Name column

    // Navigation property to AspectBMoreInfo rows
    List<TypeBMoreInfo> MoreInfo { get; set; }
}

// Map to AspectAMoreInfo table
class TypeAMoreInfo
{
    // Map to AspectAMoreInfoKey column
    int TypeAMoreInfoKey { get; set; }

    // Navigation property to MainItems row when MainItems.AspectABOrBoth is 0 or 2
    TypeAItem Owner { get; set; }
}

// Map to AspectBMoreInfo table
class TypeBMoreInfo
{
    // Map to AspectBMoreInfoKey column
    int TypeBMoreInfoKey { get; set; }

    // Navigation property to MainItems row when MainItems.AspectABOrBoth is 1 or 2
    TypeBItem Owner { get; set; }
}

Possible directions I have considered but prefer not to take include:

1. Defining 2 views above MainItems table and mapping entities to them.
   (Could use base type with this, together with Table-Per-Concrete-Type.)

2. Adding 2 nullable FK columns to MainItems table that point to self (to same row) instead of AspectABOrBoth column
   (1 not-null if MainItem is AspectA, the other not-null if MainItem is AspectB.)
   (Could use table-splitting with this, based on new FK columns.)

Answer 1

Splitting table to multiple entities is possible when using:

• Table splitting - it requires that entity share only the key and each other property can be mapped only to single entity.
• TPH inheritance - it requires that base entity defines key and shared properties. Sub entities can contain another properties but properties cannot be shared among sub entities. Table must contain one or more special columns (dicriminators) which will define what type in inheritance hierarchy the record represents. MSL doesn't allow any complex expression for discriminator. The complex condition can be only created as logical AND of all conditions.

If I look at your tables it doesn't look like inheritance. TableAItem and TableBItem don't have any shared properties. The only shared item is probably key which makes rest of your design pretty hard because relation to both TableAMoreInfo and TableBMoreInfo will be created with MainItem (holder of the key) and not child items.

Views look more suitable to solve this but view is by default read only in EF unless you manually modify SSDL.