A property need not, always, be represented as a column value in the database. In plain SQL, we may have used built-in or custom functions, such as string length or encryption, or perhaps even a subquery, to project the data we want to expose. NHibernate allows you to map such a construct directly to a property.
CalculatedProperties
to the MappingRecipes
project.Invoice
to the folder:using System; namespace MappingRecipes.CalculatedProperties { public class Invoice { public virtual Guid Id { get; protected set; } public virtual decimal Amount { get; set; } public virtual string Customer { get; set; } public virtual int InvoicesOnCustomer { get; protected set; } } }
Invoice.hbm.xml
to the folder:<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MappingRecipes" namespace="MappingRecipes.CalculatedProperties"> <class name="Invoice"> <id name="Id"> <generator class="guid.comb"/> </id> <property name="Amount"/> <property name="Customer"/> <property name="InvoicesOnCustomer" formula="(SELECT COUNT(*) FROM Invoice i WHERE i.Customer=Customer)"/> </class> </hibernate-mapping>
Recipe
to the folder:using System; using NH4CookbookHelpers; using NHibernate; namespace MappingRecipes.CalculatedProperties { public class Recipe : HbmMappingRecipe { protected override void AddInitialData(ISession session) { session.Save(new Invoice { Amount = 200, Customer = "A" }); session.Save(new Invoice { Amount = 2000, Customer = "A" }); session.Save(new Invoice { Amount = 200, Customer = "B" }); } public override void RunQueries(ISession session) { var invoices = session.QueryOver<Invoice>().List(); foreach (var invoice in invoices) { Console.WriteLine(@"Amount: {0}, InvoicesOnCustomer: {1}", invoice.Amount, invoice.InvoicesOnCustomer); } } } }
CalculatedProperties
recipe.The formula attribute on the property element specifies that we want a SQL expression to be used to retrieve the property value, instead of a plain column reference. In this case we created the rather useless property InvoicesOnCustomer
, which returns the COUNT of all invoices with the same Customer
as the current row. Two things are worth noting here:
WHERE i.Customer=Customer
to the formula query. This may look strange from a plain SQL perspective. Where does the last Customer come from? Well, NHibernate doesn't leave the SQL expression completely untouched. Instead, it parses it and injects the owning row's alias to all column references which has no other alias.Looking at the SELECT
query executed by the recipe, we see this:
SELECT this_.Id as Id0_0_, this_.Amount as Amount0_0_, this_.Customer as Customer0_0_, (SELECT COUNT(*) FROM Invoice i WHERE i.Customer=this_.Customer) as formula0_0_ FROM Invoice this_
The SQL expression we specified has been included in the query, and the last reference to Customer
has been prefixed with the currently used alias, in this case this_
.
For obvious reasons, a formula mapping effectively becomes read only. Updates to the property value will be silently ignored. It's therefore recommended that the property write access is restricted as much possible.
Formulas are not restricted to properties. They can be used in many mappings where a column reference is normally used. This includes the keys in <one-to-one>
, <many-to-one>
and <many-to-many>
relations and even the discriminator value in a class hierarchy. Such a mapping would allow the subclass discrimination to be based on virtually anything, like the contents of a text field. The most likely scenario though, is a legacy database where many different values in a column should map to one specific subclass. Such a formula mapping could look like this:
<discriminator formula="CASE WHEN CustomerType IN ('Gold', 'Silver') THEN 'Preferred' ELSE 'Normal' END" type="string"/>
3.15.226.120