[SOLVED] Select items from a group by where count is larger than 1

Select items from a group by where count is larger than 1

The following sample query:

select * from (
    select m.name, count(m.id) c from mytable m
        group by m.name) a
    where a.c > 1

How do I build that using NHibernate QueryOver?

It is easy to do the group by part:

MyTable alias = null;
Output dto = null;

var groupBy = await s.QueryOver<MyTable>(() => alias)
    .Select(
        Projections.ProjectionList()
        .Add(Projections.Group<MyTable>(p => p.Name).WithAlias(() => dto.Name))
        .Add(Projections.Count(() => alias.Id).WithAlias(() => dto.Count))
        )
.TransformUsing(NHibernate.Transform.Transformers.AliasToBean<Output>())
.ListAsync<Output>();

but I don't know how to add the where part to select only those items where there are more than one item per group by column (count(Id) > 1).

Solution

Output dto = null;
var count= Projections.Count<MyTable>(p => p.Id);
var groupBy = session.QueryOver<MyTable>()
    .SelectList(l => l
        .SelectGroup(p => p.Name).WithAlias(() => dto.Name)
        .Select(count).WithAlias(() => dto.Count)
    )
    .Where(Restrictions.Gt(count, 1))
    .TransformUsing(NHibernate.Transform.Transformers.AliasToBean<Output>())
    .ListAsync<Output>();