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.
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.