Derived Types in Entity Framework

Question

I have a Person Class and Inventory can be two types: Sales and CustomerService.

Sales and CustomerService have their unique properties and Peron holds the common properties. I want to be able to query

So, when creating all three classes how do i create EF relation between them? OR is there a better way to think about the division of classes?

I don't want to have Person as Abstract class because most of the time i would want to query for the common properties.

Daft

I don't want to have Person as Abstract class because most of the time i would want to query for the common properties. This assumption is not correct. Abstract classes cannot be instantiated as object; but you can still use them as a downcast type or for filtering. You would be able to query for Person-specific properties regardless of whether Person is abstract or not. – Arliearliene

For clarity: when I say downcast type I mean AbstractClass myAbstract = new DerivedClass();. The only thing that an abstract class prevents is doing new AbstractClass(), everything else still works as usual. – Arliearliene

Answer 1

With Entity Framework Core you can use inheritance in your database:

public class PeopleContext : DbContext {
    public DbSet<Person> Persons { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<CustomerService>().HasBaseType<Person>();
        modelBuilder.Entity<Sales>().HasBaseType<Person>();
    }
}

This will create one table with the properties of all derived types. Also, it will create a Discriminator-Column so that if you query your database EF Core instantiates the correct derived types:

context.Users.Add(new Sales() {
    Id = 1
});

context.SaveChanges();

// This will actually be of type "Sales"
var salesPerson = context.Persons.Single(u => u.Id == 1);

For more information look here and here.

Orthopedist

Ok, this 3 years later but, I want to ask the following. I have 2 derived types one with the field "illness" and one not. I have the feeling that I waste a bit of resources when I see all these NULL values in the discriminator. Is my feeling correct? Is there any way to increase the performance? – Haematogenous

Answer 2

There are 3 possible approaches you can take here:

---

1. Store all types in a single table (Table per Heirarchy)

You would have a single Person class that contains all possible properties that would be needed between the three classes. In addition, you would add a PersonType enum to specify different types for each entry.

public class Person
{
    public int PersonId { get; set; }

    public string Name { get; set; }

    // ...

    public PersonType Type { get; set; }
}

public enum PersonType
{
    Sales,
    CustomerService
}

This is generally the simplest and best performing approach. The biggest issue is with specialized fields. Since every type is in this one table, this table will need to contain all of the fields that any type may need. This also means all specialized fields need to be nullable, which makes it difficult to enforce specific types having specific fields.

---

2. Store each type in a separate table (Table per Concrete Class)

Instead of having a Person table at all, you could instead just have Sales and CustomerService tables that simply repeat the properties that would have been contained in the Person table.

public class Sales
{
    public int SalesId { get; set; }

    public string Name { get; set; }

    // ...
}

public class CustomerService
{
    public int CustomerServiceId { get; set; }

    public string Name { get set; }

    // ... 
}

Of course, you can still take advantage of the Person abstraction in code if you want. Using code-first, you can make use of inheritance:

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

public class Sales : Person
{
    public int SalesId { get; set; }

    // ...
}

public class CustomerService : Person
{
    public int CustomerServiceId { get; set; }

    // ...
}

Just make sure that you only define entities for Sales and CustomerService in your DbContext subclass:

public class MyContext : DbContext
{
    // Do not include a DbSet for Person.

    public DbSet<Sales> Sales { get; set; }

    public DbSet<CustomerService> CustomerService { get; set; }

    // ...
}

The advantage of this approach is that your types are separated into clear, distinct sets. The downside is that there is no easy way to do a universal search through every single "person" since that abstraction doesn't exist as far as the database is concerned. For example, if you wanted to find someone with a specific name, you'll have to do separate searches through the Sales table and the CustomerService table manually, which may not be ideal. Also, if you end up with a person who serves a role in both sales and customer service, you'll be creating redundancy since you need to enter their information for both entries.

---

3. Store each type and the base type in their own tables (Table per Type)

On top of your Person class, you'll also create Sales and CustomerService classes that each specify their specialized properties and contain a reference to the Person class. This is a common principle known as composition over inheritance; since we can't effectively model inheritance in a database, we can use composition instead.

public class Person
{
    public int PersonId { get; set; }

    public string Name { get; set; }

    // ...
}

public class Sales
{
    public int SalesId { get; set; }

    public int PersonId { get; set; }

    public virtual Person { get; set; }

    // ...
}

public class CustomerService
{
    public int CustomerServiceId { get; set; }

    public int PersonId { get; set; }

    public virtual Person { get; set; }

    // ...
}

This will allow you to add the specialized properties for each type while still maintaining a universal Person table that you can search through. This will also allow you to reuse a person's information if they serve multiple roles. The downside is that creating a new Sales and CustomerService record is a little more tedious, since you'll also need to also either find an existing Person record or create a new one. This also may not be the best on performance since queries may end up requiring joins.

---

The approach you should take depends on your needs. If you want to go more in depth with these 3 strategies, check out this tutorial for implementing inheritance in Entity code-first:

http://www.entityframeworktutorial.net/code-first/inheritance-strategy-in-code-first.aspx

Insole

I have to disagree with you. You can use EF to create Derived classes but still search against the base class. You would add a DbSet for the base class in the DbContext and it will return the proper derived classes. The base class can be abstract or not. – Dinette

#3 is incorrect. You can have EF do all the work for you. I am currently using this and it works beautifully. (1) BaseClass is abstract (2) MyDbContext only has a DbSet<BaseClass> property (not for derived classes). (3) DerivedClass : BaseClass (and similar) This creates a TPT system (separate tables for base class and all derived classes). db.Set<BaseClass>().ToList() will render a List<BaseClass> where every item is of the correct derived type. You can even do db.Set<DerivedClass>().ToList() and EF will filter the list on only items of the appropriate type. – Arliearliene

In other words, you can model inheritance in a database using EF code first. There is no need for composition over inheritance (which is effectively a one-to-many relationship, structurally speaking) Just to be clear: Your code would provide a possible (but not ideal) solution; but your claim that we can't effectively model inheritance in a database is abjectly false, inheritance can be modeled and is a better solution here. – Arliearliene

On closer inspection, all three code samples you provided here are not correct implementations. They all "mock" the inheritance hierarchy and omit an actual inheritance between the entity typed. In all three cases, actual inheritance can be implemented which will save a lot of hassle (as EF then handles some of the logic for you). – Arliearliene

Answer 3

I prefer to use System.ComponentModel.DataAnnotations to apply attributes to my models such as the database table name to the class, the key(s), the foreign keys, and the inverse properties for navigation. EF will use this to auto-magically create the database table(s) with the inheritance. EF can create one table with all the properties from the derived types, or separate tables for each type including the base class (which can be abstract or not). You can search against the base type and it will return the proper implementation of the derived types. So you can get back a list containing both Sales and CustomerService objects.

Dinette

Though I much prefer the attributes over the fluent API, there are solutions where the entities are separate from the datacontext (which makes sense if you want to reuse your entities). In that case, the Fluent API allows you to have a separate entities library that does not have EF as a dependency. – Arliearliene