c# - GetWeekOfYear with Entity Framework

Question

I've a column in my table called Date, and I need to compare this date's WeekOfTheYear with DateTime.Now's WeekOfTheYear,

If I give like this,

var cal = CultureInfo.CurrentCulture.Calendar;

int week = cal.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);

I am getting 26 here. The same way, in my Entity Framework, I need to compare this week's data, for this I tried like,

entities.WorkingDays.Where(a => 
             cal.GetWeekOfYear(a.DATE,CalendarWeekRule.FirstDay,DayOfWeek.Sunday)
             == cal.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstDay,
                                                 DayOfWeek.Sunday)

when I run the query like this, am getting error like,

"LINQ to Entities does not recognize the method 'Int32 GetWeekOfYear (System.DateTime, System.Globalization.CalendarWeekRule, System.DayOfWeek)' method, and this method cannot be translated into a store expression."

How can I fetch the data for weekly basis here, can any one help me out here....thanks in advance

Answer 1

Call .ToList() first. Like this:

entities.WorkingDays.ToList().Where(a => 
             cal.GetWeekOfYear(a.DATE,CalendarWeekRule.FirstDay,DayOfWeek.Sunday)
             == cal.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstDay,
                                                 DayOfWeek.Sunday)

See this post for duplicate issue. Basically, the data needs to be in memory before using your GetWeekOfYear functions.

As noted in the comments, this does bring the whole "WorkingDays" table into memory and therefore fetching more data than needed from the DB. Sometimes this is more preferable to using Stored Procedures and sometimes not, depending on the amount of data and other factors based on your application/database architecture.