Everything works great until I need to return the "Rank" and "Percentile" information for a given record. It can not be expressed using my object-oriented model. I have two problems:
1. I need to execute native sql query.
2. I need to map the query result back to an object. And the mapping is not based a physical table column

Please shed a light on me. Thank you in advance!

Build 1.2.0.Alpha1

The table is:
create table dbo.tblGamePlayHistory (
pk_gamePlayHistoryId int identity(1,1) not null,
Role nvarchar(100),
QuizScore float,
CaseScore float
)

The query I need to execute is:

Not really an answer, but the first thing that comes to my mind is: why put so much logic into one intricate query? Maybe there is a reason for it (performance?), but I would tend to let a class model do the job and keep your queries simple and generic.

May I recommend you the (N)Hibernate docs? Look for native queries. Also you can have an object constructed from your query, again look in the docs.

cheers,
radu

From my past experience, I don't think Ojbect Model can handle all the issues effectively. I think "Finding Rank for a record" is something database can handle more efficiently than Object Model.

I did read the nhibernate manual. I didn't find answer there. NHibernate requires to use Class name in native sql. I totall agree with that decision since that hides the actual table name in the native sql. What I look for is how to map the result (select rank, percentile from ....) back to the RankMapper class. As you can see, Rank is a calculated column. It does'n physically exist in any table. So does Percentile in class RankMapper.

public class RankMapper : MapperBase<int>
{
public RankMapper(int rank, int percentile)
{
_rank = rank;
_percentile = percentile;
}

private int _rank;
public virtual int Rank
{
get { return _rank; }
set { _rank = value; }
}

private int _percentile;
public virtual int Percentile
{
get { return _percentile; }
set { _percentile = value; }
}
}

The solution for now is that NHibernate execute the real-native-sql(use table name) and return a IDataReader. I am good for now. But I am still looking for a solution in a NHibernate way.

I just wonder how you guys handle the "find the rank for a record" type of problem using NHibernate. Think about just 8000 record in db.