NHibernate-QueryOver条件出现在Have子句中的Where中，错误 [英] NHibernate - QueryOver criteria appearing in Where instead in Having clause, error

问题描述

我在QueryOver中有一个问题，在哪里使用分组依据，并且在where子句中有一些条件。想要在Have子句中移动一些带有SUM（）值的条件，但是每次它出现在Where子句中并导致错误时。 **错误** = *除非聚集在HAVING子句或选择列表中包含的子查询中，否则聚集可能不会出现在WHERE子句中，并且正在聚集的列是外部引用*

 联合结点= Restrictions.Conjunction（）; 
连接词hasconjun = Restrictions.Conjunction（）;添加< Vendor>（p => v.Name ==某些供应商）; 
 
连词。 
 haveconconjun.Add（Restrictions.Gt（
 Projections.Sum（Projections.Property（（）=> v.Payments），
 Convert.ToDouble（SomeInvoice.Value））））; 
 
 var reportModels = 
 Session.QueryOver< Vendor>（（）=> v）
 .Where（conjunction）
 .Where（havingconjunjun）
 .SelectList（列表=>列表
 .SelectGroup（（）=> v.Number）.WithAlias（（）=> vModel.VendorNumber）
 .SelectGroup（（）=> vtypeCode。代码）.WithAlias（（）=> vModel.VendorType）
 .SelectGroup（（）=> v.Name）.WithAlias（（）=> vModel.VendorName））
 .TransformUsing（ Transformers.AliasToBean< VendorAnalysisReportModel>（））
 .List< VendorAnalysisReportModel>（）; 
  

预期结果：

  SELECT 
 V.VENDORNUMBER，V.VENDORTYPE，V.VENDORNAME ，SUM（V.PAYMENTS）
来自供应商V 
 WHERE V.NAME =某些供应商 
组
由V.VENDORNUMBER，V.VENDORTYPE，V.VENDORNAME 
的总和（V.PAYMENTS）> somevalue 
  

立即获取：

 选择
 V.VENDORNUMBER，V.VENDORTYPE，V.VENDORNAME，SUM（V.PAYMENTS）
从VENDOR V 
 W .NAME =某些供应商和
 SUM（V.PAYMENTS）> somevalue 
 GROUP 
由V.VENDORNUMBER，V.VENDORTYPE，V.VENDORNAME 
  

解决方案

好吧，因为很多人在NHibernate中找不到解决方案，所以我用了一些简单的技巧来实现自己的结果，直到NHibernate修复它，我才能说出解决方案。 / p>

从具有删除条件并运行简单查询之后，它看起来像这样。

  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& gt;（））
 .List< VendorAnalysisReportModel>（）; 
 
 var vlst2 = 
（从reportModels中的供应商处按vendrs.VendorName升序选择供应商）
 .ToList< VendorAnalysisReportModel>（）。AsQueryable（）; 
  

然后可以在任意字段上放置任意数量的where子句。

  vlst2 = vlst2.Where（p => p.OutstandingComm> Convert.ToDecimal（toDateComAmount.Value））; 
 
 vlst2 = vlst2.Where（p => p.ToDateOrders< Convert.ToDecimal（toDateOrdAmount.Value））; 
  

我的问题已解决，复杂的报告已成功运行，在其他查询中我们也这样做

QF

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

解决方案

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

这篇关于NHibernate-QueryOver条件出现在Have子句中的Where中，错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！