NH-3154 - Linq group counting does not work

[nhibernate-bot]

Alexander Zaytsev created an issue — 20th May 2012, 6:15:30:

Following query does not work:

var result = db.Orders
    .GroupBy(x => x.ShippingDate)
    .Count() // there I want to count groups and not elements inside each group

It produces following sql:

    select
        cast(count(*) as INT) as col*0_0* 
    from
        Orders order0_ 
    group by
        order0_.ShippedDate

Which is semantically identical to following linq query:

var result = db.Orders
    .GroupBy(x => x.ShippingDate)
    .Select(x => x.Count())
    .ToList()
    .FirstOrDefault()

But it should work as count distinct

			var result = db.Orders
			               .Select(x => x.ShippingDate)
			               .Distinct()
			               .Count();

---

Alexander Zaytsev added a comment — 20th May 2012, 6:37:25:

NH-3155 is possible transformation to resolve this issue.

---

dthompson added a comment — 28th November 2012, 16:08:09:

I am running into the same issue here. Tried both NH-3155 fix branches but neither seem to resolve the issue. Thanks for all the hard work :)

---

ZhangJian added a comment — 23rd February 2013, 6:26:44:

最新的版本也没有支持该功能，希望能尽快看到该功能！ NHibernate团队，加油！

---

Oskar Berggren added a comment — 23rd February 2013, 18:20:33:

<~jefferyzhang> I'm sorry, I cannot understand that language.

---

Alexander Zaytsev added a comment — 23rd February 2013, 22:52:47:

<~oskar.berggren> he said:

"The latest version does not support this feature, and hope to see this feature as soon as possible! NHibernate team, refuel!"

---

Bilal Fazlani added a comment — 10th July 2014, 10:27:30:

Hi, Is this issue fixed?, or anyone working on it ?

---

Frédéric Delaporte added a comment — 4th March 2017, 18:19:52:

Following query, tried as a workaround, fails too:

var result = db.Orders
    .GroupBy(x => x.ShippingDate)
    .Sum(g => 1);

It issues:

select
        cast(sum(1) as INT) as col*0_0* 
    from
        Orders order0_ 
    group by
        order0_.ShippedDate

This means this bug likely affects all aggregates.

---

Frédéric Delaporte added a comment — 4th March 2017, 18:48:59:

The Distinct().Count() workaround fails with multiple grouping key. It looks like we have no workaround for this case.

var result = db.Orders
	.Select(x => new { x.ShippingDate, x.ShippedTo })
	.Distinct()
	.Count();

Fails with NotSupportedException: new <>f**AnonymousType42(ShippingDate = <100001>.ShippingDate, ShippedTo = [100001].ShippedTo) (Seems to be the type name put as the exception message. Fails at VisitExpression, at VisitNhDistinctinHqlGeneratorExpressionTreeVisitor`.)

Indeed, this would require a sub query in the from statement, which is unsupported by HQL as far as I know.

This would imply below query can not be supported:

var result = db.Orders
	.GroupBy(x => new { x.ShippingDate, x.ShippedTo })
	.Count();

I wonder then if NHibernate should really try to translate the "unique grouping key then count" case to a "distinct then count", as the more general group by case would still fails.

---

Thomas-Louis Simard added a comment — 30th April 2017, 16:01:17:

Try this as a workaround :

var result = db.Orders
    .GroupBy(group => group.ShippingDate)
    .Select(group => group.Count() / group.Count())
    .Sum();

[bahusoid]

As a workaround group by can be replaced with subquery:

var result = db.Orders.Count(o => o == db.Orders.First(os => os.ShippingDate == o.ShippingDate));

The Distinct().Count() workaround fails with multiple grouping key. It looks like we have no workaround for this case.

With subquery it works also with multiple grouping keys:

var result2 = db.Orders.Count(o => o == db.Orders.First(os => os.ShippingDate == o.ShippingDate && os.ShippedTo == o.ShippedTo));

[manuelchantada]

Same issue here.

Found a workaround that is performant also!

var result = db.Orders.GroupBy(group => group.ShippingDate).Select(x => x.Key).Distinct().Count();