Page History

...

To migrate from the old database to the new object model, we must create a another, special mapping. This mapping will only be used for loading, not for saving, so we can use some special features like embedding SQL queries into the HBM.

...

Specifically, we can do the following:

    <property name="Discontinued" formula="( Available = 0 )"/>

...

SELECT product.Id, product.Name, product.Category, ( product.Available = 0 ) FROM Product product WHERE product.Id=...

Since 'Available' is a boolean value stored as integer, we do a '=0' compare to inverse the boolean value. Also notice that NHibernate will insert 'product.' in front of 'Available', since it recognizes it as a column in the database.

We could also have inserted an entire SQL query, for example:

    <property name="Discontinued" formula="SELECT p.Available = 0 FROM Product p WHERE p.Id=?
 = Id"/>

The resulting (cleaned-up) SQL is as you would expect:

SELECT product.Id, product.Name, product.Category, ( SELECT p.Available = 0 FROM Product p WHERE p.Id = product.Id ) FROM Product product WHERE product.Id=...

The result is equivalent (although quite a bit longer), but hopefully this shows how powerful subqueries can be. You could also retrieve values from entirely different tables here.