Description:
LINQ Group By is generating queries that don't execute properly, when trying to get the key value of the group back with the resulting sums.
How to repeat:
I was executing the following LINQ statement:
var rollup = stats.Rollups
.Where(x => x.Injected != 0 || x.BytesUsed != 0L)
.Where(x => x.DateTime >= startOverageRange && x.DateTime <= endOverageRange)
.GroupBy(x => new { x.ServerId, x.DateTime })
.Select(x => new {
ServerId = x.Key.ServerId,
DateTime = x.Key.DateTime,
Injected = x.Sum(y => y.Injected),
BytesUsed = x.Sum(y => y.BytesUsed)
})
.ToList();
Against the following table:
CREATE TABLE `Rollups` (
`ServerId` int(11) NOT NULL,
`DateTime` datetime NOT NULL,
`MailingId` varchar(150) CHARACTER SET ascii NOT NULL,
`Injected` int(11) NOT NULL DEFAULT '0',
`BytesUsed` bigint(20) NOT NULL DEFAULT '0',
`Queued` int(11) NOT NULL DEFAULT '0',
`Suppressed` int(11) NOT NULL DEFAULT '0',
`Sent` int(11) NOT NULL DEFAULT '0',
`Complaints` int(11) NOT NULL DEFAULT '0',
`Opens` int(11) NOT NULL DEFAULT '0',
`Clicks` int(11) NOT NULL DEFAULT '0',
`HardFailures` int(11) NOT NULL DEFAULT '0',
`SoftFailures` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ServerId`,`DateTime`,`MailingId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
And it generated the following query
SELECT `GroupBy1`.`K1` AS `ServerId`,
`GroupBy1`.`K2` AS `DateTime`,
`GroupBy1`.`A1` AS `C1`,
`GroupBy1`.`A2` AS `C2`
FROM (SELECT Sum(`Injected`) AS `A1`,
Sum(`BytesUsed`) AS `A2`
FROM `Rollups` AS `Extent1`
WHERE ((NOT (0 = `Extent1`.`Injected`))
OR (NOT (0 = `Extent1`.`BytesUsed`)))
AND ((`Extent1`.`DateTime` >= '2011-02-28T00:00:00.00' /* @p__linq__0 */)
AND (`Extent1`.`DateTime` <= '2011-03-28T00:00:00.00' /* @p__linq__1 */))
GROUP BY `Extent1`.`ServerId`,
`Extent1`.`DateTime`) AS `GroupBy1`
Which doesn't actually return the Group By Key value K1 and K2.
Suggested fix:
My suggestion is to make sure the key values get returned with the sub GroupBy1 query.
SELECT `GroupBy1`.`K1` AS `ServerId`,
`GroupBy1`.`K2` AS `DateTime`,
`GroupBy1`.`A1` AS `C1`,
`GroupBy1`.`A2` AS `C2`
FROM (SELECT `ServerId` AS `K1`,
`DateTime` AS `K2`,
Sum(`Injected`) AS `A1`,
Sum(`BytesUsed`) AS `A2`
FROM `Rollups` AS `Extent1`
WHERE ((NOT (0 = `Extent1`.`Injected`))
OR (NOT (0 = `Extent1`.`BytesUsed`)))
AND ((`Extent1`.`DateTime` >= '2011-02-28T00:00:00.00' /* @p__linq__0 */)
AND (`Extent1`.`DateTime` <= '2011-03-28T00:00:00.00' /* @p__linq__1 */))
GROUP BY `Extent1`.`ServerId`,
`Extent1`.`DateTime`) AS `GroupBy1`
Description: LINQ Group By is generating queries that don't execute properly, when trying to get the key value of the group back with the resulting sums. How to repeat: I was executing the following LINQ statement: var rollup = stats.Rollups .Where(x => x.Injected != 0 || x.BytesUsed != 0L) .Where(x => x.DateTime >= startOverageRange && x.DateTime <= endOverageRange) .GroupBy(x => new { x.ServerId, x.DateTime }) .Select(x => new { ServerId = x.Key.ServerId, DateTime = x.Key.DateTime, Injected = x.Sum(y => y.Injected), BytesUsed = x.Sum(y => y.BytesUsed) }) .ToList(); Against the following table: CREATE TABLE `Rollups` ( `ServerId` int(11) NOT NULL, `DateTime` datetime NOT NULL, `MailingId` varchar(150) CHARACTER SET ascii NOT NULL, `Injected` int(11) NOT NULL DEFAULT '0', `BytesUsed` bigint(20) NOT NULL DEFAULT '0', `Queued` int(11) NOT NULL DEFAULT '0', `Suppressed` int(11) NOT NULL DEFAULT '0', `Sent` int(11) NOT NULL DEFAULT '0', `Complaints` int(11) NOT NULL DEFAULT '0', `Opens` int(11) NOT NULL DEFAULT '0', `Clicks` int(11) NOT NULL DEFAULT '0', `HardFailures` int(11) NOT NULL DEFAULT '0', `SoftFailures` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ServerId`,`DateTime`,`MailingId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 And it generated the following query SELECT `GroupBy1`.`K1` AS `ServerId`, `GroupBy1`.`K2` AS `DateTime`, `GroupBy1`.`A1` AS `C1`, `GroupBy1`.`A2` AS `C2` FROM (SELECT Sum(`Injected`) AS `A1`, Sum(`BytesUsed`) AS `A2` FROM `Rollups` AS `Extent1` WHERE ((NOT (0 = `Extent1`.`Injected`)) OR (NOT (0 = `Extent1`.`BytesUsed`))) AND ((`Extent1`.`DateTime` >= '2011-02-28T00:00:00.00' /* @p__linq__0 */) AND (`Extent1`.`DateTime` <= '2011-03-28T00:00:00.00' /* @p__linq__1 */)) GROUP BY `Extent1`.`ServerId`, `Extent1`.`DateTime`) AS `GroupBy1` Which doesn't actually return the Group By Key value K1 and K2. Suggested fix: My suggestion is to make sure the key values get returned with the sub GroupBy1 query. SELECT `GroupBy1`.`K1` AS `ServerId`, `GroupBy1`.`K2` AS `DateTime`, `GroupBy1`.`A1` AS `C1`, `GroupBy1`.`A2` AS `C2` FROM (SELECT `ServerId` AS `K1`, `DateTime` AS `K2`, Sum(`Injected`) AS `A1`, Sum(`BytesUsed`) AS `A2` FROM `Rollups` AS `Extent1` WHERE ((NOT (0 = `Extent1`.`Injected`)) OR (NOT (0 = `Extent1`.`BytesUsed`))) AND ((`Extent1`.`DateTime` >= '2011-02-28T00:00:00.00' /* @p__linq__0 */) AND (`Extent1`.`DateTime` <= '2011-03-28T00:00:00.00' /* @p__linq__1 */)) GROUP BY `Extent1`.`ServerId`, `Extent1`.`DateTime`) AS `GroupBy1`