CODEWITHSUNDEEP
nhibernatedistinct
Alistair
Alistair

Reputation: 1979

NHibernate: Get distinct results based on a column, but retrieve all columns

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>();

Upvotes: 6

Views: 2171

Answers (1)

Daniel Schilling
Daniel Schilling

Reputation: 4967

Even though NHibernate doesn't have a way to exclude GLCode from the subquery's result columns, it is still possible to create a query that does the job. Use a correlated EXISTS subquery instead of IN. The SQL we're shooting for is like this:

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's the NHibernate query:

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>();

Upvotes: 3

Related Questions