ToRowCountQuery似乎忽略了分组

本文介绍了ToRowCountQuery似乎忽略了分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试从常规查询创建行计数查询，但是结果SQL似乎缺少GROUP BY，导致计数错误.有谁知道我在做什么错.

I'm trying to create a rowcount-query from a regular query, but the resulting SQL seems to lack the GROUP BY resulting in a wrong count. Does anyone know what I'm doing wrong.

首先查询:

var query = Session.QueryOver<InkoopFactuurListItem>() .Where(i => i.KlantId == Klant.Id) .AndRestrictionOn(i => i.Status).IsIn(statussen) .SelectList(list => list .SelectGroup(h => h.Id).WithAlias(() => dto.Id) .SelectGroup(h => h.Banknummer).WithAlias(() => dto.Banknummer) .SelectGroup(h => h.CrediteurNaam).WithAlias(() => dto.CrediteurNaam) .SelectGroup(h => h.DienstType).WithAlias(() => dto.DienstType) .SelectGroup(h => h.DocumentId).WithAlias(() => dto.DocumentId) .SelectGroup(h => h.DocumentNaam).WithAlias(() => dto.DocumentNaam) .SelectGroup(h => h.Factuurbedrag).WithAlias(() => dto.Factuurbedrag) .SelectGroup(h => h.Klantnummer).WithAlias(() => dto.Klantnummer) .SelectGroup(h => h.Factuurbtw).WithAlias(() => dto.Factuurbtw) .SelectGroup(h => h.FactuurDatum).WithAlias(() => dto.FactuurDatum) .SelectGroup(h => h.Factuurnummer).WithAlias(() => dto.Factuurnummer) .SelectGroup(h => h.IMSNummer).WithAlias(() => dto.IMSNummer) .SelectGroup(h => h.KlantId).WithAlias(() => dto.KlantId) .SelectGroup(h => h.Soortfactuur).WithAlias(() => dto.Soortfactuur) .SelectGroup(h => h.Status).WithAlias(() => dto.Status) .SelectGroup(h => h.VerwerktOp).WithAlias(() => dto.VerwerktOp) .SelectMin(h => h.Van).WithAlias(() => dto.Van) .SelectMax(h => h.Tot).WithAlias(() => dto.Tot)) .TransformUsing(Transformers.AliasToBean<InkoopFactuurListItem>()); var rowcount = query.ToRowCountQuery().FutureValue<int>(); IEnumerable<InkoopFactuurListItem> results; if (command.Page > 0 && command.PageSize > 0) { results = query .Skip((command.Page - 1) * command.PageSize) .Take(command.PageSize).Future<InkoopFactuurListItem>(); } else { results = query .Take(command.PageSize) .Future<InkoopFactuurListItem>(); } count = rowcount.Value;

生成的SQL:

