2

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?

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
maciusik
  • 103
  • 1
  • 1
  • 9

1 Answers1

3

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

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • I knew about it but. Problem is when you put DetachedCriteria innerSelect =DetachedCriteria.For(typeof(MyEntity)).SetProjectionList().Add(Projections.Max("ID")).Add(Projections.GroupProperty("prop1")); You not be able to do Subqueries.PropertyIn("ID", innerSelect) becouse you will get sql query like this: select * from dbo.table1 where Id in ( select max(id), prop1 as id from dbo.table1 group by prop1 ) – maciusik May 20 '13 at 18:20
  • Sorry, I had put return before I finished my comment. – maciusik May 20 '13 at 18:24
  • I've just extended the answer with the GROUP BY statement on the inner select. Will that help? – Radim Köhler May 20 '13 at 18:41
  • Okey:) As you can see on my first post I put a query which I want create in nhibernate. You gave me an example with max clasue. Ok, I know thats kind of situation will work corectly but if you grouped by few more properties and took only max of id property using projection it would be a problem. I've done it query like this: – maciusik May 20 '13 at 18:46
  • DetachedCriteria innerSelect =DetachedCriteria.For(typeof(MyEntity)).SetProjectionList().Add(Projections.Max(‌​"ID")).Add(Projections.GroupProperty("prop1").Add(Projections.GroupProperty("prop2").Add(Projections.GroupProperty("prop3")); session.CreateCriteria(typeof(MyEntity)) .Add(Subqueries.PropertyIn("ID", innerSelect)); .list(); and it does't work becouse in Subqueries.PropertyIn("ID", innerSelect)); its more than one property. Maybe you know about some tricks?:) – maciusik May 20 '13 at 18:46
  • I prepared example in sql: Declare at table table (id int, name varchar(128),surName varchar(128)) insert into at table select 1,'a','b' union select 2,'a','b' union select 3,'a','b' union select 4,'a','b' union select 6,'y','z' union select 7,'y','z' union select 8,'y','z' union select 8,'y','z' select * from at table where id in ( select max(id) as id, name from @table group by name,surName ) Msg 116, Level 16, State 1, Line 23 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. – maciusik May 20 '13 at 18:47
  • I've adjusted projection, it should work. NHibernate has the answer for anything ;) – Radim Köhler May 21 '13 at 04:25