How to group by day of week using linq

Question

I'm trying to count some records that were updated this week and group them by the day of week (depending when they were last updated). E.g.So Tues:1, Thur:4 Fri:5 etc... I'm not sure how to group by day of week.

var data = repo
    .Where(o => o.LastUpdated >= monday)
    .GroupBy(o => o.LastUpdated)
    .Select(g => new { DayOfWeek = g.Key, Count = g.Count() })
    .ToList();

I've tried .GroupBy(o => o.LastUpdated.DayOfWeek but that throws an error : "The specified type member 'DayOfWeek' is not supported in LINQ to Entities"

score 4 · Answer 1 · edited May 23 '17 at 12:33

If you are targeting only SqlServer database type, you can use SqlFunctions.DatePart canonical function like this

var data = repo
    .Where(o => o.LastUpdated >= monday)
    .GroupBy(o => SqlFunctions.DatePart("weekday", o.LastUpdated))
    .Select(g => new { DayOfWeek = g.Key, Count = g.Count() })
    .ToList();

Unfortunately there is no such general canonical function defined in DbFunctions, so if you are targeting another database type (or multiple database types), the only option is to switch to Linq To Objects context as described in another answer.

score 3 · Accepted Answer · answered Jan 01 '16 at 12:15

3

The message is explicit, Entity Framework doesn't know how to translate "DayOfWeek" to SQL. The simplest solution would be to do the grouping outside of SQL after retrieving the data:

var data = repo
    .Where(o => o.LastUpdated >= monday)
    .AsEnumerable() // After this everything uses LINQ to Objects and is executed locally, not on your SQL server
    .GroupBy(o => o.LastUpdated)
    .Select(g => new { DayOfWeek = g.Key, Count = g.Count() })
    .ToList();

It should hardly have a performance impact either way as you're not filtering further down so you're not retrieving more data than you need from the server, anything past AsEnumerable is materialized as data, anything before just générâtes a SQL query, so past AsEnumerable (or anything else that would materialize the query like ToArray or ToList) you can use anything you'd normally use in C# without worrying about it being translatable to SQL.

answered Jan 01 '16 at 12:15

Ronan Thibaudau

3,413
3
29
78

Ok I've added the .AsEnumerable() and then added .GroupBy(o => o.LastUpdated.DayOfWeek) ...and it has worked. – thegunner Jan 01 '16 at 12:25
According to this SO [answer](https://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable#:~:text=ToList%20%E2%80%93%20which%20converts%20an%20IEnumerable,for%20this%20purpose%20as%20well.&text=AsEnumerable%20preserves%20deferred%20execution%20and,a%20way%20to%20do%20that.) AsEnumerable after the Where clause does not load all the records into memory but loads all the columns. So you you may want to verify this and update your answer. – Thabiso Mofokeng Jul 22 '20 at 10:56
@ThabisoMofokeng AsEnumerable does not load anything, only materializing operators (tolist, sum etc) will load anything, it simply switches from using the linq to object methods instead of the IQueryable ones, and those methods execute in memory, so anything done after AsEnumerable will be done in memory in this case. As for what gets loaded from the db regardless of what you do after AsEnumerable what will get loaded is whatever would’ve been materialized before it (same as calling tolist but with deffered execution and no list), so it’s different depending on if using lazy loading or not – Ronan Thibaudau Jul 22 '20 at 16:11

Mukesh Kalgude · Answer 3 · 2016-01-01T13:09:51.460

0

It is only possible to lastupdated column datatype of datetime.

var data = repo.Where(o => o.LastUpdated >= monday).AsEnumerable().GroupBy(o => o.LastUpdated.Value.Day).Select(g => new { DayOfWeek = g.Key, Count = g.Count() }).ToList();

edited Jan 01 '16 at 13:09

answered Jan 01 '16 at 12:11

Mukesh Kalgude

4,814
2
17
32

How to group by day of week using linq

3 Answers3