1

Is there anyway to do a join to a select statement using NHibernate (QueryOver if possible)?

Here is the SQL I'm hoping to generate

SELECT r.MaxReading, r.MeterId, m.PreviousHours
FROM 
 Meters m
 LEFT JOIN
   (SELECT Max(mr.ReadingHours) as MaxReading, mr.AssetMeterId
    FROM MeterReadings mr
    WHERE mr.MeterId IN(1,2)
    GROUP BY mr.MeterId) r
 ON m.Id=r.MeterId

I did find this post, but it looks like the answer was to build a different query. Which makes me think this may not be possible.

I already figured out how to build the subquery piece, so any answer can just use a simple query in it's place as an example. Thanks!

Community
  • 1
  • 1
sevenstripe
  • 635
  • 1
  • 6
  • 12
  • Please post the relevant parts of your class definitions and mappings, as the possible queries will highly depend on it. What is `mr.AssetMeterId` in your subquery ? It does not seem to appear in the group by and seems to be referred to as `r.MeterId` in the outer query – jbl Dec 12 '13 at 13:42

1 Answers1

0

As far as I know, it's impossible to join on a subquery in NHibernate since all joins must come from relations in your object model.

Bredstik
  • 626
  • 5
  • 13