EF 4 - associations with keys that dont match

Question

We're using POCOs and have 2 entities: Item and ItemContact. There are 1 or more contacts per item.

Item has as a primary key:

ItemID LanguageCode

ItemContact has:

ItemID ContactID

We cant add an association with a referrential constraint as they have differing keys. There isnt a strict primary / foreign key as languageCode isnt in ItemContact and ContactID isnt in Item.

How can we go about mapping this with an association for contacts for an item if there isnt a direct link but I still want to see the contacts for an item?

One of the entities originates in a database view so it is not possible to add foreign keys to the database

Thanks

Stephen Ward

Answer 1

In order to create any relationship (in EF or any ORM for that matter) you have to have something to Join on.

Because at the moment your don't, you need to fabricate something...

The only option I can think of is to create a Relationship - using some of the same techniques described in here to create an SSDL view to back the relationship using a <DefiningQuery> based on a cross product join.

So if you have data like this:

ItemID | LanguageCode
1 | a

and this:

ItemID | ContactID
1 | x
1 | y
1 | z

Then your <DefiningQuery> should have T-SQL that produces something like this:

Item_ItemID | Item_LanguageCode | ItemContact_ItemID | ItemContact_ContactID
1 | a | 1 | x
1 | a | 1 | y
1 | a | 1 | z

Now because this is technically an Independent Association - as opposed to an FK association - you should be able to claim in the CSDL that the cardinality is 1 - * even though there is nothing in the SSDL to constrain it - and stop it from being a * - *.

Hope this helps

Alex