Monday, 25 February 2013

Model Inheritance and Relationships in Entity Framework Code First

Introduction


I was recently assisting with an Entity Framework related query on the ASP.NET forum. The question seems pretty straightforward and I assumed that an example would already exist for their scenario. However, after searching I didn't find anything that directly addressed their specific query. Rather than give up, I decided to make a console application to try out the scenario for myself. It was good timing as I expect to have a very similar requirement myself in one of my upcoming web applications so this was an opportunity to play with the concept. I've decided to share the resulting code here as I haven't found many blog posts covering this specific use case.

The Scenario


The person posting the question was building a basic appointment system for a website. He needed to have an appointments entity, a clients entity and a staff entity. Each appointment would have a related staff member and client. No problems there, however he wanted to utilise inheritance on his clients and staff objects whilst having both map to a table named people in the database. The area they were stuck on seemed to be defining the relationships on the appointments table to manage having a relationship for a staff member and client linking back to the single people table. This all needed to be defined with Entity Framework Code First which would then generate the database tables.

The Solution


So nothing too complex there and my first thought was that I should be able to find an existing blog post to link to. As I've mentioned, after a bit of Googling nothing seemed to cover this particular requirement directly. There are many on using inheritance with Entity Framework but those didn't seem to show a usage scenario when the resulting table would need to have two relationships with another table. So I opened up Visual Studio and threw together a small console application to build out the example.

I started with the models which were pretty simple for the purposes of testing.
public abstract class Person
{
     public int PersonID { get; set; }
     public string Name { get; set; }
}

public class StaffMember : Person
{
     public string BadgeNumber { get; set; }
     [ForeignKey("StaffMemberID")]
     public virtual ICollection<Appointment> Appointments { get; set; }
}

public class Client : Person
{
     [ForeignKey("ClientID")]
     public virtual ICollection<Appointment> Appointments { get; set; }
}

public class Appointment

{
     public int AppointmentID { get; set; }
     public DateTime Date { get; set; }

     public int? StaffMemberID { get; set; }
     [ForeignKey("StaffMemberID")]
     public virtual StaffMember StaffMember { get; set; }

     public int? ClientID { get; set; }
     [ForeignKey("ClientID")]
     public virtual Client Client { get; set; }
}
The things to take note of here are that Person is an abstract class, allowing me to use it as a base for the StaffMember and Client classes. I could just have easily included a Name property on Client and StaffMember classes and allowed EF to generate a table for each class. However, in a real world situation there's likely to be many more shared properties (forename, surname, DOB, title, telephone number etc.) and it doesn't make a lot of sense to define these multiple times. Clients and StaffMembers are both types of Person and using this base class makes the code a little cleaner to work with as they only have a few distinct properties each.

The other thing I want to draw attention to is the ForeignKey data annotations. These are used to create friendlier names for the generated Foreign Keys on the appointment table. As you can see, this table has two foreign keys, one for the StaffMember who will be leading the appointment and one for the Client who is attending. The actual relationships will be with the people table as although we have separate classes to represent these two variants of Person, only a single table is created.

This is the default inheritance behaviour for Code First which is known as Table Per Hierarchy. Entity Framework is clever enough to know that these classes derive from Person and will only produce a single table which is capable of representing both derived objects. Note that this is configurable and you could choose to represent these objects in separate tables just as easily.

The resulting table has the following fields
  • PersonID
  • Name
  • BadgeNumber
  • Discriminator
The BadgeNumber will only have data when the object that is saved is of type StaffMember. If Clients had specific fields these would also be included and used only for Client objects. These fields are therefore null-able since they will not always be used (a Client will never have an employee badge number). This won't always be acceptable for the design of the database. Take for example the BadgeNumber in this example. If I wanted to enforce within the database that all StaffMembers must have a badge number we couldn't do that in this design. In that case another of the inheritance settings would need to be used to create a Table Per Type structure. This still allows us to have a base object but any objects derived from that base class will have their own table linked back by the primary key to the base table people table. This allows stronger rules regarding allow nulls to be configured. It's up to you and you database architect to decide what is most appropriate for your application.

