Hi tindbaek,
NHibernate reference document under section 12.6 Projections, aggregation, grouping and 12.7 Detached Queries and Subqueries may be the area to look for. The combination of 12.6 & 12.7 should work for you.
Extract from the reference document page 110:
Code:
DetachedCriteria avgWeightForSex = DetachedCriteria.For(typeof(Cat), "cat2")
.SetProjection( Projections.Avg("Weight") )
.Add( Expression.EqProperty("cat2.Sex", "cat.Sex") );
session.CreateCriteria(typeof(Cat), "cat")
.Add( Subqueries.Gt("weight", avgWeightForSex) )
.List();
I don't know if this is correct but can be something on these lines:
Code:
DetachedCriteria latestFile = DetachedCriteria.For(typeof(ExportFile), "ex")
.SetProjection( Projections.Max("ExportFileID") )
.Add( Expression.Eq("ex.Status", 1)
.Add( Expression.EqProperty("ex.ExportFileId ", "res.ExportFileId ") )
.Add( Projections.GroupProperty("ExportFileId"));
session.CreateCriteria(typeof(ExportFile), "res")
.Add( Subqueries.Eq("ExportFileId ", latestFile ) )
.Add( Expression.In( "ExportFileGroupID", groupsToGet) )
.List();
Here the groupsToGet needs to be an object[] of IDs.
Hope this helps.