Databases
StackOverflow2013
Postsdbo
POIs this possible to model a "foreign key" into multiple tables with Entity Framework?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs this possible to model a "foreign key" into multiple tables with Entity Framework?
    text
    copied!<p>I have a MS SQL 2008 database, and I can't change its schema. I have to work with what it is. It has three tables that relevant to the question.</p> <ol> <li><p>Product table. The columns are:</p> <ul> <li>Id (identity, PK)</li> <li>Group (NOT NULL)</li> <li>SubGroup (NOT NULL)</li> <li>Code (Unique, NOT NULL)</li> <li>Description</li> </ul></li> <li><p>Contract table. The columns are:</p> <ul> <li>Id (identity, PK)</li> <li>Code (NOT NULL)</li> <li>Descritpion</li> </ul></li> <li><p>Discount table. The columns are:</p> <ul> <li>Id (identity, PK)</li> <li>Type (restricted to one of the four values: <ul> <li>'product',</li> <li>'group',</li> <li>'subgroup' or </li> <li>'contract') (NOT NULL)</li> </ul></li> <li>Object (depending on the value of Type refers one of the four: <ul> <li>Product.Code</li> <li>Product.Group</li> <li>Product.SubGroup</li> <li>Contract.Code) (NOT NULL)</li> </ul></li> <li>Value (NOT NULL)</li> </ul></li> </ol> <p>The idea is that the discount can be applied to either of the four. I'd like to reiterated, that this is the database design that I can't change.</p> <p>With Entity Framework I can query the tables all right with joins but I can't use navigation properties out of the box, because navigation properties are generated based on foreign key relationships from database, and you can't define "conditional" relationship in MS SQL, where the field object relates to one table when field type contains this value and relates to another table when the value is different.</p> <p>My question is this: Is this possible to define classes and mappings with Entity Framework, so that I can use navigation properties in this scenario? For example, I do Discount.Object and I receive either Contract object or Product object in response, and if this is a Product object it's retrieved on the right property.</p> <p>Is this, or something similar possible, or joins is the best I can do?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload