DBMS DBMS DBMS SUM
I have a MySQL database with a "Clicks" table. There is a "Created" (datetime) column that I would like to group and select year, month and day. I want to count records per day in a specific date range (startDate and endDate).
var query = from c in scope.Entities.Clicks
where c.Created >= startDate && c.Created <= endDate
group c by new {c.Created.Year, c.Created.Month, c.Created.Day}
into grouped
select new {
Year = grouped.Key.Year,
Month = grouped.Key.Month,
Day = grouped.Key.Day,
Clicks = grouped.Count()
};
This causes a bad request:
SELECT
`GroupBy1`.`K1` AS `C1`,
`GroupBy1`.`K2` AS `C2`,
`GroupBy1`.`K3` AS `C3`,
`GroupBy1`.`K4` AS `C4`,
`GroupBy1`.`A1` AS `C5`
FROM (SELECT
COUNT(1) AS `A1`
FROM `Click` AS `Extent1`
WHERE (`Extent1`.`Created` >= @p__linq__0) AND (`Extent1`.`Created` <= @p__linq__1)
GROUP BY
1,
YEAR(`Extent1`.`Created`),
MONTH(`Extent1`.`Created`),
DAY(`Extent1`.`Created`)) AS `GroupBy1`
With error: MySql.Data.MySqlClient.MySqlException: cannot group at 'A1'
How am I wrong? Or is it a MySql connector bug? I have tried MySQL 6.5.4 and 6.6.5 connectors
+3
source to share
1 answer
DJ KRAZE I've tried your approach and it turns out that a double "pick" is needed to get the correct EF request. Thank you! I am writing a complete answer here in case anyone needs it.
I tested both ways and both work:
var subQuery = from c in scope.Entities.Clicks
where c.Created >= startDate && c.Created <= endDate
select new { c.Created.Year, c.Created.Month, c.Created.Day };
var query = from c in subQuery
group c by new {c.Year, c.Month, c.Day}
into grouped
select new {
Year = grouped.Key.Year,
Month = grouped.Key.Month,
Day = grouped.Key.Day,
Clicks = grouped.Count()
};
and
var query = scope.Entities.Clicks.Where(c => c.Created >= startDate && c.Created <= endDate)
.Select(c => new { c.Created.Year, c.Created.Month, c.Created.Day})
.GroupBy(c => new {c.Year, c.Month, c.Day})
.Select(grouped => new { Clicks = grouped.Count(), grouped.Key.Year, grouped.Key.Month, grouped.Key.Day});
They give the correct mysql query:
SELECT
1 AS `C1`,
`GroupBy1`.`K1` AS `C2`,
`GroupBy1`.`K2` AS `C3`,
`GroupBy1`.`K3` AS `C4`,
`GroupBy1`.`A1` AS `C5`
FROM (SELECT
`Project1`.`C1` AS `K1`,
`Project1`.`C2` AS `K2`,
`Project1`.`C3` AS `K3`,
COUNT(1) AS `A1`
FROM (SELECT
YEAR(`Extent1`.`Created`) AS `C1`,
MONTH(`Extent1`.`Created`) AS `C2`,
DAY(`Extent1`.`Created`) AS `C3`
FROM `Click` AS `Extent1`
WHERE (`Extent1`.`Created` >= @p__linq__0) AND (`Extent1`.`Created` <= @p__linq__1)) AS `Project1`
GROUP BY
`Project1`.`C1`,
`Project1`.`C2`,
`Project1`.`C3`) AS `GroupBy1`
(I still think this is a bug and my first request should work btw as well)
+2
source to share