NHibernate: Get individual column-based results, but get all columns

Question

I have a GL table containing a GLCode. I need to get a list of unique GLCodes, but get all the other columns. The following SQL gives the results I want.

select * from GL where GLId in (select Min(GLId) from GL group by GLCode ) 

Is there a way to do this using the criteria API?

This is my best attempt:

  var subQuery = DetachedCriteria.For<GL>(); subQuery .SetProjection(Projections.Property("GLCode")) .SetResultTransformer(new DistinctRootEntityResultTransformer()); return (List<GL>)currentSession .CreateCriteria(typeof(GL)) .Add(Subqueries.PropertyIn("GLCode", subQuery)) .List<GL>(); 

Answer 1

Despite the fact that NHibernate does not have the ability to exclude GLCode from the columns of the results of the subquery, you can still create a query that performs the task. Use the correlated EXISTS subquery instead of IN. The SQL we are removing is as follows:

 select query.* from GL query where exists ( select min(subquery.GLId) AS GLId, subquery.GLCode from GL subquery group by subquery.GLCode having min(subquery.GLId) = query.GLId); 

And here is the NHibernate request:

 var min = Projections.Min("GLId"); var subquery = DetachedCriteria.For<GL>("subquery") .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty("GLCode"), "GLCode") .Add(min, "GLId")) .Add(Restrictions.EqProperty(min, "query.GLId")); return session.CreateCriteria<GL>("query") .Add(Subqueries.Exists(subquery)) .List<GL>();