selecting only max clause without group by propert

Question

I have SQL query like this:

select * from dbo.table1 where Id in
(
    select max(id) as id from dbo.table1 group by prop1, prop2, prop3
)

I want to create NHibernate query which is be able to do this for me. I tried to use QueryOver but it doesn't work. Do you have any suggestions how to do it?

Answer 1

NHibernate supports even this kind of queries. Please, see more in documentation: 15.8. Detached queries and subqueries. We just have to split the query (as in your SQL snippet) into two parts:

• inner select
• the select with the IN clause

Let's assume, that the dbo.table1 in the Questin is mapped into MyEntity. To create inner select, let's use the DetachedCriteria

EDIT (extended with the Group by, SqlGroupProjection)

There is an extract of the SqlGroupProjection method:

A grouping SQL projection, specifying both select clause and group by clause fragments

// inner select
DetachedCriteria innerSelect = DetachedCriteria
    .For(typeof(MyEntity))
    .SetProjection(
        Projections.ProjectionList()
         .Add(
            Projections.SqlGroupProjection(
              " MAX(ID) ",               // SELECT ... max(ID) only
              " Prop1, Prop2, Prop3",    // GROUP BY ... property1, p2...
              new string[] {"ID"},       // could be empty, while not used for  
              new IType[] { NHibernate.NHibernateUtil.Int32 } // transformation
            )
         )
    ;

Note: I've provided even the last two paramters, but in this case they could be empty: new string[], new IType[] {}. These are used only for Transformation (materialization from data into entity). And this is not the case, we are just building inner select...

// the select with IN clause
var result = session.CreateCriteria(typeof(MyEntity))
    .Add(Subqueries.PropertyIn("ID", innerSelect))
    .List<MyEntity>();

Also related could be 15.7. Projections, aggregation and grouping