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

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