SELECT count(*) as y0_ FROM vwInkoopFactuurListItem this_ WHERE this_.KlantId = @p0 and this_.Status in (@p1, @p2, @p3, @p4, @p5); SELECT TOP (@p6) y0_, y1_, y2_, y3_, y4_, y5_, y6_, y7_, y8_, y9_, y10_, y11_, y12_, y13_, y14_, y15_, y16_, y17_ FROM ( SELECT this_.InkoopFactuurId as y0_, this_.Banknummer as y1_, this_.CrediteurNaam as y2_, this_.DienstType as y3_, this_.DocumentId as y4_, this_.DocumentNaam as y5_, this_.Factuurbedrag as y6_, this_.Klantnummer as y7_, this_.Factuurbtw as y8_, this_.FactuurDatum as y9_, this_.Factuurnummer as y10_, this_.IMSNummer as y11_, this_.KlantId as y12_, this_.Soortfactuur as y13_, this_.Status as y14_, this_.VerwerktOp as y15_, min(this_.Van) as y16_, max(this_.Tot) as y17_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM vwInkoopFactuurListItem this_ WHERE this_.KlantId = @p8 and this_.Status in (@p9, @p10, @p11, @p12, @p13) GROUP BY this_.InkoopFactuurId, this_.Banknummer, this_.CrediteurNaam, this_.DienstType, this_.DocumentId, this_.DocumentNaam, this_.Factuurbedrag, this_.Klantnummer, this_.Factuurbtw, this_.FactuurDatum, this_.Factuurnummer, this_.IMSNummer, this_.KlantId, this_.Soortfactuur, this_.Status, this_.VerwerktOp ) as query WHERE query.__hibernate_sort_row > @p7 ORDER BY query.__hibernate_sort_row; ;@p0 = 1 [Type: Int64 (0)], @p1 = 'OverigMatchingInkoop' [Type: String (255)], @p2 = 'OverigMatchingVerkoop' [Type: String (255)], @p3 = 'Overig' [Type: String (255)], @p4 = 'Geboekt' [Type: String (255)], @p5 = 'Geexporteerd' [Type: String (255)], @p6 = 10 [Type: Int32 (0)], @p7 = 284590 [Type: Int32 (0)], @p8 = 1 [Type: Int64 (0)], @p9 = 'OverigMatchingInkoop' [Type: String (255)], @p10 = 'OverigMatchingVerkoop' [Type: String (255)], @p11 = 'Overig' [Type: String (255)], @p12 = 'Geboekt' [Type: String (255)], @p13 = 'Geexporteerd' [Type: String (255)]

那么rowcount查询中的GROUP BY在哪里?

So where is the GROUP BY in the rowcount-query?

更新 事实证明，ToRowCountQuery从原始查询中剥离选择和分组.那么我该怎么做:

UPDATE As it turns out, ToRowCountQuery strips the selection and groupings from the original query. So how can I do something like this:

select count(*) from (... query ...)

Firo的答案似乎朝着正确的方向迈出了一步，但是我无法使用CountDistinct，因为我需要对所有字段进行计数，包括选择列表中所示的最小/最大字段.紧接着，CountDistinct不将IProjection作为参数:

The answer by Firo seems to be a step in the right direction, but I cannot use CountDistinct as I need the count over all fields, including the min/max fields as shown in the selectlist. Next to that, CountDistinct doesn't take an IProjection as argument:

// // Summary: // A distinct property value count public static CountProjection CountDistinct(Expression<Func<object>> expression); // // Summary: // A distinct property value count public static CountProjection CountDistinct<T>(Expression<Func<T, object>> expression); // // Summary: // A distinct property value count // // Parameters: // propertyName: public static CountProjection CountDistinct(string propertyName);

更新2

基于Firo答案中的链接，我想出了这个替代方案.我将query修改为一个独立的QueryOver对象.接下来，我尝试了这个:

Based on the links in Firo's answer, I came up with this alternative. I modified query to be a detached QueryOver object. Next, I tried this:

var rowcount = Session.QueryOver<InkoopFactuurListItem>() .Select( Projections.Alias( Projections.Count(Projections.SubQuery(detachedQuery)), "count" ) ) .FutureValue<int>();

但是，这会导致ArgumentOutOfRangeException:

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

推荐答案

ToRowCountQuery删除所有投影，甚至分组.如果您自己运行查询，则会看到在带有group by的查询中，count(*)将返回每个组的计数，而不是组数.您必须COUNT DISTINCT所有组列才能获取组数.

ToRowCountQuery removes all Projections even the groupings. If you run the query yourself you will see that count(*) in a query with group by will return the count of each group not the number of groups. you have to COUNT DISTINCT all group-columns to get the number of groups.

您需要像SELECT count(*) FROM (query)这样的东西，这是不可能直接实现的. 我只能想到一个脆弱的解决方案，在其中生成如下所示的sql的此处，然后

You need someting like SELECT count(*) FROM (query) which is not directly possible. i can only think of a fragile solution where you generate the sql like shown here and then

string sql = GenerateSQL(query.UnderlyingCriteria); rowcount = session.CreateSQLQuery(string.Format("SELECT Count(*) FROM ({0})", sql)).SetParameter("???", KlantId).FutureValue<int>();