Geek Answers Handbook

LINQ MySQL Group by Year, Month, Day Select Year, Month, Day, Account

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 };

Does not work.

Excerpt from internal 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 proposed by Zak:

**

  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`

Excluded:

 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:
+4
source share
2 answers

Add a condition inside Count ():

 g.Count(_ => true)

The final decision 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) { }

I don’t know how, but it fixed the problem for me!

+1
source

Hmmmm, I can think of it outside the context of your other code, but I don’t see the need for the expression "let Count = g.Count ()". I think this may be when the SQL translation may be confused. Move the g.Count () function to your destination in the select clause, where you assign it to the var counter. Also, the fact that you assign two count counts in your linq seems unpleasant ... You might have tried this, but I hope this 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() };
0
source

More articles:


All Articles