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.

CREATE PROCEDURE AllOrdersInWeek52
AS
SELECT * FROM Orders
WHERE DATEPART(ww, OrderDate) = 52

With that, we can included the procedure in our diagram and query it using Linq to SQL. The code below calls the stored procedure and outputs all the dates which match.

public void LinqSproc()
{
    Console.WriteLine(“LinqSproc”);
    Console.WriteLine(“—————————————————————“);
    DataClasses1DataContext dc = new DataClasses1DataContext();

    var query = dc.AllOrdersInWeek52();

    foreach (var item in query)
    {
        Console.WriteLine(item.OrderDate);
    }
}

The results returned:

ListAllOrdersWithDatesInWeek52
—————————————-
23/12/1996 00:00:00
23/12/1996 00:00:00
24/12/1996 00:00:00
25/12/1996 00:00:00
25/12/1996 00:00:00
26/12/1996 00:00:00
27/12/1996 00:00:00
27/12/1996 00:00:00
22/12/1997 00:00:00
22/12/1997 00:00:00
22/12/1997 00:00:00
23/12/1997 00:00:00
23/12/1997 00:00:00
24/12/1997 00:00:00
24/12/1997 00:00:00
24/12/1997 00:00:00
25/12/1997 00:00:00
25/12/1997 00:00:00
26/12/1997 00:00:00
26/12/1997 00:00:00
26/12/1997 00:00:00

Another approach would be to use a Function.  This gives us more control and flexibility within our database.

CREATE FUNCTION dbo.AllOrdersInWeek52Function()
    RETURNS TABLE
AS
RETURN (SELECT * FROM Orders
        WHERE DATEPART(ww, OrderDate) = 52)

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().

public void ListAllOrdersWithDatesInWeek52()
{
    Console.WriteLine(“ListAllOrdersWithDatesInWeek52”);
    Console.WriteLine(“—————————————————————“);
    DataClasses1DataContext dc = new DataClasses1DataContext();

    var query = from o in dc.Orders.AsEnumerable()
                where WeekOf(o.OrderDate) == 52
                select o;

    foreach (var item in query)
    {
        Console.WriteLine(item.OrderDate);
    }
}

private int WeekOf(DateTime? nullable)
{
    if (nullable.HasValue)
    {
        GregorianCalendar gCalendar = new GregorianCalendar();
        int WeekNumber = gCalendar.GetWeekOfYear(nullable.Value, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
        return WeekNumber;
    }
    else
        return 0;
}

As this is occurring for every row in the database, performance will take a hit.  The best solution, is to use a scalar function within the database.

CREATE FUNCTION WeekOfYear(@Date DateTime)
RETURNS Int
AS
BEGIN
RETURN (CAST(DATEPART(ww, @Date) AS INT))
END

Which we can use within our query.

DataClasses1DataContext dc = new DataClasses1DataContext();
var q = from o in dc.Orders
        where dc.WeekOfYear(o.OrderDate) == 23
        select o;

foreach (var item in q)
{
    Console.WriteLine(item.OrderID);
}

This will execute the function inside of the database, resulting in much better performance. The sql produced would look like this:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [dbo].[WeekOfYear]([t0].[OrderDate]) = @p0

I’ve highlighted the important part.  By using a function, the result is a nice clean quick execution of the query.

Original Question: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1