Q:

Nested Selects in Nhibernate QueryOver

After my search, I haven't found anything really useful in helping me figure out where to proceed.  
So far I have only written fairly simple nHibernate queries and have a relatively complex SQL one that I need to pump out in QueryOver (Icriteria) or if that proves too fiddly, HQL.  Please find the query below: 
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

I have really hit a wall with this query in the conversion, so I am hoping to get at least a starting point to get me going, if not a full answer! 
Cheers

A:

This will depend slightly on your mappings, but something like this should work:
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[]>();

This generates SQL that looks something like this:
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

Instead of .List<object[]>() you could also project to a DTO of your choice (using TransformUsing).  If this looks overwhelming, I would strongly recommend breaking each detached QueryOver into it's own variable and then referencing them from the main query. 
Update: If you want to use .TransformUsing, you need to create a null result object and use .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>();

