DATEPART(WEEK,…) With Linq to SQL

An interesting question was raised on the Linq forums about how to query a date column based on the which week number the date is in.

If we was to write this in SQL, we would use the DATEPART keyword within our WHERE clause. In Northwind, if we wanted to return all the orders which occurred in week 52 we would run the following query.

SELECT * FROM Orders WHERE DATEPART(ww, OrderDate) = 52

However, in .Net there isn’t a simply way to access the Week via DateTime. This makes it difficult for Linq to translate into SQL.

One approach you could take is the easy route and do the query in the database.

That is all very easy because we are just using SQL and calling into our database, if we wanted both could accept the week number as a parameter. If we wanted to do this solely using .Net we would have to use a custom method to return the week number for the date and then query based on that. The problem with using a custom method is that the query must be performed in memory, as such we need to use the .AsEnumerable().