ICriteria and association fetching

Jensegutt · **Joined:** Tue Jan 17, 2006 5:00 am **Posts:** 16

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 1.2 beta 3

I use ICriteria for almost all of my querying but have come across a situation where a wonder whether this is as designed...

When querying using associations it always seems like the query selects all columns for both the base table and the association(s) table(s) when often all I want is the columns of the base table and only use the associations to limit the number of rows in the result.

I have tried to use the Criteria.SetFetchMode(association_Objekt, FetchMode.Lazy) but this doesn't seem to have any effect... am I doing it wrong or this a known issue...?

Some enlightenment on the issue would be appreciated :-)

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

I've been wondering this too. I want to use an association to tighten my query criteria but am not interested in the actual associated values, so this seems like a bit of a non-trivial performance hit for data I don't need.

Is this normal? I've seen this is the behavior for 1.0.4 too.

Symon.

mnichols · **Joined:** Mon Aug 21, 2006 9:18 am **Posts:** 179

If you haven't already,look into using Projections (not supported in 1.0.4 I think but is in 1.2) and Subqueries as part of your Criteria (or DetachedCriteria) usage.

I won't show all the classes in this query, but it should be obvious how the first three DetachedCriteria are used as subqueries (and only the necessary columns are brought in to filter the results) with the final DetachedCriteria using those subqueries to find the results.

Code:

                DetachedCriteria selfCrit = DetachedCriteria.For(typeof(Contact), "self")
                    .SetProjection(Projections.Property("Id"))
                    .Add(Expression.And(Property.ForName("Id").Eq(adot_Main.Id),
                                        Property.ForName("Id").EqProperty("agency.Id")));


                DetachedCriteria parentCrit = DetachedCriteria.For(typeof(Contact), "parentCon")
                    .SetProjection(Projections.Property("Id"))
                    .Add(Property.ForName("Id").Eq(adot_Main.Id))
                    .CreateCriteria("Parent", "parent")
                    .SetProjection(Projections.Property("Id"))
                    .Add(Property.ForName("Id").EqProperty("agency.Id"));


                DetachedCriteria associationCrit = DetachedCriteria.For(typeof(Contact), "assCon")
                    .SetProjection(Projections.Property("Id"))
                    .Add(Property.ForName("Id").Eq(adot_Main.Id))
                    .CreateCriteria("Associations", "ass")
                    .SetProjection(Projections.Property("Id"))
                    .Add(Property.ForName("Id").EqProperty("agency.Id"));


                DetachedCriteria specByContactCrit =
                    DetachedCriteria.For(typeof(SieveAnalysisDataSpecification), "spec")
                        .Add(Expression.Eq("MaterialCode", pb))
                        .CreateCriteria("Agency", "agency")
                        .Add(Expression.Disjunction().Add(
                                 Subqueries.Exists(selfCrit)).Add(Subqueries.Exists(parentCrit)).Add(
                                 Subqueries.Exists(associationCrit)));

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

In my case that's not possible since I'm still using the 1.0.x codebase and can't upgrade at this stage.

I would have thought that applying an alias or subcriteria would actually be for tightening the object selection criteria rather than increasing the number of associated objects fetched, and that specifying a fetch mode on subcriteria would allow you to choose whether to fetch them or not. Unfortunately it doesn't appear to be working that way.

Bug or feature?

Symon.