NHibernate: Join a subquery (uses aggregation) with a table

Question

I have a table containing records of machine movements between multiple locations.

As I only show the latest of them in a list, i have read only the data rows with the latest record date per machine until now in sql.

Select * 
From records
INNER JOIN
    (Select max(processdate) as maxDate, machinetag, machineno 
    from records
    group by machinetag, machineno ) as lrcd
on records.machineTag = lrcd.machineTag and records.machineno = lrcd.machineno 
and records.processDate = lrcd.maxDate

I have read many posts but I did not find any solution.

How can I implement this scenario with queryover?

Thany you very much in advance. Sascha

Answer 1

Now I can offer a (probably) working solution:

Dim r As Record = Nothing

Dim subquery = QueryOver.Of(Of Record)() _
                                         .Where(Function(rec) rec.R_MachineTag = r.R_MachineTag) _
                                          .And(Function(rec) rec.R_MachineNo = r.R_MachineNo) _
                                          .Select(Projections.Max(Of Record)(Function(rec) rec.R_ProcessDate)).Take(1)

Dim query = session.QueryOver(Function() r) _
                        .WithSubquery.WhereProperty(Function(rec) rec.R_ProcessDate).Eq(subquery) _
                        .List()

Thanks to cbp for giving me the hint ;)