The last field is Discriminator which is an Entity Framework generated column to allow it to record which actual type of object is being stored in the table. In this case some records will have a discriminator of "StaffMember", others will have "Client". We can use this when querying the context to specify which type of objects we want back which I'll show a little further on.

Next up I created the context to tell Entity Framework about the classes I wanted to include in my database.
public class MyContext : DbContext
{
     public DbSet<Person> People { get; set; }
     public DbSet<Appointment> Appointments { get; set; }
     protected override void OnModelCreating(DbModelBuilder modelBuilder)
     {
          base.OnModelCreating(modelBuilder);
     }

     public void Seed(MyContext context)
     {
          Client c1 = new Client { Name = "Steve Client" };
          StaffMember sm1 = new StaffMember { Name = "Staff 1", BadgeNumber = "1234" };
          StaffMember sm2 = new StaffMember { Name = "Staff 2", BadgeNumber = "5678" };

          context.People.Add(c1);
          context.People.Add(sm1);

          context.Appointments.Add(new Appointment { Client = c1, StaffMember = sm1, Date = DateTime.Now.AddDays(1) });
          context.Appointments.Add(new Appointment { Client = c1, StaffMember = sm2, Date = DateTime.Now.AddDays(2) });
          context.Appointments.Add(new Appointment { Client = c1, StaffMember = sm2, Date = DateTime.Now.AddDays(3) });
          context.SaveChanges();
      }

      public MyContext()
      {
           Database.SetInitializer<MyContext>(new DropCreateDatabaseAlways<MyContext>());
           this.Seed(this);
      }
}
This may look a little complex but the main parts to focus on are the two DBSets I have created to instruct Entity Framework to model the Appoints and People tables. I don't need to specify our derived classes here as they will be mapped to the People table whenever they are saved by Entity Framework.

I use a constructor to tell the initializer to always drop and recreate my table. This is so I can always start with a clean database for the purposes of this test app. This would certainly not be set in production! Finally I use a Seed method to populate some Staff, Client and Appointment records in the database so I can do some test queries.

Here you can see that I can new up StaffMembers or Clients as I need them and then add them to the People entity in the context. Entity Framework takes responsibility for mapping these correctly and including the discriminator value.

At this point I'm nearly there. I have a domain model defined which I can code against and I have told Entity Framework how to map that to a database for us. I have populated the database with some test data and the final part of the application will show how we can query against it.
static void Main(string[] args)
{
     using (var db = new MyContext())
     {
          var client = db.People.OfType<Client>().FirstOrDefault();
          Console.WriteLine(client.Name);
          foreach (var a in client.Appointments)
          {
               Console.WriteLine(string.Format("{0} - {1}", a.Date, a.StaffMember.Name));
          }

          Console.Read();
     }
}
I'm using a console application to keep things simple, so all the logic is entered directly on the main method. I use a simple command to access the People entity and request back the first record. The key part of this is the OfType extension method which allows me to specify I want a client object. This is where Entity Framework uses the Discriminator field in the database to know what object each record represents.

With the client in hand I can write out their name and then loop through to display a list of their appointments.

Summary


And there we have it. This has been a simple example of how inheritance can be used to utilise base classes for our domain objects while mapping to a suitable database structure using Entity Framework. We also covered how we set up our relationships to handle the case were we need a base object to relate more than once in the same table as we did with the Appointments in this example.

4 comments:

  1. I ran into the same issue and I'm very glad I found your well written article !

    ReplyDelete
  2. Awesome post!

    ReplyDelete
  3. Why you pass context in Seed method?
    Instead: context.People.Add(c1);
    You can use: this.People.Add(c1);
    Or even: People.Add(c1);

    ReplyDelete
  4. Wow, perfect I'm glad you wrote this post. I'm stuck with the exact same scenario at the moment. I'll have to re-jig my models but it is essentially the TPH hiearchy with all subclasses relating to another entity/table with. Thanks again.

    ReplyDelete