c# - Entity Framework get by weeknumber query -

i'm creating application sportsclub. on homepage want show overview of al games going played week. problem i'm facing on application layer (c#) i've set first day of week on monday. sql server thinks first day of week on sunday. sql server wrong. how can tell query first day of week monday? below can find code.

public static class datetimeextensions {        // extension method weeknumber 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 games, use following query:

var weeknumber = datetime.now.weeknumber(); var games = db.games               .where(x => sqlfunctions.datepart("week", x.date) == weeknumber)               .tolist(); 

thanks suggestions did own way. below can find way did it.

first, made 2 extension methods on 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 methods range in 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 way wont dependent on database functions. think thing application logic in application. database shouldn't care dates start , end date week.