NHibernate QueryOver Having clause

Question

I want to write such a query with QueryOver so that the result SQL will be similar to the following:

Select Bar, count(*) from Foo group by Bar having count(*) > 1

How can I do it ?

Doubtful

Answer 1

I think you would just use the Where method

Session.QueryOver<Foo>()
    .Select(Projections.GroupProperty(Projections.Property<Foo>(foo => foo.Bar)),
            Projections.Count<Foo>(f => f.Id))
    .Where(Restrictions.Gt(Projections.Count<Foo>(f => f.Id), 1));

Ceuta

Should be Projections.GroupProperty(Projections.Property<Foo>(foo => foo.Bar)) – Lugubrious

What is the difference here if we use Projections.Sum()? – Hitherto

Answer 2

The answer from Vadim is correct, just want to mention that it could be a challenge if one need to check the "having" condition against another database field.

For example the following SQL:

select Foo.Bar, COUNT(*) from Foo
group by Foo.Bar
having Foo.Bar <> COUNT(*)

Should be essentially created with QueryOver like this:

Session.QueryOver<Foo>()
    .Select(Projections.GroupProperty(Projections.Property<Foo>(foo => foo.Bar)),
            Projections.Count<Foo>(f => f.Id))
    .Where(Restrictions.NotEqProperty(Projections.Count<Foo>(f => f.Id), Projections.Property<Foo>(foo => foo.Bar)));

BUT unfortunately NHibernate produces the following invalid SQL (using where instead of having):

    select Foo.Bar, COUNT(*) from Foo
    group by Foo.Bar
    where Foo.Bar <> COUNT(*)

To overcome this problem I had to create the following inheritence:

    public class NonEqPropertyExpression : EqPropertyExpression
    {
        public NonEqPropertyExpression(IProjection lhsProjection, IProjection rhsProjection)
            : base(lhsProjection, rhsProjection)
        {
        }

        protected override string Op
        {
            get { return "<>"; }
        }
    }

and use my new class instead of standard NonEqProperty:

Session.QueryOver<Foo>()
    .Select(Projections.GroupProperty(Projections.Property<Foo>(foo => foo.Bar)),
            Projections.Count<Foo>(f => f.Id))
    .Where(new NonEqPropertyExpression(Projections.Count<Foo>(f => f.Id), Projections.Property<Foo>(foo => foo.Bar)));

In this case the produced SQL is correct.

Discontented

I have a similar issue posted in question. Not getting from another db but criteria appearing in where instead of having. Can you please take a look at #29566283 – Hitherto