Using NHIbernate ICriteria , I created a DetachedCriteria that looked like so:
DetachedCriteria innerAssociate = DetachedCriteria.For(typeof (Contact))
.SetProjection(Projections.Property("Id"))
.Add(Property.ForName("Id").Eq(idToFind))
.SetProjection(Projections.Property("Id"))
.Add(Property.ForName("Id").EqProperty("associate.Id"));
DetachedCriteria parentSub = DetachedCriteria.For(typeof(ContactAssociation), "parentSubAss")
.SetProjection(Projections.Property("Id"))
.Add(Expression.Eq("AssociationType",ContactAssociationTypes.PARENT))
.CreateCriteria("Associate", "associate")
.Add(Property.ForName("Id").Eq(innerAssociate));
DetachedCriteria parentCrit = DetachedCriteria.For(typeof(Contact), "parentCon")
.CreateCriteria("ContactAssociations","association")
.Add(Subqueries.Exists(parentSub));
The interesting part of the SQL generated by this is this:
FROM Contact this_
inner join ContactAssociation associatio1_ on this_.Id=associatio1_.ContactId
left outer join Contact contact4_ on associatio1_.Associate_ContactId=contact4_.Id
WHERE exists
(SELECT this_0_.Id as y0_ FROM ContactAssociation this_0_
inner join Contact associate1_ on this_0_.Associate_ContactId=associate1_.Id
WHERE this_0_.AssociationType = ? and associate1_.Id =
(SELECT this_0_0_.Id as y0_ FROM Contact this_0_0_ WHERE this_0_0_.Id = ? and this_0_0_.Id = associate1_.Id))]
You can see that I have a nested subquery within a primary subquery. From what I have gathered about SqlCE, it should supported unlimited nested subqueries, so I am confused why the SQL generated by NHibernated would create this error:
System.Data.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 1,Token line offset = 1777,Token in error = SELECT ]
The snippet is is indicating it dislikes is this and associate1_.Id = (SELECT this_0_0_.Id as y0_ FROM Contact... from the second subquery.
I only show this to point out a potential failure for unit tests using NHibernate when, in fact, the sql is just fine.