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

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 Que开发者_运维技巧ry).

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.

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 ...

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

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

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