NHibernate query over comparing two sub queries

Question

How do you I combine two sub queries with queryover with WithSubQuery ? I want something like below (exact syntax doesn't matter):

 query.WithSubquery.WhereValue(QueryOver.Of<Child>()
                                  .Where(m => m.Parent.Id == paretAlias.Id)
                                  .Select(Projections.Max("SomeProp")))
                                  .Lt(QueryOver.Of<Child>(() => childAlias)
                                  .Where(m => childAlias.Id == parentAlias.Id)
                                  .Select(Projections.Max("SomeOtherProp")));

I can't see any methods of WithSubquery that allows me to compare two methods. It has

Where : takes a lambda

WhereProperty : takes a property compares with a sub query

WhereValue : takes a value compares with a sub query

WhereExists : takes a query.

Basically I want a method with that takes sub query and compares with another sub query

A sample output query in the sql would be :

 select * from Parent inner join child on parent.id = child.parentid where 
     (select max(SomeProp) from child where child.parentid = parent.id) >   (select max(SomeOtherProp) from child where child.parentid = parent.id)

Could you provide an example of what you would like the generated SQL to look like please? — Mark Perry, Jul 04 '11 at 08:17

score 1 · Answer 1 · edited May 23 '17 at 12:04

I think you should be able to resolve your problem by slightly modifying the sql-query:

SELECT p.* FROM [Parent] as p
WHERE EXISTS
(
    SELECT c.[parentId] as parentid FROM [Child] as c
    WHERE c.[parentid] = p.[id]
    GROUP BY c.[parentid] 
    HAVING MAX(c.[someProp]) < MAX(c.[someOtherProp])
)

If this returns the correct result set then you can implement it with QueryOver like so:

Parent p = null;
Child c = null;

var subquery = QueryOver.Of(() => c)
    .SelectList(list => list.SelectGroup(() => c.ParentId))
    .Where(Restrictions.LtProperty(
        Projections.Max(() => c.SomeProp), 
        Projections.Max(() => c.SomeOtherProp)))
    .And(Restrictions.EqProperty(
        Projections.Property(() => c.ParentId), 
        Projections.Property(() => p.Id)));

var query = QueryOver.Of(() => p)
    .WithSubquery.WhereExists(subquery);

IList<Parent> resutl = Session.CreateQuery(query);

I've already replied on a similar question, there is also a Criteria API version of the above query:

Selecting on Sub Queries in NHibernate with Critieria API

I hope this helps, cheers!

NHibernate query over comparing two sub queries

1 Answers1