Given the following tables:
Product
-----------
ProductId : int (PK)
ProductVersion : int
ProductHistory
-----------
ProductId : int (PK)
ProductVersion : int (PK)
Item
-----------
ItemId : int (PK)
ProductId : int (FK) -- ProductId + ProductVersion relates to ProductHistory
ProductVersion : int (FK)
And the following classes:
public class Product { }
public class Item {
public Product Product { get; set; }
}
What I want to happen is this; we get a Product from the Product table, assign it to Item.Product property. But that Item.Product property should map to ProductHistory. The idea is that only the latest version of a product is in the main Product table, so we allow customers to search against that table (so that if each product has 4 versions and there are 1000 products, we only need to query though 1000 products, not 1000 products * 4 versions of each).
Any idea how to acomplish this?
Thanks Andy