MySQL Bugs: #57186: Linq query fails when selecting both the key and an aggregate from a grouping

Bug #57186	Linq query fails when selecting both the key and an aggregate from a grouping
Submitted:	1 Oct 2010 21:40	Modified:	25 Oct 2010 6:49
Reporter:	Jason Day	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.3.4	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY

Description:
Selecting both the key and an aggregate from a Linq grouping results in an error due to invalid SQL. You can see in the generated SQL below that K1 is referenced in the outer select but not in the GroupBy1 subquery:

SELECT
`GroupBy1`.`K1` AS `ID`, 
`GroupBy1`.`A1` AS `C1`
FROM (SELECT
Count(1) AS `A1`
FROM `Tbl` AS `Extent1`
 GROUP BY 
`Extent1`.`ID`) AS `GroupBy1`

If only the aggregate is selected, a query similar to the above (without the bad K1 reference) will be executed. If only the key is selected, a SELECT DISTINCT will be executed instead of a GROUP BY. Only the hybrid case results in an error.

How to repeat:
1. Create a database:
CREATE DATABASE GroupByTest;
CREATE TABLE GroupByTest.Tbl (ID INT NOT NULL PRIMARY KEY);

2. Generate the EDM in VS (I'm using 2010).

3. Execute a Linq query it like so:
using (var ctx = new GroupByTestDb())
{
	(from t in ctx.Tbl
	 group t by t.ID into g
	 select new { g.Key, Count = g.Count() }).ToArray();
}

This is a duplicate of Bug#46742.

I got the same problem, and i got a temp way to resolve it. 

Change "g.Key" to "g.FirstOrDefault().ColumnGroupBy"

and we can not use a subscript in group by clause

example:
// t2 is a subtable of t1
var counts = from t1 in t1entities
group gt1 by t1.c1
select new { C1 = gt1.FirstOrDefault().c1, Count = gt1.Where(t1 => t1.t2s.Any(t2=>t2.c2 == 0)).Count()};

it will also cause an "Unknown column" exception, so please FIX this, many thanks.