The specified type member 'DayOfWeek' is not supported in LINQ

Question

I've tried using the the options from this SO question. I'm still receiving the following error though:

The specified type member 'DayOfWeek' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Question now edited to contain full code of what I'm trying to achieve. It basically required to pull the average number of SMS's sent on each day of the week.

Here is my code:

var weekDays = rad.SMSSentItems
    .Where(x => x.Status == "DELIVRD")
    .GroupBy(x => x.StatusDate.Value.DayOfWeek)
    .Select(g => new
    {
        DayOfWeek = g.Key,
        Count = g.Count()
    })
    .ToList();


int avgSMSPerMonday = 0;
int avgSMSPerTuesday = 0;
int avgSMSPerWednesday = 0;
int avgSMSPerThursday = 0;
int avgSMSPerFriday = 0;
int avgSMSPerSaturday = 0;
int avgSMSPerSunday = 0;
int totalSMSPerDay = 0;

foreach (var day in weekDays)
{
    totalSMSPerDay = rad.SMSSentItems.Where(x => (DbFunctions.TruncateTime(x.StatusDate).Value.DayOfWeek == day.DayOfWeek) && (x.Status == "DELIVRD")).ToList().Count;

    if (day.DayOfWeek == DayOfWeek.Monday)
        avgSMSPerMonday = totalSMSPerDay / day.Count;
    else if (day.DayOfWeek == DayOfWeek.Tuesday)
        avgSMSPerTuesday = totalSMSPerDay / day.Count;
    else if (day.DayOfWeek == DayOfWeek.Wednesday)
        avgSMSPerWednesday = totalSMSPerDay / day.Count;
    else if (day.DayOfWeek == DayOfWeek.Thursday)
        avgSMSPerThursday = totalSMSPerDay / day.Count;
    else if (day.DayOfWeek == DayOfWeek.Friday)
        avgSMSPerFriday = totalSMSPerDay / day.Count;
    else if (day.DayOfWeek == DayOfWeek.Saturday)
        avgSMSPerSaturday = totalSMSPerDay / day.Count;
    else if (day.DayOfWeek == DayOfWeek.Sunday)
        avgSMSPerSunday = totalSMSPerDay / day.Count;
}

Updated code using Camillo's code:

var weekDays = rad.SMSSentItems
                            .Where(x => x.Status == "DELIVRD")
                            .GroupBy(x => x.StatusDate.Value.DayOfWeek)
                            .Select(g => new
                            {
                                DayOfWeek = g.Key,
                                Count = g.Count()
                            })
                            .ToDictionary(x => x.DayOfWeek, x => x.Count);

                int mondayCount = 0;
                int tuesdayCount = 0;
                int wednessdayCount = 0;
                int thursdayCount = 0;
                int fridayCount = 0;
                int saturdayCount = 0;
                int sundayCount = 0;

                //determine average number of sms's sent per day of week
                foreach (var day in weekDays)
                {
                    int daysCount = rad.SMSSentItems
                        .Where(x => (x.StatusDate.Value.DayOfWeek == day.Key)
                                 && x.Status == "DELIVRD")
                        .Count();

                    if (day.Key == DayOfWeek.Monday)
                        mondayCount = daysCount / day.Value;
                    else if (day.Key == DayOfWeek.Tuesday)
                        tuesdayCount = daysCount / day.Value;
                    else if (day.Key == DayOfWeek.Wednesday)
                        wednessdayCount = daysCount / day.Value;
                    else if (day.Key == DayOfWeek.Thursday)
                        thursdayCount = daysCount / day.Value;
                    else if (day.Key == DayOfWeek.Friday)
                        fridayCount = daysCount / day.Value;
                    else if (day.Key == DayOfWeek.Saturday)
                        saturdayCount = daysCount / day.Value;
                    else if (day.Key == DayOfWeek.Sunday)
                        sundayCount = daysCount / day.Value;
                }

I'm still left with the initial error I get from the 1st query which is as follows:

The specified type member 'DayOfWeek' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported

