NHibernate, Views and Formula columns
If you have an object type mapped to table1 and you need a piece of info from a related object type (thats mapped to a table2) then you can use a view (rather than a table) to make this attribute available in object type1. However this can cause issues with updates. So instead of a view you can use a computed column within object type 1 to gather the related data from the other table. This computed column uses the formula attribute within the nhibernate XML mapping file for object type 1.
Example
Object of type 1 requires a value from a related object of type 2. in this example the data type is a string and the relationship between the two objects is a one to one (so no issues with non existant records or multiple instances). This relation is actually already mapped but uses lazy loading becuase of the size of type 2 class (we don’t want to load the other type becuase it takes too long). However we still require this one string from the type 2 class in certain circumstances but dont want the performance hit of loading type 2.
We could use a view rather than the table within the NHibernate XML mapping file for type 1 but NHibernate will break if we need to perform an update - even if we are not updating this extra column (we do want to perform an update and we don’t need to update the string but the update = “false” has no effect).
So instead, within the object/table1 mapping file, add a new property to inform NHibernate of this new attribute within object type1. Please note the use of qualifiers within the SQL as NHibernate uses alias’s when it emits SQL to the datastore and that SQL will break if you don’t follow the syntax below exactly.
<property name="Name" type="String" formula="(SELECT <tableName2>.<columnName> FROM <tableName2> WHERE tableName2.<primaryKey> = <foreignKey>)" update="false" insert="false"/>