Non-keyed associations with Entity Framework

Question

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          -    - 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?

score 2 · Accepted Answer · answered Apr 21 '11 at 07:34

2

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.

answered Apr 21 '11 at 07:34

Ladislav Mrnka

360,892
59
660
670

Thanks, that is what I suspected but I find it odd that what is essentially a simple join in SQL would cause so much trouble for the entity framework. – Eamon Apr 21 '11 at 11:43
The join would be perhaps possible but whole your entities will became read only (no updates, deletes, inserts). – Ladislav Mrnka Apr 21 '11 at 12:11
I should probably have added that I am creating a read-only model (i'll update the Q). The application will not be writing to this particular database at all so a read-only collection would be perfectly suitable in this situation. – Eamon Apr 21 '11 at 22:59

score 0 · Answer 2 · answered Apr 21 '11 at 07:47

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>).

The example is was contrived to illustrate the problem, it might be easier to mentally substitute Item.Price in instead of Item.Name and call the table ItemPrices, but the effect is the same. The real database is a lot more complex and badly designed but it is completely out of my power to change anything about it (doesn't even have a single foreign key constraint even though the tables are clearly meant to be related) The table essentially records temporal state of an object and I want to retrieve the appropriate record at a point in time. — Eamon, Apr 21 '11 at 11:54
Yeah so I think my answer applies in that case. You can't have conditional FKs, so you will want to mimic the FK code of the framework and return an IQueryable, not an IEnumerable. — Matt Mitchell, Apr 22 '11 at 14:29

Non-keyed associations with Entity Framework

2 Answers2