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?
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