You really are not supposed to radically change the question after an answer is posted. That's why it behooves you to provide all relevant information in the first place. Please read [ask] and take the [tour] — Ňɏssa Pøngjǣrdenlarp, Dec 23 '17 at 21:01
See [Entity Framework + DayOfWeek](https://stackoverflow.com/questions/40271588/entity-framework-dayofweek) — Ivan Stoev, Dec 24 '17 at 01:36

Camilo Terevinto · Accepted Answer · 2017-12-23T21:22:20.880

2

You don't need to do all of that checking, you can use a Dictionary<DayOfWeek, int> instead:

var weekDays = rad.SMSSentItems
    .Where(x => x.Status == "DELIVRD")
    .GroupBy(x => x.StatusDate.Value.DayOfWeek)
    .Select(g => new
    {
        DayOfWeek = g.Key,
        Count = g.Count()
    })
    .ToDictionary(x => x.DayOfWeek);

int mondayCount = weekDays[DayOfWeek.Monday];
int tuesdayCount = weekDays[DayOfWeek.Tuesday];
//...

If you are not sure whether there'll be a value for a given day, use TryGetValue instead:

weekDays.TryGetValue(DayOfWeek.Monday, out mondayCount);

If you want the average for a given day of week, you could use something like this:

foreach (var day in weekDays)
{
    int daysCount = rad.SMSSentItems
        .Where(x => x.StatusDate.Value.DayOfWeek == day.DayOfWeek
                 && x.Status == "DELIVRD")
        .Count();

    int dayAverage = day.Count / daysCount;
}

edited Dec 23 '17 at 21:22

answered Dec 23 '17 at 21:01

Camilo Terevinto

31,141
6
88
120

This will produce the total number of SMS's sent on a Day of the Week? How do I amend it to produce the average number of SMS's per Day of the Week? – Orion Dec 23 '17 at 21:04
2

So, EF is able to GroupBy DayOfWeek without any problems? I thought that was the essence of the question – ironstone13 Dec 23 '17 at 21:06
I get an error on the ToDictionary(); it says no overload for method 'ToDictionary' takes 0 arguments. – Orion Dec 23 '17 at 21:10
@Orion I have fixed that, it missed the key selector. It's not clear what you mean by average number – Camilo Terevinto Dec 23 '17 at 21:15
I've edited my code to use the key selector as '.ToDictionary(x => x.DayOfWeek, x => x.Count);' Average number is the average count of SMS's sent on Mondays, Tuesdays etc. where Status = 'DELIVRD' – Orion Dec 23 '17 at 21:19
I'm getting the following error on the average code part as follows: Error 6 'System.Collections.Generic.KeyValuePair' does not contain a definition for 'DayOfWeek' and no extension method 'DayOfWeek' accepting a first argument of type 'System.Collections.Generic.KeyValuePair' could be found (are you missing a using directive or an assembly reference?) – Orion Dec 23 '17 at 21:28
I think I need to convert the Key to DayOfWeek and the value to integer? – Orion Dec 23 '17 at 21:30
@CamiloTerevinto I've updated my code and got over the code errors. However I'm still left with my initial error from my question. This is coming from the 1st query. I've updated my question to factor in your code. – Orion Dec 23 '17 at 21:44
1

@CamiloTerevinto You're focusing on the wrong problem (although it's worth tackling it). You should use `SqlFunctions.DatePart("dw", x.StatusDate))`. – Gert Arnold Dec 24 '17 at 09:53
@GertArnold I can't seem to find SqlFunctions within the namespaces? Can you point me in the right direction? I've searched Google and found it should reside here: System.Data.Objects.SqlClient.SqlFunctions however it no longer exists there. I think it has been replaced with DbFunctions? Please advise. – Orion Dec 24 '17 at 14:10
@Orion That's because it should be `DbFunctions` as of EF6 – Camilo Terevinto Dec 24 '17 at 14:37
@CamiloTerevinto I used .AsEnumerable( instead and it worked. Thank you for your help. I have a separate issue now with the data not giving me the averages. Will post a new question for that. Thanks! – Orion Dec 24 '17 at 15:23
`AsEnumerable` is basically the same as calling `ToList`, it will transform the query into an in-memory query instead of doing it in the database, so that will hurt your performance a lot. – Camilo Terevinto Dec 24 '17 at 15:25
It's `System.Data.Entity.SqlServer.SqlFunctions`. Not `DbFunctions` as suggested. – Gert Arnold Dec 25 '17 at 11:53

The specified type member 'DayOfWeek' is not supported in LINQ

1 Answers1