LINQ MySQL Group按年,月,日选择年,月,日,计数 [英] LINQ MySQL Group by Year, Month, Day select Year, Month, Day, Count
本文介绍了LINQ MySQL Group按年,月,日选择年,月,日,计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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
};
无效。
摘自内部异常:
InnerException:MySql.Data。 MySqlClient.MySqlException
HResult = -2147467259
消息='字段列表'中的未知列'GroupBy1.K1'
查询生成以下SQL:
SELECT
1 AS`
`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`
**
b $ b
Linq按照Zach建议的查询:
**
var test = from 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(异常错误)
{
}
生成的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`
捕获到异常:
System.Data.EntityCommandExecutionException被捕获
HResult = -2146232004
消息=执行命令定义时发生错误。有关详细信息,请参阅内部异常。
Source = System.Data.Entity
StackTrace:
在System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand,CommandBehavior行为)
在System.Data.Objects.Internal .ObjectQueryExecutionPlan.Execute [TResultType](ObjectContext context,ObjectParameterCollection parameterValues)
在System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
在System.Data.Objects.ObjectQuery`1 .System.Collections.Generic.IEnumerable< T> .GetEnumerator()
在System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
在System.Data.Entity.Infrastructure。 DbQuery`1.System.Collections.Generic.IEnumerable< TResult> .GetEnumerator()
在System.Collections.Generic.List`1..ctor(IEnumerable`1集合)
在System.Linq。 Enumerable.ToList [TSource](IEnumerable`1 source)
在myNamespace._Default.fillChart(字符串用户名,Int32 tzClient)在e:\ ... [我的硬盘上的路径] .. \Default.aspx .cs:line 102
InnerException:MySql.Data.MySqlClient.MySqlException
HResult = -2147467259
消息='字段列表'中的未知列'GroupBy1.K1'
源= MySql.Data
ErrorCode = -2147467259
Number = 1054
StackTrace:
在MySql.Data.MySqlClient.MySqlStream.ReadPacket()
在MySql.Data.MySqlClient .NativeDriver.GetResult(Int32& affectedRow,Int64& insertId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId,Int32& affectedRows,Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId,Boolean force)
在MySql.Data.MySqlClient.MySqlDataReader.NextResult()
在MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior行为)
在MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior行为)
在System.Data.Common.DbCommand.ExecuteReader(CommandBehavior行为)
在System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand,CommandBehavior行为)
InnerException:
解决方案在Count()中添加条件:
g.Count(_ => true)
b $ b
最终解决方案是:
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(异常错误)
{
}
不知道怎么,但这解决了我的问题!
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:
解决方案 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!
这篇关于LINQ MySQL Group按年,月,日选择年,月,日,计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文