Entity Framework multiple parent tables

Question

I have an existing database that I am trying to access via Entity Framework 4.3. Most tables and relationships haven't been a problem, but this set of tables is causing me a few issues which I can't seem to find an answer to.

Here are the (condensed) entities:

Customer

public class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }

    private int addressSourceTypeID = 2;
    [NotMapped]
    public int AddressSourceTypeID { 
        get { return addressSourceTypeID; } 
        set { addressSourceTypeID = value; } }

    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<Contract> Contracts { get; set; }
    public virtual ICollection<Address> Addresses { get; set; }
}

Contract

public class Contract
{
    public int ContractID { get; set; }
    public string Name { get; set; }

    private int addressSourceTypeID = 4;
    [NotMapped]
    public int AddressSourceTypeID { 
        get { return addressSourceTypeID; } 
        set { addressSourceTypeID = value; } }

    public virtual int CustomerID { get; set; }
    public virtual Customer Customer { get; set; }

    //public virtual ICollection<Address> Addresses { get; set; }
}

Address

public class Address
{
    [Key]
    public int AddressID { get; set; }
    public int AddressSourceTypeID { get; set; }

    [ForeignKey("Customer")]
    public int SourceKey { get; set; }

    public virtual Customer Customer { get; set; }
    //public virtual Contract Contract { get; set; }
    public virtual ICollection<Contact> Contacts { get; set; }
}

What I have above is two entities Customer and Contract that both can have child Address entities. Currently the Address entity is set up to be a child of the Customer entity and this works fine as there isn't a link to Contract from Address.

I have tried adding in Contract to the Address entity as I have done with the Customer entity as you can see from the commented out code segments. Unfortunatly this doesn't work, but I'm not surprised due to the reference to Customer in the Address ForeignKey annotation. I even tried to create specific version of the Address entity (i.e. CustomerAddress), but I get an error when more than one entity is attempting to bind to the same table.

I have also tried using ModelBuilder in the EF DBContext however my knowledge here is pretty limited and I'm not sure how to do it in this case.

Overall, what I need is the following:

• Customer entity to have a collection of child Addresses.
• Contract entity to have a collection of child Addresses.

The link between these 'parent' tables to the Address table uses the following:

• Customer: CustomerID => Address: SourceKey AND Customer: AddressSourceTypeID (always 2) => Address: AddressSourceTypeID.
• Contract: ContractID => Address: SourceKey AND Contract: AddressSourceTypeID (always 4) => Address: AddressSourceTypeID.

If anyone could help me or point me in the correct direction that would be great.

Thanks very much.

Answer 1

You can either have EF enforce your SourceKey attribute using Table per Hierarchy Inheritance - and then you're mapping will break, or you can enforce the SourceKey in your business logic and only have EF manage the main Address class.

If you have to maintain your current DB schema, I think having your business logic enforce your SourceKey as a disriminator is your only option:

public class Address
{
    public int AddressID { get; set; }
    public int AddressSourceTypeID { get; set; }
    public int SourceKey { get; set; }
    public virtual Contract Contract { get; set; }
    public virtual Customer Customer { get; set; }
}
public class Contract
{
    public Contract()
    {
        this.Addresses = new List<Address>();
    }

    public int ContractID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Address> Addresses { get; set; }
}
public class Customer
{
    public Customer()
    {
        this.Addresses = new List<Address>();
    }

    public int CustomerID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Address> Addresses { get; set; }
}

And this in your fluent mappings:

        modelBuilder.Entity<Address>().HasOptional(t => t.Contract)
            .WithMany(t => t.Addresses)
            .HasForeignKey(d => d.SourceKey);
        modelBuilder.Entity<Address>().HasOptional(t => t.Customer)
            .WithMany(t => t.Addresses)
            .HasForeignKey(d => d.SourceKey);

Alternatively - if you created a CustomerAddress and ContractAddress, you can using TPH inheritance enforce the SourceKey - but currently there's no way to map the Nav properties:

public abstract class Address
{
    [Key]
    public int AddressID { get; set; }
    public int AddressSourceTypeID { get; set; }

    public int SourceKey { get; set; }
}

public class CustomerAddress : Address
{
    public virtual Customer Customer { get; set; }
}

public class ContractAddress : Address
{
    public virtual Contract Contract { get; set; }
}

And this as your mapping:

        modelBuilder.Entity<Address>()
            .Map<ContractAddress>(m => m.Requires("AddressSourceTypeID").HasValue(2))
            .Map<CustomerAddress>(m => m.Requires("AddressSourceTypeID").HasValue(4));

This will enforce AddressSourceTypeID as your discriminator - unfortunately the breakdown here is in mapping your nav property back to the ContractAddress and Customer Address. See this related post which had the same basic problem. Maybe this will start you in the right direction at least.