CODEWITHSUNDEEP
c#nhibernateormqueryover
Nico Pizzo
Nico Pizzo

Reputation: 105

nhibernate join on subquery

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(???)

The SQL statement should look something like this when it is done

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

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

EDIT: Can the same thing be done using an exist statement? This will allow us to use something like:

SomeClass classAlias = null
var 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.

Upvotes: 0

Views: 1460

Answers (1)

Nico Pizzo
Nico Pizzo

Reputation: 105

After a lot of trial and error I was able to get this working using WHERE NOT EXISTS. Hopefully this will help people with a similar problem.

Here is the code snippet that will return the latest version of a particular record using QueryOver:

SomeClass classAlias = null
var 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.WhereNotExists(subquery);

var results = query.List();

Upvotes: 1

Related Questions