further optimizing NHibernate CreateMultiQuery usage for eager fetching

Question

CreateMultiQuery is recommended when we want to eagerly load entity with its subentities with a single roundtrip - less columns returned as we don't have X*Y*Z*T but rather X, X*Y, X*Z, X*T. This is working well for me, but it's not optimal as if X is a table with many columns, I pull a lot of data from the DB. example:

const string query1 = "select f from Feature f where f.Id in (:fids)"; const string query2 = "select f from Feature f left join fetch f.SprintAssignment where f.Id in (:fids)"; const string query3 = "select f from Feature f left join fetch f.Tasks where f.Id in (:fids)"; const string query4 = "select f from Feature f left join fetch f.Tags where f.Id in (:fids)"; const string query5 = "select f from Feature f inner join fetch f.Project where f.Id in (:fids)";

Now, Feature table is pretty big (around 20 fields, including FK to other tables). this means that for query2-5, I get huge replication of the data (the entire feature fields + join-table fields).

Obviously, we need the FeatureId so the Identity Map could resolve everything (the entire Feature object with all of its sub-entities), but why does it pull the rest?

Is there a way to optimize it so it will return only the FeatureId + the join-table columns?

Answer 1

No, there isn't

Answer 2

You must use a Projection on your query, and query the joined table based on the id projection. This way you only get the columns of the joined table. Here a small sample using Criteria in vb.net

' create the first query, and restrict it to ids
Dim dFilterTask As NHibernate.Criterion.DetachedCriteria = Nothing
dFilterTask = NHibernate.Criterion.DetachedCriteria.For(GetType(Task))
' add your criterias here
dFilterTask.Add(your criterias here)
dFilterTask.SetProjection(NHibernate.Criterion.Projections.Id)

' second query, select where the task id is in your first query
Dim dFilterTaskJoin As NHibernate.Criterion.DetachedCriteria = Nothing
dFilterTaskJoin = NHibernate.Criterion.DetachedCriteria.For(GetType(TaskJoinedClass))
dFilterTaskJoin.Add(NHibernate.Criterion.Subqueries.PropertyIn("TaskId", dFilterTask))