[nhusers] possible to join on subquery (QueryOver)?

[Nico Pizzo]

I am trying to do a join on a subquery to another table I have the
following entity:


public class SomeClass{
public virtual string KeyPart1 { get; set; }
public virtual string KeyPart2 { get; set; }
public virtual int VersionNo { get; set; }
public virtual string ClassProperty1 { get; set; }
public virtual string ClassProperty2 { get; set; }}


I then have the following query to get me the latest version of each record:

var subquery = QueryOver.Of<SomeClass>()
.SelectList(lst => lst
.SelectGroup(f => f.KeyPart1)
.SelectGroup(f => f.KeyPart2)
.SelectMax(f => f.VersionNo));


I am now trying to return the entire SomeClass for each of the results of
the subquery. So far I have something like this:

var query = QueryOver.Of<SomeClass>()
.WithSubquery.Where(???)


I would like to have the SQL statement look something like this:

SELECT cls.*
FROM SomeClass as cls
INNER JOIN
(SELECT KeyPart1, KeyPart2, MAX(VersionNo)
FROM SomeClass
GROUP BY KeyPart1, KeyPart2) as sub
ON sub.KeyPart1 = cls.KeyPart1 and sub.KeyPart2 = cls.KeyPart2 and sub.VersionNo = cls.VersionNo


I have also done some research around using an exist statement? This will
allow us to use something like:

SomeClass classAlias = nullvar subquery = QueryOver.Of<SomeClass>()
.SelectList(lst => lst
.SelectGroup(f => f.KeyPart1)
.SelectGroup(f => f.KeyPart2)
.SelectMax(f => f.VersionNo))
.Where(x => x.KeyPart1 == classAlias.KeyPart1)
.Where(x => x.KeyPart2 == classAlias.KeyPart2)
.Where(x => x.VersionNo == classAlias.VersionNo)
var query = Session.QueryOver(() => classAlias)
.WithSubQuery.WhereExists(subquery);

Which generates the following SQL statement:

SELECT *
FROM SomeClass cls
WHERE EXISTS
(SELECT KeyPart1, KeyPart2, MAX(VersionNo)
FROM SomeClass cls2
WHERE cls.KeyPart1 = cls2.KeyPart1 and cls.KeyPart2 = cls2.KeyPart2 and cls.VersionNo = cls2.VersionNo
GROUP BY KeyPart1, KeyPart2)

This however also brings back all versions, but I thought it would be
another good place to start.


Can someone help me return the entire SomeClass record for each highest
version?