3

Is it possible to get NHibernate to generate a query similar to the following with HQL or Criteria API?

select
    *
from (
    select
       row_number() over ( partition by Column1 order by Column2 ) as RowNumber,
       T.*
    from
        MyTable T
)
where
    RowNumber = 1

I can get it to execute the inner select using the formula attribute, but I can't figure out a way to write a HQL or Criteria query that lets me wrap the inner select in the outer one.

Nathan Roe
  • 844
  • 1
  • 8
  • 22
  • 2
    When we need to be that particular in the form of the query we just use named queries to achieve it. – Cobusve Jun 03 '10 at 12:27
  • we came accroos with this problem, when paging large data sets. There is a bug with NHibernate. What we did was use a cusom SQL dialect. Maybe this might help? http://www.webdevbros.net/2010/11/11/nhibernate-returns-duplicate-results-on-paged-data-sets-work-around/ – Dai Bok Dec 09 '10 at 21:31
  • +1 for Cobusve for suggesting named queries. – odez213 Jun 24 '11 at 19:45

1 Answers1

-1

NHibernate 3 has the Linq to NHibernate provider integrated, so you can write queries for paging like:

Session.Query<Customer>().Skip(10).Take(10).ToList();

This should help.

Digbyswift
  • 10,310
  • 4
  • 38
  • 66