MVC LINQ Not recognised method

Question

I've got a linq query extracting data from a table and I want to, being given a Date, convert it to week ( for example what week of the year it is ).

And the GetWeekofYear function:

 private int GetWeekOfYear(DateTime d)
 {
     var cal = System.Globalization.DateTimeFormatInfo.CurrentInfo.Calendar;
     return cal.GetWeekOfYear(new DateTime(d.Year, d.Month, 1), System.Globalization.CalendarWeekRule.FirstDay, System.DayOfWeek.Sunday);
 }

In its current state, when I'm trying to test it ( using Postman / Fiddler ) I am getting the following error:

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

Answer 1

The error occurs because Linq2Sql can not translate the GetWeekOfYear method to SQL.

Try the following:

• select raw data instead into QuestionaireDetailsDTO

  select new QuestionaireDetailsDTO() {
      DepartureDate = transport.DepartureDate 
  };
  

• add a getter to QuestionaireDetailsDTO that does the calculation:

  public string Week => GetWeekOfYear(DepartureDate);
  

This way the conversion happens in memory instead of on the DB.

If the GetWeekOfYear method resides in a project that is not accessible by the consumer of the DTO, add a postprocessing step instead after you have selected the DTOs from the DB.

foreach (var result in query) {
     result.Week = GetWeekOfYear(result.DepartureDate);
}