Criteria for where predicate using columns from two tables and functions in NHibernate

Question

My goal is to fetch two values from two tables joined together and perform a comparison which if true, outputs the rows from table 1. The TSQL code below illustrates the query, question is whether there is a way to do the third predicate in the where clause using NHibernate criteria using session.CreateCriteria:

declare @currentSystemTime datetimeoffset = '2015-07-22 18:42:16.1172838 +00:00'
select 
    inst.ExpiryDate, 
    ent.DaysToExpiryNotificationStart, 
    convert(date, dateadd(day, -ent.DaysToExpiryNotificationStart, inst.ExpiryDate)) as NotificationStart, 
    convert(date, @currentSystemTime) as CurrentSystemTime, 
    * 
from 
    Instances inst
    inner join Entries ent on inst.Entry_id = ent.Id
where
    inst.ExpiryDate is not null
    and ent.DaysToExpiryNotificationStart is not null
    and convert(date, @currentSystemTime) >= convert(date, dateadd(day, -ent.DaysToExpiryNotificationStart, inst.ExpiryDate))

The properties are defined as follows in the entity classes:

public virtual DateTimeOffset? ExpiryDate { get; set; }
public virtual int? DaysToExpiryNotificationStart { get; set; }

I am using Fluent NHibernate to map these. Are manual queries via CreateQuery or CreateSQLQuery the only way to go? If there is an easier way to accomplish this task, I am open. Any help will be appreciated.

Thanks, Shawn

You could use Expression.Sql or combination of Expression.Ge with Projections.SqlFunction. See http://stackoverflow.com/questions/2936700/nhibernate-criteria-how-to-filter-on-combination-of-properties — kliszaq, Aug 07 '13 at 08:20

score 1 · Accepted Answer · edited May 23 '17 at 12:05

session.Query<Instances>()
    .Where(i => Datetime.Today >= i.ExpiryDate.AddDays(i.DaysToExpiryNotificationStart))

adddays is not yet natively supported but can be added quite easyly. see here to make AddDays work

the same slightly different can be done with QueryOver

session.QueryOver<Instances>()
    .Where(i => Datetime.Today >= i.ExpiryDate.AddDays(i.DaysToExpiryNotificationStart))

public static class QueryOverExtensions
{
    public static void Register()
    {
        ExpressionProcessor.RegisterCustomProjection(() => default(DatTime).AddDays(1), QueryOverExtensions.ProcessAddDays);
    }

    private static IProjection ProcessAddDays(MethodCallExpression methodCallExpression)
    {
        IProjection property = ExpressionProcessor.FindMemberProjection(methodCallExpression.Arguments[0]).AsProjection();
        return (Projections.SqlFunction("addDays", NHibernateUtil.DateTime, NHibernateUtil.Int32, property));
    }
}

Note: I'm not sure if adddays is already defined as sql function. you might need to register one in the driver

The problem with the LINQ query is that you cannot set a lock mode (https://nhibernate.jira.com/browse/NH-2285), hence I needed it to go with criteria which makes the Where clause tricky as far as I know. Is it possible via criteria APIs? — Shawn, Aug 05 '13 at 15:57

Criteria for where predicate using columns from two tables and functions in NHibernate

1 Answers1