MySQL entity framework Group and Select By Date

Question

I have a MySQL database with a table 'Clicks'. There's a 'Created' column (datetime) which I'd like to group by and select the year, month and day part. I want to count the records per day within a specific range of dates (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 produces a bad query:

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 an error: MySql.Data.MySqlClient.MySqlException: Can't group on 'A1'

What do I do wrong? Or is this a MySql connector bug? I tried MySQL connectors 6.5.4 and 6.6.5

Answer 1

DJ KRAZE I tried your approach and it turns out that the double "select" is necessary to produce a correct query by EF. Thanks! I post the full answer here if 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});

These 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 query should work as well btw)