Using calculated properties

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.

Getting ready

Complete the Getting ready instructions at the beginning of this chapter.

How to do it…

  1. Add a new folder named CalculatedProperties to the MappingRecipes project.
  2. Add a class named 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; }
  }
}
  3. Add a new embedded mapping named 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>
  4. Add a class named 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);
      }
    }
  }
}
  5. Run the application and start the CalculatedProperties recipe.

How it works…

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:

  • The formula is specified using plain SQL. It may appear strange that we're not using something more abstracted, such as HQL, here. However, the formula mapping is handled at a lower level than the querying system, and it has no knowledge about the rest of the class model. The downside of this is that our mapping possibly becomes tied to the specific DBMS we're using. The upside is that we get easy access to core functions in SQL.
  • We can reference the owning row in the SQL query. In our example we added 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.

There's more…

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"/>
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.47.218