C# Linq - 按日期分组并选择计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19225895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq - Grouping by date and selecting count
提问by Thewads
I am currently working through a problem where I would like to run a query which groups the results by the date selected.
我目前正在解决一个问题，我想运行一个查询，按所选日期对结果进行分组。
For this example, imagine a simple model like so:
对于这个例子，想象一个像这样的简单模型：
public class User
{
public DateTime LastLogIn {get; set;}
public string Name {get; set;}
}
The solution I am looking for is to get a count of Users logged in by Date. In the database the DateTime are stored with both date and time components, but for this query I really only care about the date.
我正在寻找的解决方案是获取按日期登录的用户数。在数据库中，DateTime 与日期和时间组件一起存储，但对于这个查询，我真的只关心日期。
What I currently have is this:
我目前拥有的是：
context.Users
.Where((x.LastLogIn >= lastWeek)
&& (x.LastLogIn <= DateTime.Now))
.GroupBy(x => EntityFunctions.TruncateTime(x.LastLogIn))
.Select(x => new
{
Value = x.Count(),
Day = (DateTime)EntityFunctions.TruncateTime(x.Key)
}).ToList();
The above however returns an empty list.
然而，上面返回一个空列表。
End goal is to have a List of objects, which contain a Value (the count of users logged in on a day) and a Day (the day in question)
最终目标是拥有一个对象列表，其中包含一个值（一天登录的用户数）和一个天（有问题的一天）
Any thoughts?
有什么想法吗？
Upon changing the query to:
将查询更改为：
context.Users
.Where((x.LastLogIn >= lastWeek)
&& (x.LastLogIn <= DateTime.Now))
.GroupBy(x => EntityFunctions.TruncateTime(x.LastLogIn))
.Select(x => new
{
Value = x.Count(),
Day = (DateTime)x.Key
}).ToList();
it now returns a list with a single item, with the Value being the total count of Users that match the where clause, and the Day being the very first day. It still hasn't seemed to be able to group by the days
它现在返回一个包含单个项目的列表，其中 Value 是与 where 子句匹配的用户总数，而 Day 是第一天。好像还是不能按天分组
NOTE:turns out the above code is right, I was just doing something else wrong.
注意：结果证明上面的代码是正确的，我只是做错了其他事情。
Sql that it is generating is (note might be very slight syntactical errors here with me adjusting it for the example):
它正在生成的 Sql 是（注意这里可能有非常轻微的语法错误，我针对示例进行了调整）：
SELECT
1 AS [C1],
[GroupBy1].[A1] AS [C2],
CAST( [GroupBy1].[K1] AS datetime2) AS [C3]
FROM ( SELECT
[Filter1].[K1] AS [K1],
COUNT([Filter1].[A1]) AS [A1]
FROM ( SELECT
convert (datetime2, convert(varchar(255), [Extent1].[LastLogIn], 102) , 102) AS [K1],
1 AS [A1]
FROM [dbo].[Users] AS [Extent1]
WHERE (([Extent1].[LastLogIn] >= @p__linq__1) AND ([Extent1].[LastLogIn] <= @p__linq__2)
) AS [Filter1]
GROUP BY [K1]
) AS [GroupBy1]
采纳答案by dasblinkenlight
You do not need the second TruncateTimein there:
你不需要TruncateTime那里的第二个：
context.Users
.Where((x.LastLogIn >= lastWeek) && (x.LastLogIn <= DateTime.Now))
.GroupBy(x => DbFunctions.TruncateTime(x.LastLogIn))
.Select(x => new
{
Value = x.Count(),
// Replace the commented line
//Day = (DateTime)DbFunctions.TruncateTime(x.Key)
// ...with this line
Day = (DateTime)x.Key
}).ToList();
The GroupByhas truncated the time from the DateTimealready, so you do not need to call it again.
该GroupBy已截断从时间DateTime了，所以你不需要再次调用它做。
To use DbFunctions.TruncateTimeyou'll need to reference the assembly System.Data.Entityand include using System.Data.Entity;
要使用，DbFunctions.TruncateTime您需要引用程序集System.Data.Entity并包括using System.Data.Entity;
Note:Edited to address deprecation of EntityFunctions.
注意：已编辑以解决EntityFunctions.
回答by AD.Net
Try this:
尝试这个：
.GroupBy(x => new {Year = x.LastLogIn.Year, Month = x.LastLogIn.Month, Day = x.LastLogIn.Day)
.Select(x => new
{
Value = x.Count(),
Year = x.Key.Year,
Month = x.Key.Month,
Day = x.Key.Day
})
回答by SeyedPooya Soofbaf
You can do it easily:
您可以轻松做到：
yourDateList.GroupBy(i => i.ToString("yyyyMMdd"))
.Select(i => new
{
Date = DateTime.ParseExact(i.Key, "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None),
Count = i.Count()
});
回答by Yahiya Iqbal
You can also do it in one line.
您也可以在一行中完成。
var b = (from a in ctx.Candidates select a)
.GroupBy(x => x.CreatedTimestamp.Date).Select(g => new { Date=(g.Key).ToShortDateString(), Count = g.Count() });
回答by Jabez
I came across this same problem to get a count based on group by a datetime column. This is what i did. Thanks for some of the answers here. I tried the minimal version and I used this code in .netcore solution.
我遇到了同样的问题，以通过日期时间列获取基于组的计数。这就是我所做的。感谢这里的一些答案。我尝试了最小版本，并在 .netcore 解决方案中使用了此代码。
var result = _context.yourdbmodel
.GroupBy(x=>x.yourdatetimecolumn.Value.Date)
.select(x=> new
{
Count = x.Count(),
Date = (DateTime)x.Key // or x.Key.Date (excluding time info) or x.Key.Date.ToString() (give only Date in string format)
})
.ToList();
Sample output:
示例输出：
[ { "requestDate": "2020-04-01", "requestCount": 3 }, { "requestDate": "2020-04-07", "requestCount": 14 } ]
[ { "requestDate": "2020-04-01", "requestCount": 3 }, { "requestDate": "2020-04-07", "requestCount": 14 } ]
Hope this helps someone in future.
希望这对将来的人有所帮助。
