c# - Entity Framework Polymorphic associations

Question

I'm going to use Entity Framework soon for a Booking System ( made from scratch ).

I have been doing a few Prototypes, trying to figure out what I want to do before the project is started (I'm still discussing requirements etc. with the client).

Consider this case:

I have a Booking, and a booking can have associated Ressources, that can be booked, but these ressource can be different, and have different Fields etc.

I have never really used EF before, so I don't know how I can achieve this kind of Polymorphism, that I am use to in other projects (where we have been using raw SQL).

The C# Model would look like this:

public class Booking
{
    public int BookingID { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
    public IRessource Ressources { get; set; }
    ...
}

public interface IRessource
{
    public int RessourceTypeId { get; set; }
}

public class Room : IRessource
{
    public int RoomID { get; set; }
    public int RessourceTypeId { get; set; }
    ...
}

public class Car : IRessource
{
    public int CarID { get; set; }
    public int RessourceTypeId { get; set; }
    ...
}

Is this achievable, and if yes, then how? How would the querying even look?

Answer 1

Yes, absolutely.

It sounds like you really want a Table Per Type (TPT) relationship. This represents the standard is-a / has-a foreign key relationships. Note that by default, Entity Framework utilizes Table Per Hierarchy (TPH).

Here are some links:

• Table Per Hierarchy (TPH)
• Table Per Type (TPT)
• Table Per Concrete Type (TPC)

I highly recommend you go through some of these links on your own, but the main point to extract is that you use something like:

modelBuilder.Entity<IRessource>().ToTable("IRessources");
modelBuilder.Entity<Room>().ToTable("Rooms");
modelBuilder.Entity<Car>().ToTable("Cars");

Both of which inherit from IRessource in your demonstrated code above. Then, you can do a join between these individual tables and the central IRessource table to get all of the information pertaining to the individual entities.

Something like: SELECT * FROM IRessource i JOIN Room r ON i.id == r.id

However, you'll often times find that if you use a Web API controller, if you just pull the entire Room POCO, you'll get all of its properties at once without the join at all!

---

In my experience, I've also found that typically things go smoother if you do not use interfaces or abstract classes because if you do, you'll have to make Data Transfer Objects (DTOs) to transfer data occasionally since you can't instantiate objects of those types. Things can get a little bit messy -- especially if you're not sure exactly what you're doing. To help you understand, think of a controller with which you want to pass in a generic IRressource -- it'll try to convert those objects as it receives them into one which can't be instantiated. Bummer! So, the easy solution is just to make both the base class and the inherited classes normal classes.