Defining a Derived Boolean Property using Fluent NHibernate

Question

I have a class

public class Account {

   public DateTime? StartDate {get;set;}

   public DateTime? EndDate {get;set;}

   public bool IsActive {get;set;}

}

the IsActive property is defined as a formula as

.Formula(" StartDate < GETDATE() and (EndDate is NULL or EndDate > GetDate())")

However when use QueryOver to query all Accounts where the IsActive == true I get an error that NHibernate cannot execute a SQL.

The SQL that NHibernate generates has

...other criterias...
and this_.StartDate < GETDATE()
   and (this_.EndDate is NULL 
         or this_.EndDate > GetDate()) = 1

How do I define the Formula correctly.

Is Formula the right way to go above doing this or is there a completely different way to go about it

Update :

• Is there a way to do this without altering the underlying database

Thanks

Bollworm

Answer 1

The Formula should/must in this case return the bool. So, we can redifine the formula this way (SQL Serever syntax):

.Formula(@"
(
  CASE
    WHEN  StartDate < GETDATE() and (EndDate is NULL or EndDate > GetDate())
     THEN 1
     ELSE 0
  END
)
";

(The parentheses are not needed, but...) Returned values are 1 or 0 representing bool. So now this QueryOver will work:

var query = session.QueryOver<Account>()
           .Where(a => a.IsActive == true);
var list = query.List<Account>();

Translucid

I agree with the solution, but about the initial statement: Isn't it rather the opposite - the original formula returns a bool which is a "hidden" type that SQL Server pretends not exist and we need to convert it to an integer type (usually bit)? – Scarper

In fact the initial statement returns: 'NHibernate cannot execute a SQL' – Rochdale

I think I was unclear. I meant your statement "The Formula should/must in this case return the bool". But I think it already is bool, and your solution (correctly) converts to an integer type. (Yes, I'm nitpicking here. :) ) – Scarper

Oskar, please do not get me wrong! ;) the issue is that in SELECT clause you cannot have = or < or >. Only if there is CASE statement. This chars means something like WHERE caluse. And that is not allowed in SELECT. My suggestion will work for select, where and even for order by ... does this make sense? – Rochdale

Answer 2

Define the formula on the table itself as a computed column. This has the benefit of working outside of NHibernate as well, without needing to duplicate the formula logic. You will need to inform your NHibernate map that it should never attempt to write to the computed column by setting "Generated" to always.

Mayda

Answer 3

I think this will work but I didn't test it with NHibernate to make sure that the condition is interpreted correctly. You have to use LINQ queries with this method. You could also take a similar approach using extension methods.

public class Account
{
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public bool IsActive
    {
        get { return AccountIsActive(this); }
    }

    public static readonly Func<Account, bool> AccountIsActive = a =>
        {
            var now = DateTime.Now;
            return (a.StartDate.HasValue && a.StartDate < now) && (!a.EndDate.HasValue || a.EndDate > now);
        };
}

Usage:

var activeAccounts = session.Query<Account>()
                     .Where(a => Account.AccountIsActive(a))
                     .ToList();

Grapple