NHibernate Criteria Query: deal with partitions

Question

I just started learning NHibernate Criteria query. now get into this join table and partition problem.

Background:

1.Two Object models:

Project, User

2.Model properties:

Project:

• ID (int)
• UpdateDate (DateTime)
• Status (String)
• User (User)

User:

• ID (int)
• Name (string)

3.Relationship

One project is updated by a user at a time, after each update, a new project instance will be created with the current UpdateDate, Status, User.

What I want to do:

1. Group projects by User.ID

2. Order by UpdateDate

3. Get the first record of each Group from 1&2

4. Check if this project's Status != 'Deleted'

5. If 4 passes, then put this project into result list

Problem:

1. I am restricted to using Criteria at this moment

2. I know how to do simple queries, e.g.:

   ICriteria projectCriteria = Session.CreateCriteria();

   projectCriteria.Add(Restrictions.Not("Status", "Deleted"));

   projectCriteria.AddOrder(Order.Desc("UpdateDate"));

but having a hard time for partitions and table joins using Criteria API.

wondering if anybody who knows how to do this can help me out.

thanks!

The SQL query I tried in database:

WITH PartitionProject AS 
(
    SELECT   *, ROW_NUMBER() OVER(PARTITION BY UserFk ORDER BY UpdateDate DESC) AS RowNumber
    FROM     Projects
)
SELECT * 
FROM PartitionProject 
WHERE RowNumber = 1 and (ProjectStatus != 'Deleted')

Answer 1

I think that Nhibernate does not support Row_Number() partition but you can use Projections.SqlProjection

You can handle your query dividing it in 2 different steps:

first step query to db using Projections.SqlProjection that return a list of object

second step iterate that list to extract what you need