NHibernate Query/QueryOver with DateTime Comparison

Question

I have this QueryOver method:

public IEnumerable<TrackedReportData> GetUnused(TimeSpan cacheExpiration)
{
    return Session
        .QueryOver<TrackedReportData>()
        .Where(t => t.CacheSize > 0)
        // TODO: NHibernate doesn't support DateTime comparison out-of-box.
        //.Where(t => t.CacheSize > 0 && !t.IsRecentlyAccessed(cacheExpiration))
        .List()
        .Where(t => !t.IsRecentlyAccessed(cacheExpiration));
}

Currently, I am filtering my collection outside of SQL. Not so good for performance.

My IsRecentlyAccessed method looks like:

/// <summary>
/// Returns true if the cacheMeta has been accessed within the last `cacheExpiration`
/// number of milliseconds.
/// </summary>
public bool IsRecentlyAccessed(TimeSpan cacheExpiration)
{
    if (!LastAccessDate.HasValue)
        return false;

    return DateTime.Now.Subtract(LastAccessDate.Value) <= cacheExpiration;
}

NHibernate does not support DateTime.Subtract nor does it handle DateTime1 - DateTime2 very easily.

I've looked through resources online and everyone seems to be suggesting overly complex function expressions or extension methods. These solutions seem overkill when all I'm interested in doing is subtracting a value.

Are there no simple solutions? A manual approach to crafting the SQL query seems to be one's best bet, but it's a shame NHibernate is stopped in its tracks by something which appears to be trivial.

score 2 · Accepted Answer · answered May 19 '17 at 10:17

What about the following?

public IEnumerable<TrackedReportData> GetUnused(TimeSpan cacheExpiration)
{
    return Session
        .QueryOver<TrackedReportData>()
        .Where(t => t.CacheSize > 0)
        .Where(Restrictions.Or(
            Restrictions.On<TrackedReportData>(t => t.LastAccessDate).IsNull,
            Restrictions.Where<TrackedReportData>(
                t => t.LastAccessDate < DateTime.Now.Add(-cacheExpiration))))
        .List();
}

NHibernate knows how to compare dates. That is the date computation which is not supported in your case. Moving it on the parameter caused it to be evaluated at runtime instead of trying to translate it to SQL.

If you want to have date computations translated to SQL, see this answer to another question.

score 0 · Answer 2 · answered May 19 '17 at 08:06

The syntax is not great and this will take some tweaking, but you should be able to do something like this...

return Session
    .QueryOver<TrackedReportData>()
    .Where(t => t.CacheSize > 0)
    .Where(
        Restrictions.Lte(
            Projections.SqlFunction(
                new VarArgsSQLFunction("(", "-", ")"),
                NHibernateUtil.DateTime,
                Projections.Property(t => t.LastAccessDate),
                Projections.Constant(DateTime.Now)),
            cacheExpiration);

NHibernate Query/QueryOver with DateTime Comparison

2 Answers2