Row_Number() with partition in Nhibernate using CreateCriteria

Question

I am stuck in a problem while creating a query in nhibernate in c#. Actually I have to create a criteria query for following sql statement

select fCompanyID,ROW_NUMBER() over( PARTITION BY fCompanyID order by fPropertyID)
from  tARCustomer

But it seems that nhibernate does't support Row_Number() partition by as till now I googled. I need this query functionality in nhibernate but I am not getting how to do it. If anyone did it befor then please let me know / help me to solve out this problem.

Thanks,

check this post stackoverflow.com/questions/12897943/…. I think this kind of RDBMS dependent instructions are not supported by NHibernate. — Agustin Meriles

Radim Köhler Radim Köhler · Accepted Answer · 2013-01-25T18:43:51

Well, NHibernate has solution for anything. This could be the Criteria syntax:

var list = session
  .CreateCriteria<Customer>()
  .SetProjection
  (
       Projections.SqlProjection
       (   
          "fCompanyID, ROW_NUMBER() over( PARTITION BY fCompanyID order by fPropertyID) as RowNumber"
          , new string[] {"fCompanyID", "RowNumber"}
          , new IType[] { NHibernate.NHibernateUtil.Int32, NHibernate.NHibernateUtil.Int32}
       )
  )
  .SetResultTransformer(Transformers.AliasToBean<ResultDTO>())
  .List<ResultDTO>()
;

And the ResultDTO like this:

public class ResultDTO
{
  public virtual int RowNumber { get; set; }
  public virtual int fCompanyID{ get; set; }
}

And now you can work with a list as a set of ResultDTO

Row_Number() with partition in Nhibernate using CreateCriteria

1 Answers