Populate an unmapped property of domain object from result of join with Nhibernate

Question

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?

Answer 1

If you have the prices for all the offices in the Prices property, why go back to the database?

public virtual StockItemPrice GetPriceForOffice(Office office)
{
    return Prices
        .Where(p => o.Office.Equals(office))
        .SingleOrDefault();
}

Answer 2

If you are willing to remove the ID from StockItemPrice, you could better handle this by using a map.

Declare your Prices property as follows:

IDictionary<Office, decimal> Prices { get; set; }

And map it like this:

<map name="Prices" lazy="extra">
  <key column="StockItemID" />
  <map-key-many-to-many class="Office" column="OfficeId"/>
  <element type="Decimal" column="Price"/>
</map>

Pay special attention to the lazy="extra" attribute. It means when you do this:

decimal priceInNY = item.Prices[nyOffice];

Only the price for nyOffice (an Office instance or proxy) will be retrieved from the DB.