nhibernate criteria projection results in inefficient queries

Question

Please see the following example which is a very simplified version of my code:

Dim Criteria = Session.CreateCriteria(Of Person)()
Criteria.SetProjection(Projections.Property("Car"))
return Criteria.List(Of Car)()

This works perfectly, however NHibernate 3.1 creates two queries to fetch the results. Something like:

SELECT CarId FROM Person WHERE blababla

and then for each row:

SELECT color, brand, wheels FROM Car WHERE CarId = ?

This is not very efficient so i tried:

Criteria.CreateAlias("Car", "Car")
Criteria.SetFetchMode("Car", NHibernate.FetchMode.Join)

Which does nothing. How can I force NHibernate to make a join on the first query, so I end up with one roundtrip to the MySql server?

Answer 1

I found a workaround using a subquery. This will work, but I think this still would be more efficient using a join so my original question still stands. My workaround:

var cars = s.CreateCriteria<Cars>()
    .Add(Subqueries.PropertyIn("Id",
        DetachedCriteria.For<Person>()
            .Add(Restrictions.Eq("Name","MyName"))
            .SetProjection(Projections.Property("Car.Id"))
        ))
    .List<Cars>();