NHibernate QueryOver SQLFunction 在 where 子句中 [英] NHibernate QueryOver SQLFunction in where clause

问题描述

我想查询一个包含多行的表，每行都有一个 timestamp，数据以十分钟为间隔.我想找到任何丢失数据的开头，即没有 timestamp 等于下一个十分钟间隔的地方，如下所示:

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]

到目前为止，我有这个，但我看不到如何构建查询以让我执行 b.[timestamp] = dateadd(mi, 10, a.[timestamp]) in上面的查询:

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

我无法理解对 sqlfunction 部分的限制 - Nhibernate 只是不允许我比较 sqlfunction 和我的时间戳.

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...

亲切的问候

推荐答案

你在正确的轨道上.您需要使用 Restrictions.EqProperty 而不是 Restrictions.Eq 因为您正在比较两个投影而不是投影和常量值.

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.

此外，您可以使用 Expression 来访问内部查询的 TimeStamp 属性，而不是使用字符串.

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

以下代码适用于 Sql Server 2008，但可能需要对其他数据库引擎进行一些调整:

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

应该生成以下 SQL(至少在 Sql Server 2008 上):

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)

这篇关于NHibernate QueryOver SQLFunction 在 where 子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！