NHibernate - CreateSqlQuery returns only one row

Question

I am new to nhibernate... i have created a an SQL query (hits an Oracle database), that returns custom result set like this...

I try to map these results into a custom object like this...(this object is not a persistant object and does not contain a nhibernate mapping file)

The problem is that when i execute this sql query in the Oracle database it gives me all 4 records, however if when i execute this from the code , i get only one record (result as only one item).

Am i doing anythign wrong here?

Answer 1

Enable sql logging in NHibernate and check the question actually being executed.

Try setting projections so that transformer knows how to map the data.

Answer 2

Its your Id column, well the lack of it! NHibernate cannot work out what is a unique row in your ResultSet. Therefore NHibernate only populates the first level cache once as it sees ALL four rows as duplicates.

You have two options:-

1. in your view return a unique id which is a unique Id although that judging by your schema that does not look possible OR

2. You need to override equals and GetHashCode in your view map class, see this blogenter link description here for more info.