How do I use the Criteria API to get the equivalent SQL in NHibernate
for this situation?
I have an entity with a composite primary key (a, b) and want to
achieve the same result as
select * from Entity this_, (select a, b, from Entity where
<irrelevant>) subquery_
where this_.a=subquery_.a and this_.b=subquery_.b;
The subquery will be complex and is necessary for performance reasons
and to work around the issue of paging and duplicates (described well
here http://julianjelfs.wordpress.com/tag/nhibernate/). Right now the
subquery is written as a DetatchedCriteria that projects the primary
keys. All of this detail is irrelevant however. The end result is I
would like to fetch Entity's from a projection that contains all of
the relevant composite keys.
If the entity's key were NOT composite I could simply do this:
DetatchedCriteria subQuery = new DetatchedCriteria<Entity>();
subquery.Add(Restrictions.Eq...); // etc.
subquery.SetProjection(Projections.Distinct(Projections.Id()));
ICriteria mainQuery = MyCurrentSession.CreateCriteria<Entity>();
mainQuery.Add(Subqueries.PropertyIn("a", subQuery)); // supposing only
property "a" was the primary key
mainQuery.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);
var resultSet = mainQuery.List<Entity>;
and obtain the desired effect.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
