Linq group by entity and returning the entity fails with wrong SQL

Details

Description

The following test is a variation of the test given in the first comment on NH-2560. This test fails, because the generated SQL includes all properties from the customer in the select clause, which of course isn't allowed since we are only grouping on the customer's id. Not sure if this is an important case, but to get it working would require a subquery to be generated.

select
customer1_.CustomerId as col_0_0_,
cast(count(*) as INT) as col_1_0_,
customer1_.CustomerId as CustomerId27_,
customer1_.CompanyName as CompanyN2_27_,
customer1_.ContactName as ContactN3_27_,
customer1_.ContactTitle as ContactT4_27_,
customer1_.Address as Address27_,
customer1_.City as City27_,
customer1_.Region as Region27_,
customer1_.PostalCode as PostalCode27_,
customer1_.Country as Country27_,
customer1_.Phone as Phone27_,
customer1_.Fax as Fax27_
from
Orders order0_
left outer join
Customers customer1_
on order0_.CustomerId=customer1_.CustomerId
groupby
customer1_.CustomerId
order by
cast(count(*) as INT) desc

Alexander Zaytsev
added a comment - 22/Jan/12 7:14 PM Because we selecting entire customer the query is detected as non-aggregating group by and processes by NonAggregatingGroupByRewriter which fallback to very limit client-side grouping.

I do have a similar problem. This issue is very important for as as we do rely on the expression generated by a third-party component (devexpress grid).
Our proposal to fix this would be to apply an aggregate function (MAX or MIN), to each column selected in the foreign-key table. This way an additional sub-select won`t be necessary.

select country1_.Id as col_0_0_,
cast(count(*) as INT) as col_1_0_,
cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_,
country1_.Id as Id1_,
country1_.Name as Name1_
from tbl_Person person0_ left outer join tbl_Country country1_
on person0_.CountryId=country1_.Id
groupby person0_.CountryId order by person0_.CountryId asc

Possible fixed SQL using aggreagte function:
---------

select MAX(country1_.Id) as col_0_0_,
cast(count(*) as INT) as col_1_0_,
cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_,
MAX(country1_.Id) as Id1_,
MAX(country1_.Name) as Name1_
from tbl_Person person0_ left outer join tbl_Country country1_
on person0_.CountryId=country1_.Id
groupby person0_.CountryId order by person0_.CountryId asc

David Roth
added a comment - 20/Jun/12 8:39 AM - edited I do have a similar problem. This issue is very important for as as we do rely on the expression generated by a third-party component (devexpress grid).
Our proposal to fix this would be to apply an aggregate function (MAX or MIN), to each column selected in the foreign-key table. This way an additional sub-select won`t be necessary.
session.Query<Person>()
.Where(x => !x.VersionInfo.IsDeleted)
.GroupBy(x => x.Country).OrderBy(x => x.Key)
.Select(x => new
{
Key = x.Key,
Count = x.Count(),
}).ToList();
Current wrong SQL:
---------
select country1_.Id as col_0_0_,
cast(count(*) as INT) as col_1_0_,
cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_,
country1_.Id as Id1_,
country1_.Name as Name1_
from tbl_Person person0_ left outer join tbl_Country country1_
on person0_.CountryId=country1_.Id
group by person0_.CountryId order by person0_.CountryId asc
Possible fixed SQL using aggreagte function:
---------
select MAX(country1_.Id) as col_0_0_,
cast(count(*) as INT) as col_1_0_,
cast(max(person0_.CountryId) as NVARCHAR(255)) as col_2_0_,
MAX(country1_.Id) as Id1_,
MAX(country1_.Name) as Name1_
from tbl_Person person0_ left outer join tbl_Country country1_
on person0_.CountryId=country1_.Id
group by person0_.CountryId order by person0_.CountryId asc

Chris Bristol
added a comment - 16/Oct/12 8:58 PM - edited This does not appear to be an issue with the LINQ provider. The HQL equivalent of the LINQ given in GroupByTest.SingleKeyPropertyGroupByEntityAndSelectEntity will generate the same SQL.
using ( var logSpy = new SqlLogSpy())
{
var x = session.CreateQuery( "select o.Customer, count(o) from Order o group by o.Customer" ).List();
}