LINQ MySQL Group by Year, Month, Day select Year, Month, Day, Count

Question

 var res = from r in db.myTable
                  group r by new
                  {
                      Year = r.DateVal.Year,
                      Month = r.DateVal.Month,
                      Day = r.DateVal.Day
                  } into g
                  let Count = g.Count()
                  select new
                  {
                      Year = g.Key.Year,
                      Month = g.Key.Month,
                      Day = g.Key.Day,
                      Count = Count
                  };

Doesn't work.

Excerpt from Inner Exception:

    InnerException: MySql.Data.MySqlClient.MySqlException
    HResult=-2147467259
    Message=Unknown column 'GroupBy1.K1' in 'field list'

The query generates the following SQL:

SELECT
    1 AS `C1`, 
    `GroupBy1`.`K1` AS `C2`, 
    `GroupBy1`.`K2` AS `C3`, 
    `GroupBy1`.`K3` AS `C4`, 
    `GroupBy1`.`A1` AS `C5`
    FROM (SELECT
        COUNT(1) AS `A1`
        FROM `myTable` AS `Extent1`
        GROUP BY 
        YEAR(`Extent1`.`DateVal`), 
        MONTH(`Extent1`.`DateVal`), 
        DAY(`Extent1`.`DateVal`)) AS `GroupBy1`

---

**

Linq Query as suggested by Zach:

**

    var test = from r in db.myTable
                   group r by new
                   {
                       Year = r.DateCol.Year,
                       Month = r.DateCol.Month,
                       Day = r.DateCol.Day
                   } into grp
                   select new
                   {
                       Year = grp.Key.Year,
                       Month = grp.Key.Month,
                       Day = grp.Key.Day,
                       Count = grp.Count()
                   };
        try
        {
            var test2 = test.ToList();
        }
        catch (Exception err)
        {

        }

Generated SQL:

    SELECT
    1 AS `C1`, 
    `GroupBy1`.`K1` AS `C2`, 
    `GroupBy1`.`K2` AS `C3`, 
    `GroupBy1`.`K3` AS `C4`, 
    `GroupBy1`.`A1` AS `C5`
    FROM (SELECT
    COUNT(1) AS `A1`
    FROM `myTable` AS `Extent1`
     GROUP BY 
    YEAR(`Extent1`.`DateCol`), 
    MONTH(`Extent1`.`DateCol`), 
    DAY(`Extent1`.`DateCol`)) AS `GroupBy1`

Exception caught:

System.Data.EntityCommandExecutionException was caught
  HResult=-2146232004
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
       at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at myNamespace._Default.fillChart(String username, Int32 tzClient) in e:\...[Path on my harddrive]..\Default.aspx.cs:line 102
  InnerException: MySql.Data.MySqlClient.MySqlException
       HResult=-2147467259
       Message=Unknown column 'GroupBy1.K1' in 'field list'
       Source=MySql.Data
       ErrorCode=-2147467259
       Number=1054
       StackTrace:
            at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
            at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
            at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
            at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
            at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
            at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
            at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       InnerException: 

Answer 1

Add a condition inside Count():

g.Count(_ => true)

Final solution will be:

var test = from r in db.myTable
               group r by new
               {
                   Year = r.DateCol.Year,
                   Month = r.DateCol.Month,
                   Day = r.DateCol.Day
               } into grp
               select new
               {
                   Year = grp.Key.Year,
                   Month = grp.Key.Month,
                   Day = grp.Key.Day,
                   Count = grp.Count(_ => true)
               };
    try
    {
        var test2 = test.ToList();
    }
    catch (Exception err)
    {

    }

Don't know how but that fixed the problem for me!