Define Many Tables to One Table relationship in Code First approach

Question

I am in the process of building up a data model in Entity Framework using the Code First approach, but one part has me a bit stumped. The title on this question may be a bit confusing, so I will explain my problem in detail. The length of this post may be daunting, but I think it's a fairly straightforward problem.

I have one model defined like this:

public class KeyValuePair
{
    [Key]
    [MaxLength(128)]
    [Column(Order = 0)]
    public virtual string OwnerId { get; set; }

    [Key]
    [MaxLength(128)]
    [Column(Order = 1)]
    public virtual string Key { get; set; }

    public virtual string Value { get; set; }
}

My intent is for this to just define a generic table for storing key-value properties on other entities in the system. I am using GUIDs for all of my Ids, so OwnerId should uniquely refer to one entity in the system, and the pair (OwnerId, Key) should uniquely identify one property on one entity.

In other words, I want to allow multiple tables in my system to have a One->Many relationship to this KeyValuePair table.

So for example, if I wanted to store the height of a Person who has the ID b4fc3e9a-2081-4989-b016-08ddd9f73db0, I would store a row in this table as:

OwnerId = "b4fc3e9a-2081-4989-b016-08ddd9f73db0"
Key = "Height"
Value = "70 in."

So now I want to define navigation properties from the parent entities to this table, like (to take the Person example):

public class Person
{
    [Key]
    public virtual string Id { get; set; }

    public virtual string Name { get; set; }

    // I want this to be a navigation property
    public ICollection<KeyValuePair> Properties { get; set; }
}

But I'm not sure how do define the relationship between Person and KeyValuePair so that Entity Framework knows that it should look up the Person's properties by matching the Person's Id against the KeyValuePairs' OwnerId. I can't define a foreign key in the KeyValuePair model, because the OwnerId is going to refer to Ids in several different tables.

It looks like I can do the following to define a relationship from Person to KeyValuePair in OnModelCreating:

modelBuilder.Entity<Person>()
    .HasMany(p => p.Properties).WithMany().Map(mp =>
    {
        mp.MapLeftKey("Id");
        mp.MapRightKey("OwnerId", "Key");
        mp.ToTable("PersonDetail");
    });

Or I could even give the KeyValuePairs their own unique IDs, get rid of OwnerId, and do this:

modelBuilder.Entity<Person>()
    .HasMany(p => p.Properties).WithMany().Map(mp =>
    {
        mp.MapLeftKey("Id");
        mp.MapRightKey("Id");
        mp.ToTable("PersonDetail");
    });

But both of these approaches involve the creation of an intermediary table to link the Person and KeyValuePair tables, and that seems like excessive overhead in terms of bloating my database schema and requiring more expensive JOINs to query the data.

So is there a way to define the relationship such that I don't need to involve intermediary tables? Am I going about this database design the wrong way?

Side note: For anyone wondering why I am using this approach to define properties on my entities rather than simply adding fixed properties to the data model, I am using fixed properties in the data model where applicable, but the application I am building requires the ability to define custom properties at runtime. I also think this question is applicable to other potential scenarios where multiple tables have a One->Many relationship to a shared table.

Answer 1

The only way I can think of doing it (and I'll admit, this is not the best of ideas, but it will do what you're asking) would be to have any classes that need to have this relationship with KeyValuePair implement an abstract class that contains the fully implemented navigational property, as well as the ID field. By "fully implemented" I don't mean an actual, mapped relationship; I mean that it should use a DbContext to go out to the KeyValuePair table and actually grab the relevant properties given the ID.

Something like this:

public abstract class HasKeyValuePairs
{
   [Key]
   public virtual string Id { get; set; }

   [NotMapped]
   public ICollection<KeyValuePair> Properties
   {
      get
      {
          using(var db = new DbContext())
          {
             return db.KeyValuePairs.Where(kvp => kvp.OwnerID == this.ID);
          }
      }
   }
}

Assuming you're using Lazy Loading (given that you're using the virtual keyword), there shouldn't be much extra overhead to doing it like this, since EF would have to go back to the database anyway to pick up the properties if you ever called for them. You might need to have that return a List just to avoid any potential ContextDisposedException later on in your code, but that at least will get you up and running.