Non-keyed associations with Entity Framework

I am having problems setting up an association in entity framework between two tables where the association does not contain all the primary key values.

e.g. I have two tables (this is a contrived example, but it is sufficient to represent my real database which I cannot change)

------Items------    ---Orders----
-----------------    -------------
* ItemId        -    * OrderId   -
* EffectiveDate -    - OrderDate -
- Name          -    -开发者_StackOverflow中文版 ItemId    -
-----------------    -------------    * denotes primary key field

Ideally I'd like a property on Orders that indicates the Item that was effective at OrderDate, but I could live with an association on Order to a collection of Items and then create a readonly property on Order that selected the correct Item.

EDIT: The database and model will be read-only so read-only solutions are OK.

Is this possible in entity framework? (or even LINQ to SQL?)

I believe that it is possible using NHibernate (can anyone confirm?) but I keep hitting brick walls with entity framework. So far the only solution I have managed is to create a property in the partial class for Order that uses a 'hack' to access the ObjectContext from order and query the context.Items collection directly as such

private IEnumerable<Item> Items
{
    get 
    { 
        var ctx = this.GetContext();
        return from i in ctx.Items where i.ItemId == this.ItemId select i; 
    }
}

public Item Item
{
    get 
    { 
        return (from i in Items 
               where i.EffectiveDate <= this.OrderDate
               orderby i.EffectiveDate ascending
               select i).First(); 
    }
}

Is there a better solution?

---

The problem is that your database is incorrectly designed and there is no relationship between those tables - Order cannot have FK relation to Item because its FK doesn't contain all parts of Item's PK. In the database this can be avoided by placing unique index on ItemId in the Item table but it makes your composite PK redundant and it doesn't solve the problem for EF because EF doesn't support unique keys. Many-to-many relation cannot be mapped as well because you are missing junction table.

So the answer for EF is no. The same answer will be for linq-to-sql.

---

Rather than a "hack" to get a context, you could use a method and take a context as a parameter, or simply create a new context (in LINQ to SQL at least, there is minimal penalty for this depending on your use-case and if my research is valid).

However, you're trying to create a conditional link, so you are going to have to write a method representing this condition - the frameworks are doing basically the same thing (i.e. selecting the Item with the ID in the FK column). I'm not exactly sure what the issue is in doing that?

I'm also thoroughly confused by your ERD - it seems as though:

• Items have the same ID but different names over time (I wouldn't call the table 'Item' in this case)
• Each order is for one Item
• You are trying to work out what the name of the item was at the time of the order.

Out of curiosity, is this correct?

Based on the fact you can't change your ERD, the method you've mentioned is probably the best way to achieve that (although you might want to mark Items as IQueryable<Item>).