Linq query DateTime.Date.DayOfWeek

Question

Trying to get only Thursdays of 1 year back

using (var context = new Context1())
{
      // Get 1 year back only Thursday
      var oneYearEarlier = DateTime.Now.AddYears(-1);

      var query = (from c in context.Daily25Data
                   where c.Date > oneYearEarlier && c.Date.DayOfWeek == DayOfWeek.Thursday
                   select c).ToList();

      Console.WriteLine(query);
}

and getting

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

Seems linq to entity can't translate `DateTime.DayOfWeek` into SQL, which is strange as there are sql methods for that. — René Vogt, Mar 30 '16 at 15:29

scottjustin5000 · Answer 1 · 2016-03-30T15:35:17.227

8

This is a clever solution using EntityFunctions.DiffDays

from his post:

DateTime firstSunday = new DateTime(1753, 1, 7); 
var bookings = from b in this.db.Bookings 
               where EntityFunctions.DiffDays(firstSunday, b.StartDateTime) % 7 == 1 
               select b;

edited Mar 30 '16 at 15:35

answered Mar 30 '16 at 15:33

scottjustin5000

1,316
12
10

2

Nice answer +1 You could eventually replace `EntityFunctions` which now are marked as obsolete with `DbFunctions`. – Ivan Stoev Mar 30 '16 at 16:43
DbFunctions throws "Nullable object must have values" exception – SZT Sep 27 '20 at 06:23

Nkosi · Answer 2 · 2016-03-30T20:14:48.703

0

LINQ will not recognize the second part of your query into SQL. You need to break your query up in order to perform the filter.

using (var context = new algoventurelab_db1Context())
{
      // Go one year back from current date
      var startDate = DateTime.Today.AddYears(-1);
      // This will get all dates in context greater than start date.
      var query1 = (from c in context.Daily25Data
                   where c.Date >= startDate
                   select c).AsEnumerable();
      //this will filter only thurdays
      var query = from c in query1
                  where c.Date.DayOfWeek == DayOfWeek.Thursday
                  select c).ToList();

      Console.WriteLine(query);
}

edited Mar 30 '16 at 20:14

answered Mar 30 '16 at 15:31

Nkosi

235,767
35
427
472

Instead of using `ToList` you could instead use `AsEnumerable`. That way you don't need to create the intermediate list. – juharr Mar 30 '16 at 15:40
Current date `2016 mar 30 is Wednesday`, so `.AddYears(-1)` will give you `2015 mar 30 is Wednesday` that is monday. not `Wednesday` as you expected – sujith karivelil Mar 30 '16 at 15:41
I thought the first ToList was to materialize the query. It would work with `AsEnumerable`? If so I've just learned something new. – Nkosi Mar 30 '16 at 15:42
@Nkosi: no i was misunderstanded, its fine – sujith karivelil Mar 30 '16 at 15:54
1

`AsEnumerable` just switches it over from a `IQueryable` to an `IEnumerable` so that any Linq operations done after it will be done in Linq-to-Objects and thus not converted to SQL. – juharr Mar 30 '16 at 17:47

Linq query DateTime.Date.DayOfWeek

2 Answers2

Linked