Lesser-Known NHibernate Features: Calculated Properties

With NHibernate you can have entity properties that are the result of a SQL expression (unlike other O/RMs). It is also possible to add arbitrary SQL restrictions to collections of an entity.

First, here’s how we define a calculated property:

public class MyEntity

{

    //rest goes here

    public Int32 ? MyCalculatedProperty { get; protected set; }

}

mapper.Class<MyEntity>(c =>

{

    //rest goes here

    c.Property(x => x.MyCalculatedProperty, x =>

    {

        x.Formula("(SELECT MAX(SomeTable.Something) FROM SomeTable WHERE SomeTable.Id = Id)");

        x.Insert(false);

        x.Update(false);

    });

});

NHibernate is clever enough to find out that the un-prefixed Id refers to the entity’s table. This is a silly example, but I think you get the picture. Remember that this is plain SQL, not HQL, and will not be translated in any way.

As for collection restrictions, a simple example:

public class MyEntity

{

    //rest goes here

    public virtual IEnumerable<MyIssue> RecentIssues { get; protected set; }

}

mapper.Class<MyEntity>(c =>

{

    //rest goes here

    c.Set(x => x.RecentIssues, x =>

    {

        c.Where("(date >= (GETDATE() - 7))");

        //rest goes here

    }, c => c.OneToMany());

});

Notice that I am mapping the RecentIssues collection as IEnumerable<T>, this is because otherwise I would have to check if the values being added matched the desired constraint (“>= GETDATE() – 7”, the last 7 days). Certainly possible, but I leave it as an exercise to you, dear reader! Of course, GETDATE() is a SQL Server function, the restrictions can only be specified in native SQL.

Stay tuned for more!