Defining a Derived Boolean Property using Fluent N

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

Answer 1

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();

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.

Answer 3

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>();