entity framework - How can I define an EF many-to-many relationship between tables that use Table-Per-Type inheritance? -

entity framework - How can I define an EF many-to-many relationship between tables that use Table-Per-Type inheritance? -

i have next models defined:

public class business relationship { public int id {get; set;} ... // other business relationship properties public icollection<accountaddress> addresses {get; set;} } public class address { public int id {get; set;} public string street1 {get; set;} public string street2 {get; set;} ... // other standard address properties } public class accountaddress : address { public int accountid {get; set;} public datetime? begindate {get; set;} public string notes {get; set;} ... // other business relationship address specific properties } public class site { public int id {get; set;} public address siteaddress {get; set;} }

the thought address can tied account site. however, time address associated account, there may additional info needs stored address.

since accountaddress inherits address, wanted utilize tpt (table per type) inheritance map address properties address table , extended properties table of extended address properties.

i tried this:

public class accountconfiguration : entitytypeconfiguration<account> { public accountconfiguration() { totable("accounts"); haskey(a => a.id); hasmany(a => a.addresses).withmany().map(x => { x.mapleftkey("accountid"); x.maprightkey("addressid"); x.totable("accountaddresses"); } } } public class addressconfiguration : entitytypeconfiguration<address> { public addressconfiguration() { totable("addresses"); haskey(a => a.id); ... // other property mappings } } public class accountaddressconfiguration : entitytypeconfiguration<accountaddress> { public accountaddressconfiguration() { totable("addressextended"); ... //other property mappings } }

the problem here there nil defined map accountid addressextended table. if address id of 40 tied 2 different accounts, doing next query:

var _account = _context.accounts .include(a => a.addresses) .singleordefault(a => a.id = 1234);

sometimes gives extended properties wrong account.

what need in order working?

edit: while accepted reply did not reply question (i.e. how can define many many relationship between 2 entities 1 entity defined tpt inheritance mapping), did provide help in coming acceptable work around.

i did drop entity inheritance , added address property of accountaddress entity. gave accountaddress (addressextended table) own primary id. changed accountaddresses table bring together table contains business relationship id , accountaddress id (the new id of addressextended table).

i had add together navigation property accountaddress address , mapping go along that.

i believe may able work around problem changing way view "accountaddress" , "siteaddress" tables. instead of them beingness subclasses of address, why not create them "join tables". accountaddress entity joins address , account, technically has own business relationship address. can specify other info relevant when linking address business relationship (e.g. address type, purge dates, etc).

from understand, goal trying accomplish link many addresses many accounts (or sites), , maintain metadata regarding each link.

public class business relationship { public int id {get; set;} ... // other business relationship properties public icollection<accountaddress> addresses {get; set;} } public class address { public int id {get; set;} public string street1 {get; set;} public string street2 {get; set;} public icollection<accountaddresses> accounts {get; set;} // denotes two-way connection business relationship entity ... // other standard address properties } public class accountaddress { public int accountid {get; set;} public virtual business relationship {get; set;} // these "account" properties public int addressid {get; set;} public virtual address {get;set;} //these "address" properties public datetime? begindate {get; set;} public string notes {get; set;} ... // other business relationship address specific properties }

this model denote next relationship:

1 business relationship <-> * accountaddress * <-> 1 address

this simulates many many relationship between addresses , accounts going through accountaddress entity.

you should configure both business relationship , address entities demonstrate one-to-many relationship like:

modelbuilder.entity<account>().hasmany(a => a.accountaddresses) .withrequired(aa => aa.account) .hasforeignkey(aa => aa.accountid); modelbuilder.entity<address>().hasmany(a => a.accounts) .withrequired(ac => ac.address) .hasforeignkey(ac => ac.addressid);

your can modify query like:

var _account = _context.accounts .include(a => a.addresses) .include(a => a.addresses.address) .singleordefault(a => a.id = 1234);

entity-framework