nhibernate cross table query optimization

Question

I have a query I've written with NHibernate's Criteria functionality and I want to optimize it. The query joins 4 tables. The query works, but the generated SQL is returning all the columns for the 4 tables as opposed to just the information I want to return. I'm using SetResultTransformer on the query which shapes the returned data to an Individual, but not until after the larger sql is returned from the server.

Here's the NHibernate Criteria

        return session.CreateCriteria(typeof(Individual))
            .CreateAlias("ExternalIdentifiers", "ExternalIdentifier")
            .CreateAlias("ExternalIdentifier.ExternalIdentifierType", "ExternalIdentifierType")
            .CreateAlias("ExternalIdentifierType.DataSource", "Datasource")
            .Add(Restrictions.Eq("ExternalIdentifier.Text1", ExternalId))
            .Add(Restrictions.Eq("ExternalIdentifierType.Code", ExternalIdType))
            .Add(Restrictions.Eq("Datasource.Code", DataSourceCode))
            .SetResultTransformer(new NHibernate.Transform.RootEntityResultTransformer());

And the generated sql (from NHProfiler) is

SELECT (all columns from all joined tables) FROM INDIVIDUAL this_ inner join EXTERNAL_ID externalid1_ on this_.INDIVIDUAL_GUID = externalid1_.GENERIC_GUID inner join EXTERNAL_ID_TYPE externalid2_ on externalid1_.EXTERNAL_ID_TYPE_GUID = externalid2_.EXTERNAL_ID_TYPE_GUID inner join SYSTEM_SRC datasource3_ on externalid2_.SYSTEM_SRC_GUID = datasource3_.SYSTEM_SRC_GUID WHERE externalid1_.EXTERNAL_ID_TEXT_1 = 96800 /* @p0 / and externalid2.EXTERNAL_ID_TYPE_CODE = 'PATIENT' /* @p1 / and datasource3.SYSTEM_SRC_CODE = 'TOUCHPOINT' /* @p2 */

I only want the columns back from the Individual table. I could set a projection, but then I lose the Individual type.

I could also rewrite this with DetachedCriteria.

Are these my only options?

Answer 1

I had exactly the same question and asked one of NHibernate development team members directly, here is the answer I got:

with the Criteria API, the only thing you could do is to use a projection list and include all of the properties of the root entity... then you would need to use the AliasToBeanResultTransformer. Far from an optimal solution obviously

if you don't mind rewriting the query with hql, then you can do it very easily. an hql query that says "from MyEntity e join e.Association" will select both the entity columns as well as the association's columns (just like the problem you're having with criteria). But an hql query that says "select e from MyEntity e join e.Association" will only select the columns of e.