How can I model non-matching Foreign Keys

Question

I'm trying to use EF to model an existing SQL database. The DB is multi-tenant by having a clientID column in every single table (I cannot change this). I have table structure like the following.

Table 'ItemID' columns:

ClientID (pk)
ItemID (pk)
ItemName

Table 'Items' columns:

ClientID (PK) 
ItemID (PK) [FK to ItemID.ItemID]
Version (PK)
ItemAttribute1
ItemAttribute2
ItemAttribute3

The DB is designed to store previous versions (rows) of the 'Item' object, hence the 'Version' column and PK.

I am new to the EF and trying to adopt it for my project. However, it seems that EF cannot handle this situation very well. I am open to all ideas including using stored procedures or views instead of access tables directly from EF. What about getting rid of the PKs and using 'independant' relations instead?

One specific problem I ran into is that EF (at least the designer) requires all PKs in one table be mapped to all PK columns in any related table. This does not make sense to me, as the example I've given will never work given that constraint. I am trying to make Items inherit from ItemID. The error I get is:

Error 3003: Problem in mapping fragments starting at line 170:All the key properties (ItemID.ClientID, ItemID.ItemID) of the EntitySet ItemID must be mapped to all the key properties (Items.ClientID, Items.ItemID, Items.Version) of table Items.

I have been looking up all I can find on this topic and nothing has answered these questions for me. I appreciate any help. Thanks.

Answer 1

The error that you are getting from your EDM is coming from the fact that EF supports inheritance only if both entities have the exact same primary keys (hence a one to one relationship on database) so you cannot have inheritance between these 2 entities according to the current schema.
However, I don't see a problem on having a One to Many association between ItemID and Items entities and I believe this is the default EF behavior when you update your model from the database.

Please have a look at this post for more info: Entity Framework, TPT Inheritance