I'm currently using NHibernate-1.2.0.Beta3-debug and have been trying to get subqueries to work. I came across a
patch for a bug in Hibernate (Java) which describes exactly the problem I'm seeing in NHibernate:
Joins in subqueries using DetachedCriteria do not work.
The Subquery
I can create a subquery and run it independently of the main query:
Code:
DetachedCriteria matchedPostcode = DetachedCriteria.For(typeof(Address), "addr");
matchedPostcode.Add(Expression.InsensitiveLike("addr.Postcode", filter.Postcode, MatchMode.Anywhere));
matchedPostcode.CreateAlias("addr.OrganisationAddressList", "organisationAddress");
matchedPostcode.CreateAlias("organisationAddress.Organisation", "org");
matchedPostcode.SetProjection(Projections.Property("org.Id"));
ICriteria c = matchedPostcode.GetExecutableCriteria(session);
c.List();
This generates the correct SQL:
Code:
SELECT
org2_.id as y0_
FROM qmis.Address this_
inner join qmis.OrganisationAddress organisati1_ on this_.id=organisati1_.addressId
inner join qmis.Organisation org2_ on organisati1_.organisationId=org2_.id
WHERE lower(this_.postcode) like @p0;
@p0 = '%gl50%'
The Parent QueryHowever, when I include the subquery in the parent query...
Code:
ICriteria query = session.CreateCriteria(typeof(Organisation));
query.Add(Subqueries.Eq("Id", matchedPostcode));
IList result = query.List();
...the SQL that it generates no longer includes the join conditions of the subquery:
Code:
SELECT
...
FROM qmis.Organisation this_
inner join qmis.OrganisationAddress organisati1_ on this_.id=organisati1_.organisationId
inner join qmis.Address addr2_ on organisati1_.addressId=addr2_.id
WHERE @p0 = (SELECT org2_.id as y0_ FROM qmis.Address this0__ WHERE lower(this0__.postcode) like @p1);
@p0 = 'Id', @p1 = '%gl50%'
Bug?
Can anyone confirm if this is the same bug that Hibernate had? If so, I'll create a JIRA case.
Login
FAQ
Search
