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.

Answer 1

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

Answer 2

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.