CODEWITHSUNDEEP
c#.netentity-framework
syncis
syncis

Reputation: 1433

LINQ to Entities does not recognize the method

I have a problem with Linq to Entities that I can't 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 extention method is :

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

Problem :

I am getting "LINQ to Entities does not recognize the method GetWeekFromDateTime" and I understand why because it's trying to translate my code into sql code and of "GetWeekFromDateTime" does not exist. But how do I do it? I want to compare the weeknumber from date that I get from my database to this weeknumber.

Thank you!

Upvotes: 2

Views: 1919

Answers (3)

BlueMonkMN
BlueMonkMN

Reputation: 25601

You'll either have to retrieve all the records to the client as an array or list, then filter them using a query like you have shown operating on the array (instead of onver LINQ to SQL) or you'll have to convert it to a 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 to SQL code like this:

(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

Upvotes: 0

AJ.
AJ.

Reputation: 16729

Linq-to-Entities does not currently support culture-specific querying features. See the accepted answer on this question for more detail. I believe you will have to pull back your entire result set into a non-L2E collection, which you can then filter with your GetWeekFromDateTime method.

Upvotes: 1

Andrew Barber
Andrew Barber

Reputation: 40160

If you have a decent index on the FollowedUp field, you might want to adjust your algo here to instead just calculate via C# what the Min and Max dates are for the current week, and do your query as a value >= min && value <= max instead. That way you aren't doing a calculation on every single row of the database.

But to answer your question more directly, even if I don't recommend doing it this way in your particular case: You could create an SQL function which does the calculation here, import it to your data context, and call it inside your query instead.

This would result in every row going through that function, keep in mind. Also, even if you still want to do it this way, you should change how you get getting the current week, too; You only need to get that value once, and use a variable for it in the query... no need to try to recalculate it for every row.

Upvotes: 7

Related Questions