Bug #80952 linq generate wring query with group by: Unknown column 'GroupBy1.K1' in 'field
Submitted: 4 Apr 2016 21:31 Modified: 5 Apr 2016 5:56
Reporter: ZOKIAD ZOKIAD Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.8 OS:Microsoft Windows (7 64 bits)
Assigned to: CPU Architecture:Any
Tags: connector net groupby

[4 Apr 2016 21:31] ZOKIAD ZOKIAD
Description:
MySQL Connector Net 6.9.8
EntityFramework 6.0.0
MySql.Data 6.9.8
MySql.Data.Entity 6.9.8
VS 2015

Hello,

When execute a query with groupy by in linq. I have an error:

Unknown column 'GroupBy1.K1' in 'field

            using (TestEntities db = new TestEntities())
            {
                db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

                var stats = (from stat in db.Tests.AsNoTracking()
                             group stat by new { stat.Date.Year, stat.Date.Month } into g
                             orderby g.Key.Year, g.Key.Month
                             select new
                             {
                                 DateYear = g.Key.Year,
                                 DateMonth = g.Key.Month,
                                 Contact = g.Sum(x => x.Contact),
                                 Phone = g.Sum(x => x.Phone),
                                 Redirection = g.Sum(x => x.Redirection),
                                 Visite = g.Sum(x => x.Visite)
                             });

                foreach (var statAds in stats)
                {
                }
            }

Generated SQL query is:

SELECT
`Project1`.`C7` AS `C1`, 
`Project1`.`C5` AS `C2`, 
`Project1`.`C6` AS `C3`, 
`Project1`.`C1` AS `C4`, 
`Project1`.`C2` AS `C5`, 
`Project1`.`C3` AS `C6`, 
`Project1`.`C4` AS `C7`
FROM (SELECT
`GroupBy1`.`A1` AS `C1`, 
`GroupBy1`.`A2` AS `C2`, 
`GroupBy1`.`A3` AS `C3`, 
`GroupBy1`.`A4` AS `C4`, 
`GroupBy1`.`K1` AS `C5`, 
`GroupBy1`.`K2` AS `C6`, 
1 AS `C7`
FROM (SELECT
SUM(`Extent1`.`Contact`) AS `A1`, 
SUM(`Extent1`.`Phone`) AS `A2`, 
SUM(`Extent1`.`Redirection`) AS `A3`, 
SUM(`Extent1`.`Visite`) AS `A4`
FROM `Test` AS `Extent1`
 GROUP BY 
YEAR(`Extent1`.`Date`), 
MONTH(`Extent1`.`Date`)) AS `GroupBy1`) AS `Project1`
 ORDER BY 
`Project1`.`C5` ASC, 
`Project1`.`C6` ASC

Exception:
Unknown column 'GroupBy1.K1' in 'field 

How to repeat:
Create database:

USE Test;
CREATE TABLE `Test` (
  `UserAccountId` bigint(20) NOT NULL,
  `Date` date NOT NULL,
  `Visite` int(10) unsigned NOT NULL DEFAULT '0',
  `Redirection` int(10) unsigned NOT NULL DEFAULT '0',
  `Contact` int(10) unsigned NOT NULL DEFAULT '0',
  `Phone` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`UserAccountId`,`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Excute attached Visual Studio program.
[5 Apr 2016 5:56] Chiranjeevi Battula
Hello ZOKIAD ZOKIAD,

Thank you for the bug report.
This is most likely duplicate of Bug #79817, please see Bug #79817.

Thanks,
Chiranjeevi.