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

0
votes

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.

2
c#entity-frameworkef-code-first
I may be wrong, but I think the Virtual properties are optional in your address class. You need them for sure if you want lazy loading. At any rate, if you are dealing with a person, why does it matter if Addresses.Store is null, and why does it matter if Addresses.Person is null if you are dealing with a store? - Johnie Karr
You shouldn't have the PersonId and StoreId in your address class. Just the Virtual property is fine. - Johnie Karr
If I dont have PersonId and StoreId on the address class, how do I specify the relationship in my mapping code? This.HasRequired(p=>p.Person) .WIthMany(c=>c.Addresses).HasForiegnKey(WHAT_GOES_HERE)? - cdarrigo
I m also having a similar question. any soln yet? stackoverflow.com/questions/25042768/… - codebased

2 Answers

1
votes

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; }
}
0
votes

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.

TPH Table Diagram