I have classes that look like this
public class Agreement
{
public virtual string ArrgKey { get; set; }
public virtual string SubjectCode { get; set; }
// mapped as a HasMany to AgreementStateCountyRelation
public virtual IList<AgreementStateCountyRelation> StateCounties { get; set; }
}
public class AgreementStateCountyRelation
{
public virtual string ArrgKey { get; set; }
public virtual State State { get; set; } // State has a StateAbbr property
public virtual County County { get; set; }
}
The NHibernate mappings are straightforward using Fluent NHibernate.
I am trying to write a query that finds all Agreements pertaining to a State. I don't need the StateCounties collection's data at all except for use in the where clause. Using HQL, this query does exactly what I want (returns Agreements for TX and doesn't eager-load the StateCounties):
var list = session.CreateQuery("select a from Agreement a
join a.StateCounties sc
join sc.State s
where s.StateAbbr = ?")
.SetParameter(0, "TX")
.List<Agreement>();
The problem comes when I try to use the criteria API (I can't use HQL for this scenario). I thought that this would produce the same query as the HQL:
DetachedCriteria dc = DetachedCriteria.For<Agreement>("a");
dc.CreateAlias("a.StateCounties", "sc");
dc.CreateAlias("sc.State", "s");
dc.Add(Restrictions.Eq("s.StateAbbr", "TX"));
var list = dc.GetExecutableCriteria(session).List<Agreement>();
But all of the properties for State are also put in the select clause (I'm guessing because State is in the where clause). Also, the Counties are lazy-loaded as part of this same call (even though I never want or need them).
If I specify the projection list and set the ResultTransformer, then I get similar results to the HQL (with identical generated SQL):
DetachedCriteria dc = DetachedCriteria.For<Agreement>("a");
dc.CreateAlias("a.StateCounties", "sc");
dc.CreateAlias("sc.State", "s");
dc.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("ArrgKey"), "ArrgKey")
.Add(Projections.Property("SubjectCode"), "SubjectCode"));
dc.SetResultTransformer(Transformers.AliasToBean<Agreement>());
dc.Add(Restrictions.Eq("s.StateAbbr", "TX"));
var agreements = dc.GetExecutableCriteria(session).List<Agreement>();
The only difference is the StateCounties collection is always null instead of proxied, but that's even better since I don't want to accidentally lazy load them.
So, my question: Is there any way to tell NHibernate to exclude a property (StateCounties) in the select clause (projection list) as opposed to having to specify all of the other properties in the list? Something that says "this association only exists for use in a where clause." ExcludeFromProjectionList or DoNotPopulate or something I can put on the HasMany relationship in the mapping or in the criteria?
Edit: The collections are defined as lazy.
As a stripped-down example, this doesn't cause the collection to load:
DetachedCriteria dc = DetachedCriteria.For<Agreement>("a");
var agreements = dc.GetExecutableCriteria(session).List<Agreement>();
but this does:
DetachedCriteria dc = DetachedCriteria.For<Agreement>("a");
dc.CreateAlias("a.StateCounties", "sc");
dc.CreateAlias("sc.State", "s");
var agreements = dc.GetExecutableCriteria(session).List<Agreement>();
I just found this which appears to be the same issue (without a good solution):
https://forum.hibernate.org/viewtopic.php?t=959176
Another person with this problem (who unfortunately can't do any better than set the projection like I did):