Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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.
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
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):
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]
–
–
–
–
–
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 GroupBy has truncated the time from the DateTime already, so you do not need to call it again.
To use DbFunctions.TruncateTime you'll need to reference the assembly System.Data.Entity and include using System.Data.Entity;
Note: Edited to address deprecation of EntityFunctions.
–
–
–
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
–
.Select(i => new
Date = DateTime.ParseExact(i.Key, "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None),
Count = i.Count()
–
–
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.
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 } ]
Hope this helps someone in future.
Just convert first IQueryable to IEnumerable(List) with the help of ToList() then use groupby
context.Users
.Where((x.LastLogIn >= lastWeek) && (x.LastLogIn <= DateTime.Now))
.ToList()
.GroupBy(x => x.LastLogIn.Date))
.Select(x => new
Value = x.Count(),
Day = (DateTime)x.Key
}).ToList();
–
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
