Linq, Lambda : Get all the records for this week

Question

How can I write Linq/lambda expression for selecting all the records for this week.

1 week = Sunday to Saturday(inclusive).

For example if today is Wednesday I should get all the records of this week i.e from Sunday to Wednesday.

Well, I haven't wrote code completely and also not tested. I am writing it and just got stuck at "thisWeek".

[HttpPost]
public ActionResult Purchase(PurchaseSearchVM vm)
{
    var filter = new PurchaseFilterRepository();
    var model = filter.FilterPurchase(vm);
    return View(model);
}

public IQueryable<Purchase> FilterPurchase(PurchaseSearchVM vm)
{
    var result = db.Purchases.AsQueryable();

    if (vm != null)
    {
        if (!string.IsNullOrEmpty(vm.option))
        {
            if (vm.option == "today")
            {
                result = result.Where(p => p.Date.Date == DateTime.Now.Date);
            }
            else if (vm.option == "yesterday")
            {
                result = result.Where(p => p.Date.Date == DateTime.Now.Date.AddDays(-1));
            }                    
            else if (vm.option == "thisWeek")
            {
                //help needed here
                result = result.Where(p=> p.Date.)
            }
            else if (vm.option == "thisMonth") { }
            else if (vm.option == "lastMonth") { }
            else if (vm.option == "thisYear") { }
            else if (vm.option == "lastYear") { }
        }

        if (!string.IsNullOrEmpty(vm.supplier))
        {
            var value = vm.supplier;
            //query here
        }

        if (vm.fromDate != null || vm.toDate != null)
        {
            if (vm.fromDate != null && vm.toDate == null)
            {
                //query here 
            }
            else if (vm.fromDate == null && vm.toDate != null)
            {
                //query here
            }
            else if (vm.fromDate != null && vm.toDate != null)
            {
                //query here 
            }
        }

        if (vm.IsPaid != null) 
        {
            //query here
        }
    }
    return result;
}

UI

[This answer](http://stackoverflow.com/questions/38039/how-can-i-get-the-datetime-for-the-start-of-the-week) shows an extension method you can use to get the date of the start of the week. Then your query would be `.Where(p => p.Date.Date >= DateTime.Now.StartOfWeek(DayOfWeek.Sunday) && p.Date.Date <= DateTime.Now.Date);` Note you can use `DateTime.Today` instead of `DateTime.Now.Date` — , Oct 29 '15 at 07:07

score 1 · Answer 1 · answered Oct 29 '15 at 07:10

You can calculate startDayOfWeek and endDayOfWeek like this:-

DateTime startDayOfWeek= DateTime.Today.AddDays(-1 * (int)(DateTime.Today.DayOfWeek));
DateTime endDayOfWeek = DateTime.Today.AddDays(6 - (int)DateTime.Today.DayOfWeek);

Then simply apply a filter using Where:-

var result = vm.Where(x => x.myDate >= startDayOfWeek && x.myDate <= endDayOfWeek );

score 0 · Answer 2 · answered Oct 29 '15 at 07:11

Please try with the below code snippet.

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Purchase> dates = new List<Purchase>();
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(-6) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(-5) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(-4) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(-3) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(-2) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(-1) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(0) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(1) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(2) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(7) });
            dates.Add(new Purchase() { FromDate = DateTime.Now.AddDays(8) });

            var test = dates.Where(i => i.FromDate >= DateTime.Now.StartOfWeek(DayOfWeek.Sunday) && i.FromDate <= DateTime.Now.StartOfWeek(DayOfWeek.Sunday).AddDays(7)).ToList();
            // you can access the this weeks dates here
            Console.ReadLine();
        } 
    }

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

            return dt.AddDays(-1 * diff).Date;
        }
    }
}

score 0 · Answer 3 · answered Oct 29 '15 at 07:23

You could write it as follows to remove the amout of days until the beginning of the week:

result = result.Where(
                p=> p.Date >= p.Date.AddDays(-(p.Date.DayOfWeek - DayOfWeek.Sunday) &&
                p.Date <= DateTime.Today.Date);

or to have it more globally:

var firstDayOfWeek = CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek;
result = result.Where(
                p=> p.Date >= p.Date.AddDays(-(p.Date.DayOfWeek - firstDayOfWeek) &&
                p.Date <= DateTime.Today.Date);

Linq, Lambda : Get all the records for this week

3 Answers3