CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-frameworklinq-to-entities
Thijs
Thijs

Reputation: 3055

Entity Framework get by weeknumber query

I'm creating an application for a sportsclub. On the homepage I want to show an overview of al the games that are going to be played this week. The problem I'm facing is that on the application layer (c#) I've set my first day of the week on Monday. SQL Server thinks my first day of the week is on Sunday. SQL Server is wrong. How can tell my query that the first day of the week is Monday? Below you can find my code.

public static class DateTimeExtensions
{   
    // Extension method to get the weeknumber from a date
    public static int Weeknumber(this DateTime date)
    {
        var cal = System.Globalization.DateTimeFormatInfo.CurrentInfo.Calendar;
        return cal.GetWeekOfYear(date, 
                      System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday);
    }
}

To fetch the games, I use the following query:

var weeknumber = DateTime.Now.Weeknumber();
var games = db.Games
              .Where(x => SqlFunctions.DatePart("week", x.Date) == weeknumber)
              .ToList();

Upvotes: 0

Views: 2468

Answers (3)

Thijs
Thijs

Reputation: 3055

Thanks for the suggestions but I did it my own way. Below you can find the way I did it.

First, I made 2 extension methods on the DateTime object:

public static class DateTimeExtensions
{
    public static DateTime StartOfWeek(this DateTime dt, DayOfWeek startOfWeek = DayOfWeek.Monday)
    {
        int diff = dt.DayOfWeek - startOfWeek;
        if (diff < 0)
        {
            diff += 7;
        }

        return dt.AddDays(-1 * diff).Date;
    }
    public static DateTime EndOfWeek(this DateTime dt, DayOfWeek endOfWeek = DayOfWeek.Sunday)
    {
        if (dt.DayOfWeek == endOfWeek)
        {
            return dt.Date.Date.AddDays(1).AddMilliseconds(-1);
        }
        else
        {
            var diff = dt.DayOfWeek - endOfWeek;
            return dt.AddDays(7 - diff).Date.AddDays(1).AddMilliseconds(-1);
        }
    }
}

I used those methods to get a range in the query:

var start = DateTime.Now.StartOfWeek();
var end = DateTime.Now.EndOfWeek();
var games = db.Games
            .Where(x => x.Date >= start && x.Date <= end )
            .OrderBy(x => x.Team.MinAge)
            .ToList();

I used this way so I wont be dependent on database functions. Also I think it is a good thing that my application logic is actually in the application. The database shouldn't care what dates are the start and end date for a week.

Upvotes: 4

JohnB
JohnB

Reputation: 1793

You could also subtract 1 day from x.Date before taking the date part. If you were unable to adjust the database to do so. This brings Sunday back to the previous week and makes Monday the first day.

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109252

You can change the first day of the week by the t-SQL statement SET DATEFIRST. For Monday this is:

SET DATEFIRST 1;

Note however, that this change is scoped to a connection, so you either have to set it each time EF opens a connection, or set the connection culture to have a different default for the first day of the week. For more background, see How to Set DateFirst SQL Function Return for a SQL Server Database Scope Permanent

Upvotes: 0

Related Questions