MySQL Bugs: #79817: Error in LINQ to Entities query when using multiple GroupBy's

Bug #79817	Error in LINQ to Entities query when using multiple GroupBy's
Submitted:	31 Dec 2015 11:20	Modified:	15 Jan 2016 9:16
Reporter:	Jorr it	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.8	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Connector/Net, entity framework, linq

Description:
When we write an LINQ statement with multiple GroupBy's, the generated SQL is invalid and gives exception:

Unknown column 'GroupBy1.K1' in 'where clause'

Below there are three variations of LINQ statements which all more or less result in same the exception.

new myDbContext().careactivity
	.GroupBy(x => new { x.ActivityGroup, x.MedicalSpecialist })
	.Select(o => new
	{
		P6 = o.Key,
		P3 = o.Sum(elem => elem.HoursAfterSummary)
	})
	.ToList();
	
-- or --

new myDbContext().careactivity
	.GroupBy(x => x.ActivityGroup })
	.Select(o => new
	{
		P6 = o.Key,
		P3 = o.Sum(elem => elem.HoursAfterSummary),
		P4 = o.GroupBy(elem => elem.MedicalSpecialist).Count()
	})
	.ToList();
	
-- or --

new myDbContext().careactivity
	.GroupBy(x => x.ActivityGroup })
	.Select(o => new
	{
		P6 = o.Key,
		P3 = o.Sum(elem => elem.HoursAfterSummary),
		P4 = o.Select(elem => elem.MedicalSpecialist).Distinct().Count()
	})
	.ToList();	

I'm using .NET Framework 4.5.1, EntityFramework 6.1.3 and MySQL Connection/NET 6.9.8. My project has an Database First approach.  This is the table:

CREATE TABLE `careactivity` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Oid` int(11) DEFAULT NULL,
  `CustomerSiteId` int(11) DEFAULT NULL,
  `MedicalSpecialist` varchar(255) DEFAULT NULL,
  `ActivityGroup` varchar(255) DEFAULT NULL,
  `HoursAfterSummary` decimal(19,5) DEFAULT NULL,
  PRIMARY KEY (`Id`),
)

Any sample data can be used. Sample data can be sent. Also a complete sample VS project can be sent.

It seems to have similarities with this bug:
http://bugs.mysql.com/bug.php?id=68513

How to repeat:
A complete sample VS project can be sent.

Suggested fix:
LINQ Query must work without throwing exception.

Hello  Jorrit Steetskamp,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.8.

Thanks,
Chiranjeevi.

Result StandardOutput:	
---
SELECT
`GroupBy1`.`K3` AS `C1`, 
`GroupBy1`.`K2` AS `ActivityGroup`, 
`GroupBy1`.`K1` AS `MedicalSpecialist`, 
`GroupBy1`.`A1` AS `C2`
FROM (SELECT
`Extent1`.`MedicalSpecialist` AS `K1`, 
`Extent1`.`ActivityGroup` AS `K2`, 
SUM(`Extent1`.`HoursAfterSummary`) AS `A1`
FROM `careactivity` AS `Extent1`
 GROUP BY 
`Extent1`.`MedicalSpecialist`, 
`Extent1`.`ActivityGroup`, 
1) AS `GroupBy1`
********************************
SELECT
1 AS `C1`, 
`Project2`.`ActivityGroup`, 
`Project2`.`C1` AS `C2`, 
`Project2`.`C2` AS `C3`
FROM (SELECT
`GroupBy1`.`A1` AS `C1`, 
`GroupBy1`.`K1` AS `ActivityGroup`, 
(SELECT
COUNT(1) AS `A1`
FROM (SELECT DISTINCT 
`Extent2`.`MedicalSpecialist`
FROM `careactivity` AS `Extent2`
 WHERE (`GroupBy1`.`K1` = `Extent2`.`ActivityGroup`) OR ((`GroupBy1`.`K1` IS  NULL) AND (`Extent2`.`ActivityGroup` IS  NULL))) AS `Distinct1`) AS `C2`
FROM (SELECT
`Extent1`.`ActivityGroup` AS `K1`, 
SUM(`Extent1`.`HoursAfterSummary`) AS `A1`
FROM `careactivity` AS `Extent1`
 GROUP BY 
`Extent1`.`ActivityGroup`) AS `GroupBy1`) AS `Project2`
********************************
SELECT
1 AS `C1`, 
`Project2`.`ActivityGroup`, 
`Project2`.`C1` AS `C2`, 
`Project2`.`C2` AS `C3`
FROM (SELECT
`GroupBy1`.`A1` AS `C1`, 
`GroupBy1`.`K1` AS `ActivityGroup`, 
(SELECT
COUNT(1) AS `A1`
FROM (SELECT DISTINCT 
`Extent2`.`MedicalSpecialist`
FROM `careactivity` AS `Extent2`
 WHERE (`GroupBy1`.`K1` = `Extent2`.`ActivityGroup`) OR ((`GroupBy1`.`K1` IS  NULL) AND (`Extent2`.`ActivityGroup` IS  NULL))) AS `Distinct1`) AS `C2`
FROM (SELECT
`Extent1`.`ActivityGroup` AS `K1`, 
SUM(`Extent1`.`HoursAfterSummary`) AS `A1`
FROM `careactivity` AS `Extent1`
 GROUP BY 
`Extent1`.`ActivityGroup`) AS `GroupBy1`) AS `Project2`

Grouping by function will also trigger the same error:

var salesDailies = (from o in this.AppContext.SalesDailies
  group o by DbFunctions.TruncateTime(o.Date) into g
  select new { Date = g.Key, Qty = g.Sum(q => q.Qty) });

http://bugs.mysql.com/bug.php?id=80952 marked as duplicate of this one.

http://bugs.mysql.com/bug.php?id=82604 marked as duplicate of this one.

Is there any solution or workaround for this bug? I'm also affected by it. It is still present in version 6.10.8.0!

Here's my code:

            var l = from t in db.ticket
                    group t by t.change_time.YearWeek(3).Insert(5, 0, "/") into g
                    select new {
                        g.Key,
                        cnt = g.Count()
                    };

            var test4 = l.ToList();

By the way, this generates the following weird SQL syntax:

SELECT
1 AS `C1`, 
`GroupBy1`.`K1` AS `C2`, 
`GroupBy1`.`A1` AS `C3`
FROM (SELECT
COUNT(1) AS `A1`
FROM `ticket` AS `Extent1`
 GROUP BY 
INSERT(YEARWEEK(`Extent1`.`change_time`, 3), 5, 0, @gp1)) AS `GroupBy1`

Dear support,

any updates on this issue? Is there a workaround available?
Thank you very much!
BR Chris

Workaround: use myGroup.Min(f => f.x) instead of myGroup.Key.x

This bug has still not been fixed. 
Reported in 2016 - still broken in 2021. 

Will there be any effort at all to fix it?

_repo.Table
     .GroupBy(p => p.object.type)
     .Select(g => new
            {
                type = g.Key,                                    
                typecount = g.Count(p => p.object.type == g.Key)
            });