NHibernate's ISQLQuery returns instances that are of an unexpected type.

Question

Hi all,

I'm using NHibernate 2.1.2.400, and I'm having an issue with a an ISQLQuery query.

The reason why I use an ISQLQuery here, is that this query uses a table for which I have no entity mapped in NHibernate.

The query looks like this:

ISQLQuery query = session.CreateSQLQuery (
    "select p.*, price.* " +
    "from prestation p left outer join prestationprice price on p.PrestationId = price.PrestationId " +
    "where p.Id IN ( select id from prestationregistry where ...");

'Prestationregistry' is the table that is not known by NHibernate (unmapped, so therefore the native SQL Query).

my code continues like this:

query.AddEntity ("p", typeof(Prestation));
query.AddJoin ("price", typeof(PrestationPrice));

query.SetResultTransformer (Transformers.DistinctRootEntity);

var result = query.List();

So far so good. I expect that I'm given a list of 'Prestation' instances as a result of this query, since I declared 'Prestation' as being the root-object that has to be returned by the AddEntity method. I also expect that the PrestationPrices for each Prestation are eagerly loaded by this query (hence the AddJoin method).

To my surprise, the List() method returns a collection of PrestationPrice instances instead of Prestation instances. How come ? Am I doing something wrong ? And if so, could you be so kind to tell me what I'm doing wrong ?

Edit: Additional Info:

When I debug, and put a watch on the 'query' instance, I can see that the queryReturns member of the query contains 2 items: - one NativeSqlQueryRootReturn instance who'se ReturnEntityName is 'Prestation' - one NativeSqlQueryJoinReturn

When I do not specify the 'DistinctRootEntity' result transformer, the query returns instances of 'Prestation' instead of PrestationPrice. However, it contains multiple copies of the same instance.

Answer 1

Try specifying the type to be returned in the List method:

var result = query.List<Prestation>();

Answer 2

I'm not sure this is the real cause of your problem, but you must enclose SQL aliases in braces, e.g.

select {p.*}, {price.*} 
from prestation p 
left outer join prestationprice price on p.PrestationId = price.PrestationId
where p.Id IN ( select id from prestationregistry where ...

Answer 3

I've solved the problem in a slightly different way. I didn't know that you could use an SQL expression as an ICriteria-criterion (thx to the NH-Users google-group), so I did it like this:

ICriteria crit = session.CreateCriteria (typeof(Prestation), "p");

crit.SetFetchMode ("p.Prices", FetchMode.Eager);

crit.Add (Expression.Sql ("{alias}.PrestationId = ( SELECT id FROM sometable WHERE ... "));

crit.SetResultTransformer (Transformers.DistinctRootEntity);

var result = crit.List<Prestation>();

Anyway, the question remains if the behaviour that I've got using the ISQLQuery is expected, or a bug ...

The NHUsers thread that I've started as well