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:

Looks buggy, `K1`, `K2`, and `K3` should be in the SELECT clause of the subquery. — Gert Arnold, Feb 27 '13 at 18:13
[This](http://stackoverflow.com/q/15117391/861716) might be interesting for you. Esp. [the comment](http://stackoverflow.com/questions/15117391/mysql-entity-framework-with-distinct#comment21302403_15117391). — Gert Arnold, Feb 28 '13 at 11:50
I quite agree that they should be in the select clause of the subquery. — Faizan Kazi, Mar 01 '13 at 10:43
It seems to have a problem generating queries for grouping by multiple keys, then performing a count on the group AND selecting the key parts. If I remove either {Year, Month, Day} or {Count} from the select it works. — Faizan Kazi, Mar 01 '13 at 10:45

score 2 · Answer 1 · answered Jan 03 '17 at 15:53

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!

Zach Johnson · Answer 2 · 2013-04-08T06:12:45.500

0

Hmmmm, I may be thinking of this outside the context of your other code but I don't see the need for the "let Count = g.Count()" statement. I think that this could be where the SQL translation could be getting screwed up. Move the g.Count() function down to your assignment in the select clause where you assign it to the Count var. Also the fact that you assign two count vars in your linq looks troublesome... Maybe you tried this, but I hope it helps.

Like this...

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

edited Apr 08 '13 at 06:12

answered Apr 02 '13 at 20:11

Zach Johnson

682
7
14

not quite working.... I've updated my question to reflect your suggestion and the resulting linq output & error – Faizan Kazi Apr 04 '13 at 06:18
Just spitballing here, but what if you changed the names in the group by statement so that they are different than the names in the select statement. I'm thinking that there could be a bug in the way that the SQL is being generated. I think that It could be getting hung up when it tries to generate names. Linq, unlike SQL uses nested scopes which is consistent with all of C#. In SQL all names have the scope of the entire statement. This inconsistency between the two languages could cause some "lost in translation" bugs. I've updated my answer to reflect what I mean. – Zach Johnson Apr 08 '13 at 06:18

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

Linq Query as suggested by Zach:

2 Answers2

Linked