NHibernate - QueryOver criteria appearing in Where instead in Having clause, error

Question

I have a problem in QueryOver where using Group by and have some criteria in where clause. Want to move some criteria with SUM() values in Having clause but every time it appears in Where clause and result in error. **Error** ="*An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference*"

Conjunction conjunction = Restrictions.Conjunction();
Conjunction havingconjun = Restrictions.Conjunction();

conjunction.Add<Vendor>(p => v.Name == "Some Vendor");
havingconjun.Add(Restrictions.Gt(
   Projections.Sum(Projections.Property(() => v.Payments),
   Convert.ToDouble(SomeInvoice.Value)));

var reportModels =
            Session.QueryOver<Vendor>(() => v)
    .Where(conjunction)
    .Where(havingconjun)
    .SelectList(list => list
                    .SelectGroup(() => v.Number).WithAlias(() => vModel.VendorNumber)
                    .SelectGroup(() => vtypeCode.Code).WithAlias(() => vModel.VendorType)
                    .SelectGroup(() => v.Name).WithAlias(() => vModel.VendorName))
             .TransformUsing(Transformers.AliasToBean<VendorAnalysisReportModel>())
             .List<VendorAnalysisReportModel>();

Expected Result:

SELECT 
    V.VENDORNUMBER, V.VENDORTYPE, V.VENDORNAME, SUM(V.PAYMENTS)
 FROM VENDOR V
    WHERE V.NAME = "Some Vendor"
 GROUP 
    BY V.VENDORNUMBER, V.VENDORTYPE, V.VENDORNAME
 HAVING SUM(V.PAYMENTS) > somevalue

Getting Now:

SELECT 
    V.VENDORNUMBER, V.VENDORTYPE, V.VENDORNAME, SUM(V.PAYMENTS)
 FROM VENDOR V
    WHERE V.NAME = "Some Vendor" AND
    SUM(V.PAYMENTS) > somevalue
 GROUP 
    BY V.VENDORNUMBER, V.VENDORTYPE, V.VENDORNAME

Misconceive

Please dont go for missing brackets or similar mistake. Actual query is quiet huge cannot fit here, so its a tiny replica of original problem query. – Misconceive

Sorry forgot one line in query .Select(Projections.Sum(Projections.Property(() => v.Payments))).WithAlias(() => vModel.Payments) but my concentration is on Where and Having clause. Thanks – Misconceive

I know it has been a while, but I came across this post because I have the same problem. Could you solve it? – Discourtesy

It seems to be an issue with no solution so far. I googled and tried different approaches, none of them worked. – Discourtesy

No but I found a solution which is being used by other developers also in my company, should I put this as an answer to this? ok Leave it. I removed havingconjun form query. Get the answer from reportmodal, and then used LINQ to apply my where clause as I got all SUM fields. – Misconceive

Continuing... from rpt in reportModels where sum1 > somevalue, dont look for syntax as just giving you for hint – Misconceive

Answer 1

Well as many people could not find the solution of this in NHibernate, then I used some simple trick to achieve my results which I could say a solution to this problem until NHibernate fix it.

After getting the removing criteria from having and running simple queryover it looked like this.

var reportModels =
            Session.QueryOver<Vendor>(() => v)
    .Where(conjunction)
    .SelectList(list => list
                    .SelectGroup(() => v.Number).WithAlias(() => vModel.VendorNumber)
                    .SelectGroup(() => vtypeCode.Code).WithAlias(() => vModel.VendorType)
                    .SelectGroup(() => v.Name).WithAlias(() => vModel.VendorName))
             .TransformUsing(Transformers.AliasToBean<VendorAnalysisReportModel>())
             .List<VendorAnalysisReportModel>();

var vlst2 =
                    (from vendrs in reportModels orderby vendrs.VendorName ascending select vendrs)
                        .ToList<VendorAnalysisReportModel>().AsQueryable();

and then you can put as many where clause as you want on any field.

vlst2 = vlst2.Where(p => p.OutstandingComm > Convert.ToDecimal(toDateComAmount.Value));

vlst2 = vlst2.Where(p => p.ToDateOrders < Convert.ToDecimal(toDateOrdAmount.Value));

My problem was solved and the complex report is running successfully and we are following the same this in other queries as well.

QF

Misconceive

I'm sorry, but this does not solve the problem. You execute the query, get the results with QueryOver (good), with no filters whatsoever (very bad) and then you filter the entire result set using linq (very very bad). Imagine doing this in a middle-size DB, I'm not talking about a big DB. I wonder how this could successfully solve your complex reports issue. – Discourtesy

I never said that no filters on Queryover, as many as possible to minimize the result set then when QueryOver limit is reach then you started filtering using LINQ and we closely observed the performance and found no issues even with million records. There are other things that we take care but its out of scope of this. – Misconceive