EF Code First One To Many (using a "common" table)

Question

consider the following example:

A person can have multiple addresses. A store can have multiple addresses. Both have a 1:M to the address table.

Using EF6,Code First, how do I set up my entities and mappings (FluentApi) to support this? In my experience with EF 1:M, the child table (address) is unique to its parent table (Person or Store). I would have a Person Table and a PersonAddressTable with a 1:M relationship. I would also have a Store and StoreAddress tables with their own 1:M relationship. Unfortunately for me, the existing data model is written this way.

Its my understanding that I define a 1:M relationship by adding a nav property on the child entity that points back to the parent. The child entity must also have an ID field (FK) that points back to the parent, and this FK Property must be marked as required on the child entity.

so in my example, the Address Entity would have

public virtual Person Person {get;set;}
public int PersonId {get;set;    
public Virtual Store Store {get;set;}
public int StoreId {get;set;

and the Address entity mapper would define:

this.Property(i=>i.PersonId).IsRequired();
this.Property(i=>i.StoreId).IsRequired();

this.HasRequired(p=>p.Person)
.WithMany(c=>c.Addresses)
.HasForeignKey(c=>PersonId);

this.HasRequired(p=>p.Store)
.WithMany(c=>c.Addresses)
.HasForeignKey(c=>c.StoreId);

But depending on their parent, the child navigation property (Person or Store) will be null. When referencing addresses for a Person, the Address.Store nav property will be null, and when referencing addresses for a store, the Address.person nav property will be null.

How can I correctly define these relationships?

Thanks.

Answer 1

First of all it's not immediately clear whether or not you are in control of the underlying table or if you are stuck with an existing table. However assuming that you do have control of the table and simply want to map all addresses to a singular table you could do this with TPH.

Please find below an example of how it could be set up.

    public class AppContext : DbContext
    {
        public DbSet<Person> People { get; set; }
        public DbSet<Store> Stores { get; set; }
        public DbSet<Address> Addresses { get; set; }
    }

    public class Person
    {
        public Person()
        {
            Addresses = new HashSet<PersonAddress>();
        }
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<PersonAddress> Addresses { get; set; }

    }

    public class Store
    {
        public Store()
        {
           Addresses = new HashSet<StoreAddress>();
        }
        public int Id { get; set; }
        public string StoreName { get; set; }

        public virtual ICollection<StoreAddress> Addresses { get; set; }

    }

    public abstract class Address
    {
        public int Id { get; set; }
        public string AddressInformation { get; set; }
    }

    public class PersonAddress : Address
    {
        public string PersonAddressInformation { get; set; }
    }

    public class StoreAddress : Address
    {
        public string StoreAddressInformation { get; set; }
    }

The database tables generated from this would look like this.

Answer 2

I am not sure how to do it with the FluentAPI, but this is one way to do it with CodeFirst.

Note, the Store and Person reference the Address, not the other way around.

I would have the following three classes:

public class AddressModel
{
    public int AddressId { get; set; }
    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
}

public class PersonModel
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<AddressModel> Addresses { get; set; }
}

public class StoreModel
{
    public int StoreId { get; set; }
    public string StoreName { get; set; }
    public List<AddressModel> Addresses { get; set; }
}