NHibernate: How to use "IN" with CreateCriteria?

Question

I have a parent-child table relationship: Elements -(1 to n)-> ContentBlocks. Each ContentBlock row has: unique Id (Id), ElementId, Version, and then some less relevant fields. I'm trying to get all Content rows that have the highest Version number (and Id) on them.

I have this line of SQL that gives me what I want:

SELECT * FROM ContentBlocks WHERE Id IN 
    (SELECT MAX(Id) FROM ContentBlocks GROUP BY ElementId)

(this assumes that the latest version has the highest Id)

However, I can't figure out how to get it from NHibernate. This is the closest I've gotten:

var subquery = DetachedCriteria.For<ContentBlock>()
            .SetProjection( Projections.Max( "Id" ) )
            .SetProjection( Projections.GroupProperty( "ElementId" ) );

var query = session.CreateCriteria<ContentBlock>()
            .Add( Subqueries.PropertyIn( "Id", subquery ) ).List<ContentBlock>();

If I execute subquery, I get a list of the unique ElementIds in the table. Instead, I need the list of Ids of the newest ContentBlocks. If I reverse the order of the SetProjections on the subquery, I just get the max of ElementId.

I also tried this way:

        subquery = DetachedCriteria.For<ContentBlock>()
            .SetProjection(
                Projections.ProjectionList()
                    .Add( Projections.Max( "Id" ) )
                    .Add( Projections.GroupProperty( "ElementId" ) )
                );

but that gets me a list of arrays with 2 elements in it, Id & ElementId, which the CreateCriteria call can't process.

Is there a way to not include the Grouped Property "ElementId" in the results? Or specify that I want to return Max(Id) instead?

Answer 1

Not implemented yet.