NHibernate QueryOver SQLFunction in where clause

Question

I would like to query a table having multiple rows, each with a timestamp with data coming at ten minute intervals. I would like to find the beginning of any missing data, which is where there is not a timestamp equaling the next ten minute interval, like this:

select a.[timestamp]
from [table] as a
where not exists (select 1
                  from [table] as b
                  where a.[id] = b.[id] 

                    and b.[timestamp] = dateadd(mi, 10, a.[timestamp]))

order by a.[timestamp]

I have this so far, but I fail to see how to build the query to let me do the b.[timestamp] = dateadd(mi, 10, a.[timestamp]) in the query above:

Table tableAlias = null;

IList<DateTimeOffset> dateTimeOffsets = session.QueryOver(() => tableAlias)
.WithSubquery
.WhereNotExists(QueryOver.Of<Table>()
.Where(x => x.Id == tableAlias.Id)

.And(Restrictions.Eq(Projections.SqlFunction("addminutes",
                                             NHibernateUtil.DateTimeOffset,
                                             new[]
                                             {
                                               Projections.Property("Timestamp"),
                                               Projections.Constant(10)
                                             }),
                                             <insert timestamp property again here>))

.Select(Projections.Constant(1)))
.Select(x => x.Timestamp)
.List<DateTimeOffset>();

I can not get my head round the restriction on the sqlfuntion part - Nhibernate just won't let me do the comparison of the sqlfunction and my timestamp.

I hope I am on the right track with the code above, but please correct me if I'm totally off with my attempt at solving this...

Kind regards

Answer 1

You are on the right track. You need to use Restrictions.EqProperty instead of Restrictions.Eq since you are comparing two projections and not a projection and a constant value.

Also, you can use an Expression to access the TimeStamp property of the inner query instead of using a string.

The following code works for me on Sql Server 2008, but it may require a bit of tweaking for other database engines:

Table a = null;

session.QueryOver<Table>(() => a)
    .WithSubquery
    .WhereNotExists(
        QueryOver.Of<Table>()
            .Where(t => t.Id == a.Id)
            .And(Restrictions.EqProperty(
                Projections.SqlFunction(
                    "addminutes",
                    NHibernateUtil.DateTimeOffset,
                    Projections.Constant(10),
                    Projections.Property(() => a.TimeStamp)),
                Projections.Property(() => a.TimeStamp)))
            .Select(Projections.Constant(1)))
.Select(t => t.TimeStamp)
.List<DateTimeOffset>();

Which should generate the following SQL (at least on Sql Server 2008):

SELECT this_.TimeStamp as y0_
FROM   [Table] this_
WHERE  not exists (SELECT 1 /* @p0 */ as y0_
                   FROM   [Table] this_0_
                   WHERE  this_0_.Id = this_.Id
                          and dateadd(minute, 10 /* @p1 */, this_.TimeStamp) = this_.TimeStamp)