Re: Stuck on how to use this SQL in nHibernate

[NeoDarque]

Hi David,

You have some odd restriction in your innermost subquery that I don't understand

AND thirdAliasTable1.Id = aliasTable1.Id
AND thirdAliasTable1.Time > aliasTable1.Time

first you're filtering so that thirdAliasTable1 must be the same entity as aliasTable1, then at the same time it should have the Time property larger than it self!?? 

Anyway, disregaring this, we'll go ahead anyway and reimplement the query. :)

I have no idea what your entities look like so I'll use some simplified ones here using the same properties you have in your query:

public class Table1 : Entity
{
public virtual string Name { get; set; }
public virtual int Time { get; set; }
public virtual int EndTime { get; set; }
public virtual ICollection<Table2> Children { get; set; }
public Table1() {
Children = new List<Table2>();
}
}
public class Table2 : Entity
{
public virtual string Name { get; set; }
public virtual int Version { get; set; }
public virtual Table1 Parent { get; set; }
}

And the code (using the QueryOver api) would be something like this:

Table1 aliasTable1 = null;
Table1 secondAliasTable1 = null;
Table1 thirdAliasTable1 = null;
Table2 aliasTable2 = null;
var subQuery2 = QueryOver.Of(() => thirdAliasTable1)
.Where(() => thirdAliasTable1.Name == "stringValue")
.And(() => thirdAliasTable1.Id == aliasTable1.Id)
.And(() => thirdAliasTable1.Time > aliasTable1.Time)
.OrderBy(() => thirdAliasTable1.Time).Asc
.SelectList(list => list.Select(() => thirdAliasTable1.Time))
.Take(1);
var subQuery1 = QueryOver.Of(() => secondAliasTable1)
.Where(() => aliasTable1.Time < secondAliasTable1.Time)
.WithSubquery.WhereProperty(() => secondAliasTable1.Time).Lt(subQuery2)
.And(() => secondAliasTable1.Name == "stringValue2")
.And(() => secondAliasTable1.Id == aliasTable1.Id)
.OrderBy(() => secondAliasTable1.Time).Asc
.SelectList(list => list.Select(() => secondAliasTable1.Time))
.Take(1);
var result = Session.QueryOver(() => aliasTable1)
.Inner.JoinAlias(() => aliasTable1.Children, () => aliasTable2)
.Where(() => aliasTable1.Name == "stringValue2")
.OrderBy(() => aliasTable1.Time).Asc
.SelectList(list => list
.Select(() => aliasTable1.Id).WithAlias(() => aliasTable1.Id)
.Select(() => aliasTable1.Time).WithAlias(() => aliasTable1.Time)
.Select(() => aliasTable2.Version).WithAlias(() => aliasTable2.Version)
.SelectSubQuery(subQuery1).WithAlias(() => aliasTable1.EndTime)
).TransformUsing(Transformers.AliasToBean<Table1>())
.List<Table1>();

And this is assuming that the result should be in the same format as the entity type Table1.

Cheers,

Ted