CODEWITHSUNDEEP
c#nhibernate
Stephen Locke
Stephen Locke

Reputation: 31

NHibernate - How to return a distinct list of column entries and the count of each entry

Is it possible to replicate the following SQL query in NHibernate?

SELECT Name, COUNT(Name)
FROM Table
GROUP BY Name

Unfortunately I'm not able to get NHibernate to execute this as a raw sql query either as it is not permitted by my current employer.

I've seen examples of returning a count of linked entities but not a count of data in the same table.

I've currently got this working by using two queries. One to get a distinct list of names and one to get the count for each name. I'd like to optimise this to a single database call.

Thanks in advance for any help you can give!

Upvotes: 0

Views: 445

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123881

We can do it like this:

// this would be our DTO for result
public class ResultDTO
{
    public virtual string Name { get; set; }
    public virtual int Count { get; set; }
}

This would be the query

// here we declare the DTO to be used for ALIASing
ResultDTO dto = null;

// here is our query
var result = session.QueryOver<Table>()
    .SelectList(l => l
        .SelectGroup(x => x.Name).WithAlias(() => dto.Name)
        .SelectCount(x => x.Name).WithAlias(() => dto.Count)
    )
    .TransformUsing(Transformers.AliasToBean<ResultDTO>())
    .List<ResultDTO>();

Upvotes: 1

Related Questions