Generate Inherited DBSets in EF CodeFirst (Like Views)

Question

This is my first time to use code first approach. I have a table for Users, and users can be of two types of Normal User and Seller, in which seller inherits from users with some extra options (relations to other tables).

I was wondering if I could do something like:

public class Seller: User
{
    public virtual ICollection<WorkingDay> WorkingDays {get; set;}
    ....
} 

and then in ApplicationDbContext I add:

public DbSet<Seller> Sellers { get; set; }

Answer 1

Yes, you can use inheritance in Entity Framework. However, databases usually don't support inheritance. In databases inheritance is mocked. Several strategies are used to mock inheritance.

Suppose you have three classes:

public class MyBaseItem {...}
public class MyDerived1Item : MyBaseItem {...}
public class MyDerived2Item : MyBaseItem {...}

Strategy Table Per Concrete Class (TPC) I use this one most often if I don't need to create MyBaseItem objects, only MyDerived1Items or MyDerived2Items. To prevent creating a MyBaseItem objects by accident, I declare MyBaseItem abstract.

The database will have two tables: one with MyDerived1Items and one with MyDerived2Itemss. Both tables will have columns for all MyBaseItem properties.

Fluent API is used to inform the database builder that this strategy is used.

public MyDbContext : DbContext
{
    public DbSet<MyDerived1Item> MyDerived1Items {get; set;}
    public DbSet<MyDerived2Item> MyDerived2Items {get; set;}

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

        modelBuilder.Entity<MyDerived2Item>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("MyDerived2Items");
        });  
    }
}  

Adding / Changing / Deleting items in the database will always imply accessing one table, unless if you want to do something with the MyBaseItems properties of all derived items. In that case you'll have to cast the elements of each Derived table into MyBaseItem and concat all the derived tables, like this:

IQueryable<MyBaseItem> someBaseItems =
    dbContext.MyDerived1Items.Where(...).Cast<MyBaseItem>()
    .Concat(dbContext.MyDerived2Items.Where(...).Cast<MyBaseItem>();

It is easy to later add a MyDerived3Items table. The tables MyDerived1Items and MyDerived2Items won't have to change.

However, adding one property to MyBaseItem means adding a field to all tables of derived classes.

Strategy Table Per Type (TPT)

Create three tables. One table for every type. The tables with the derived items have a foreign key to the table with the base items.

If you create a MyDerived1Item, then the object will be put in two tables. The MyBaseItem properties of the base class will be in the MyBaseItems table, and the MyDerived1Item properties will be in the MyDerived1Items table, together with a foreign key to the corresponding item in the MyBaseItem.

I don't prefer this method, because it looks very much like a one-to-many relation. It would be possible in the database to have one base item with two derived items having the same foreign key, which is not what you intended. I'm not sure if you can prevent that such an object is added to the database.

Adding / Searching / Changing / Deleting items always implies the use of two tables, which makes these actions slower.

However, the advantage of this method is that you can create MyBaseItems. So if you need to do this, consider this strategy.

How to implement Table Per Type (TPT)

Table per Hierarchy (TPH)

This strategy is the default for entity framework.

The database will have one table that contains all properties of MyBaseItem, MyDerived1Item and MyDerived2Item. Not used columns are set to NULL. If MyDerived1Items are almost the same as MyDerived2Items, then there won't be much superfluous columns. However if these two classes are very different, then there will be a lot of columns set to null.

As you will have one table you will have one DbSet:

public class MyDbContext: DbContext
{
    public DbSet<MyBaseItem> MyItems { get; set; }
}

Because this is the default strategy, no fluent API is needed.

If you need a query that only uses the base properties:

IQueryable<MyBaseItem> baseItems = dbContext.MyItems
    .Where(...);

If you need only MyDerived1Items:

IQueryable<MyDerived1Item> myItems = dbContext.MyItems
    .OfType<MyDerived1Items>()
    .Where(...);

Consider this strategy if you expect a lot of queries with only base properties, as this only accesses one table. Reconsider it if your derived classes are very different.