I have a situation where I have 3 tables: StockItem, Office and StockItemPrice. The price for each StockItem can be different for each Office.
StockItem(
ID
Name
)
Office(
ID
Name
)
StockItemPrice(
ID
StockItemID
OfficeID
Price
)
I've set up a schema with 2 many-to-one relations to link StockItem and Office. So in my StockItem domain object I have a property:
IList<StockItemPrice> Prices;
which gets loaded with the price of the item for each office. That's working fine. Now I'm trying to get the price of an item for a single office. I have the following Criteria query:
NHibernateSession.CreateCriteria(persistentType)
.Add(Restrictions.Eq("ID", id))
.CreateAlias("Prices", "StockItemPrice")
.Add(Restrictions.Eq("StockItemPrice.Office", office))
.UniqueResult<StockItem>();
This appears to work fine as the SQL it generates is what I qould expect. However, I dont know if it populates StockItem.Prices with a single object correctly as as soon as I reference that property NHibernate performs a lazy load of all the office's prices. Also, even if it does work, it feels really crufty having to access the price by using:
mystockitem.Prices[0].Price
What I would really like is to have a Price field on the StockItem object and have the price of the item put into that field by NHibernate.
I've tried adding .CreateCriteria("Price", "StockItemPrice.Price") and the same with CreateAlias, but I get the error
NHibernate.QueryException : could not resolve property: Price of: StockItem
which makes sense I guess as Price isn't a mapped property.
How would I adjust the query to make this possible?
