Is this possible to model a "foreign key" into multiple tables with Entity Framework?

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.

1. Product table. The columns are:

   • Id (identity, PK)
   • Group (NOT NULL)
   • SubGroup (NOT NULL)
   • Code (Unique, NOT NULL)
   • Description

2. Contract table. The columns are:

   • Id (identity, PK)
   • Code (NOT NULL)
   • Descritpion

3. Discount table. The columns are:

   • Id (identity, PK)
   • Type (restricted to one of the four values:
     • 'product',
     • 'group',
     • 'subgroup' or
     • 'contract') (NO开发者_运维问答T NULL)
   • Object (depending on the value of Type refers one of the four:
     • Product.Code
     • Product.Group
     • Product.SubGroup
     • Contract.Code) (NOT NULL)
   • Value (NOT NULL)

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.

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.

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.

Is this, or something similar possible, or joins is the best I can do?

You said that "this is the database design that I can't change", but without changing existing tables, can you at least add views?

If you can, you can create a view for the Discount table that has four different nullable columns for each relationship. That would map nicely in EF as four navigation properties.

After you do that, if you still want a combined column, you could add your own property to the Discount entity that will return an object by checking which of the four navigation properties is not null, and returning the linked entity.

You cannot create a relational database like this. You need separate columns for the keys to each potential parent row.