GroupBy isn't working
Hi all,
I'm using MySQL with Entity Framework in a .net 4.5.2 project.
I've got EF6.1.3 installed, alongside MySQL/Connector 6.9.9.
I'm having trouble with Groupings.
This first manifested when I was trying to use server-side binding on the DevExpress PivotGrid - I kept getting "query errors" reported. I've raised this with DevExpress, and they are investigating. HOWEVER, I'm now getting the same kind of errors from simple grouping queries I'm running directly through LINQ and EF.
I can't see any existing queries about this other than one from 2010 which I'm sure has been dealt with by now....?
Anyway,
This LINQ:
var q = dbset.Where(o => o.CentreSectorId == centreSectorId)
.GroupBy(
o =>
new
{
o.GreatGrandParentTitle,
o.GrandParentTitle,
o.ParentTitle,
o.Title,
})
.Select(g => new {titles = g.Key, titleCount = g.Count()})
generates this SQL:
SELECT
`GroupBy1`.`K5` AS `C1`,
`GroupBy1`.`K4` AS `GreatGrandParentTitle`,
`GroupBy1`.`K3` AS `GrandParentTitle`,
`GroupBy1`.`K2` AS `ParentTitle`,
`GroupBy1`.`K1` AS `Title`,
`GroupBy1`.`A1` AS `C2`
FROM (SELECT `Extent1`.`Title` AS `K1`,
`Extent1`.`ParentTitle` AS `K2`,
`Extent1`.`GrandParentTitle` AS `K3`,
`Extent1`.`GreatGrandParentTitle` AS `K4`,
Count(1) AS `A1`
FROM `OpenStats` AS `Extent1`
WHERE `Extent1`.`CentreSectorId` = @p__linq__0
GROUP BY `Extent1`.`Title`,
`Extent1`.`ParentTitle`,
`Extent1`.`GrandParentTitle`,
`Extent1`.`GreatGrandParentTitle`,
1) AS `GroupBy1`
...The problem being the very first line of the SQL - GroupBy1.K5 does not exist anywhere!
Help please?