sql - Nhibernate QueryOver 中的嵌套选择

Question

在我搜索之后，我没有发现任何真正有用的东西可以帮助我弄清楚从哪里开始。

到目前为止，我只编写了相当简单的 nHibernate 查询，并且有一个相对复杂的 SQL 查询，我需要在 QueryOver (Icriteria) 中抽出，或者如果证明过于繁琐，则使用 HQL。请在下面找到查询:

Select aliasTable1.Id, AliasTable1.Time, 
       AlaisTable2.Version,
       (select top 1 secondAliasTable1.Time
          from Table1 secondAliasTable1
          where aliasTable1.Time < secondAliasTable1.Time
            AND secondAliasTable1.Time < (SELECT Top 1 thirdAliasTable1.Time from Table1 thirdAliasTable1 
                                            where thirdAliasTable1.Name = 'stringValue1'
                                              AND thirdAliasTable1.Id = aliasTable1.Id
                                              AND thirdAliasTable1.Time > aliasTable1.Time
                                            ORDER By thirdAliasTable1.Time)
            AND secondAliasTable1.Name = 'stringValue2'
            AND secondAliasTable1.Id = aliasTable1.Id
            ORDER BY secondAliasTable1.Time) As 'Endtime'
  from Table1 aliasTable1
    INNER JOIN Table2 aliasTable2 on AliasTable2.Id = aliasTable1.table2Id
  where alaisTable1.Name = 'stringValue2'
  ORDER BY alaisTable1.Time

我在转换中遇到了这个查询真的碰壁了，所以我希望至少能得到一个让我继续前进的起点，如果不是一个完整的答案!

干杯

Answer 1

这将稍微取决于您的映射，但这样的事情应该可以工作:

Table1 aliasTable1 = null, secondAliasTable1 = null, thirdAliasTable1 = null;
Table2 aliasTable2 = null;

var result = session.QueryOver<Table1>(() => aliasTable1)
    .Where(p => p.Name == "stringValue1")
    .JoinQueryOver(p => p.Table2, () => aliasTable2)
    .SelectList(list => list
        .Select(() => aliasTable1.Id)
        .Select(() => aliasTable1.Time)
        .Select(() => aliasTable2.Version)
        .SelectSubQuery(
            QueryOver.Of<Table1>(() => secondAliasTable1)
                .Where(() => aliasTable1.Time < secondAliasTable1.Time)
                .WithSubquery.Where(() => secondAliasTable1.Time <
                    QueryOver.Of<Table1>(() => thirdAliasTable1)
                        .Where(() => thirdAliasTable1.Name == "stringValue1")
                        .And(() => thirdAliasTable1.Id == aliasTable1.Id)
                        .And(() => thirdAliasTable1.Time > aliasTable1.Time)
                        .SelectList(inner => inner
                            .Select(() => thirdAliasTable1.Time))                            
                        .OrderBy(() => thirdAliasTable1.Time).Asc()
                        .Take(1)
                        .As<DateTime>())
                .And(() => secondAliasTable1.Name == "stringValue2")
                .And(() => secondAliasTable1.Id == aliasTable1.Id)
                .SelectList(third => third
                    .Select(() => secondAliasTable1.Time))
                .OrderBy(() => secondAliasTable1.Time).Asc()
                .Take(1)))
    .OrderBy(() => aliasTable1.Time).Asc()
    .List<object[]>();

这会生成如下所示的 SQL:

SELECT this_.Id                            as y0_,
       this_.Time                          as y1_,
       aliastable1_.Version                as y2_,
       (SELECT TOP (1 /* @p0 */) this_0_.Time as y0_
        FROM   [Table1] this_0_
        WHERE  this_.Time < this_0_.Time
               and this_0_.Time < (SELECT TOP (1 /* @p1 */) this_0_0_.Time as y0_
                                             FROM   [Table1] this_0_0_
                                             WHERE  this_0_0_.Name = 'stringValue1' /* @p2 */
                                                    and this_0_0_.Id = this_.Id
                                                    and this_0_0_.Time > this_.Time
                                             ORDER  BY this_0_0_.Time asc)
               and this_0_.Name = 'stringValue2' /* @p3 */
               and this_0_.Id = this_.Id
        ORDER  BY this_0_.Time asc) as y3_
FROM   [Table1] this_
       inner join [Table2] aliastable1_
         on this_.Id = aliastable1_.Table1Id
WHERE  this_.Name = 'stringValue1' /* @p4 */
ORDER  BY this_.Time asc

而不是 .List<object[]>()您还可以投影到您选择的 DTO(使用 TransformUsing )。如果这看起来势不可挡，我强烈建议打破每个分离的 QueryOver进入它自己的变量，然后从主查询中引用它们。

更新:如果你想使用 .TransformUsing ，您需要创建一个 null结果对象和使用 .WithAlias() :

Table1 aliasTable1 = null, secondAliasTable1 = null, thirdAliasTable1 = null;
Table2 aliasTable2 = null;
MyDTO dto = null;

var result = session.QueryOver<Table1>(() => aliasTable1)
    .Where(p => p.Name == "stringValue1")
    .JoinQueryOver(p => p.Table2, () => aliasTable2)
    .SelectList(list => list
        .Select(() => aliasTable1.Id).WithAlias(() => dto.Id)
        .Select(() => aliasTable1.Time).WithAlias(() => dto.Time)
        .Select(() => aliasTable2.Version).WithAlias(() => dto.Version)
        .SelectSubQuery(
            QueryOver.Of<Table1>(() => secondAliasTable1)
                .Where(() => aliasTable1.Time < secondAliasTable1.Time)
                .WithSubquery.Where(() => secondAliasTable1.Time <
                    QueryOver.Of<Table1>(() => thirdAliasTable1)
                        .Where(() => thirdAliasTable1.Name == "stringValue1")
                        .And(() => thirdAliasTable1.Id == aliasTable1.Id)
                        .And(() => thirdAliasTable1.Time > aliasTable1.Time)
                        .SelectList(inner => inner
                            .Select(() => thirdAliasTable1.Time))                            
                        .OrderBy(() => thirdAliasTable1.Time).Asc()
                        .Take(1)
                        .As<DateTime>())
                .And(() => secondAliasTable1.Name == "stringValue2")
                .And(() => secondAliasTable1.Id == aliasTable1.Id)
                .SelectList(third => third
                    .Select(() => secondAliasTable1.Time))
                .OrderBy(() => secondAliasTable1.Time).Asc()
                .Take(1)).WithAlias(() => dto.Time2))
    .OrderBy(() => aliasTable1.Time).Asc()
    .TransformUsing(Transformers.AliasToBean<MyDTO>())
    .List<MyDTO>();