views:

220

answers:

0

I have a table GL that contains GLCode. I need to get a list of unique GLCodes, but get all the other columns. The following SQL produces 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>();