Geek Answers Handbook

LINQ to Entities does not recognize method

I have a problem with Linq to Entities that I cannot find a workaround for.

This is my code:

var queryResult = result.Where(x => x.FollowedUp.Value.GetWeekFromDateTime() == DateTime.Now.GetWeekFromDateTime()).Select(x => x);

and my extension method:

  public static int GetWeekFromDateTime(this DateTime date) { return System.Threading.Thread.CurrentThread.CurrentCulture.Calendar.GetWeekOfYear(date, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday); }

Problem:

I get "LINQ to Entities does not recognize the GetWeekFromDateTime method", and I understand why, because it is trying to translate my code into sql code and "GetWeekFromDateTime" does not exist. But how do I do this? I want to compare the week number from the date that I get from my database to this week number.

Thanks!

+2
source share
3 answers

If you have a decent index in the FollowedUp field, you can configure your algorithm here, and instead simply calculate through C # what are the minimum and minimum dates for the current week, and make your request as value >= min && value <= max instead . This way you are not doing calculations on every single row of the database.

But for a more direct answer to your question, even if I do not recommend doing it this way in your specific case: you can create an SQL function that performs the calculations here, import it into the data context and call it inside your query instead.

This will cause each line passing through this function to keep in mind. In addition, even if you still want to do it this way, you must change the way you get the current week; You only need to get this value once and use a variable for it in the query ... there is no need to try to recalculate it for each row.

+7
source

Linq-to-Entities does not currently support culture-specific query functions. See the accepted answer to this question for more details. I believe that you will have to pull the entire result set into a collection other than L2E, which you can then filter using the GetWeekFromDateTime method.

+1
source

You will need to get all the records for the client as an array or list, and then filter them using the query you showed while working in the array (instead of LINQ to SQL), or you will have to convert it to syntax that LINQ to SQL can understand :

  int thisWeek = (DateTime.Now.DayOfYear + (int)(new DateTime(DateTime.Now.Year, 1, 1).DayOfWeek)) / 7; var queryResult = (from row in result let WeekNum = (row.FollowedUp.Value.DayOfYear + (int)(new DateTime(row.FollowedUp.Value.Year, 1, 1).DayOfWeek)) / 7 where WeekNum == thisWeek select row);

LINQ to SQL is actually smart enough to convert the WeekNum expression into SQL code as follows:

 (DATEPART(DayOfYear, [t0].[FollowedUp]) + (CONVERT(Int,(DATEPART(dw, CONVERT(DATETIME, CONVERT(NCHAR(2), 1) + ('/' + (CONVERT(NCHAR(2), 1) + ('/' + CONVERT(NCHAR(4), DATEPART(Year, [t0].[FollowedUp]))))), 101)) + (@@DATEFIRST) + 6) % 7))) / 7
0
source

More articles:


All Articles