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?