LINQ - how to get next three business days excluding dates in table?

Question

I need to find the next three available business days for a scheduling application. What is available depends upon excluded dates in a table. So...as long as the date is not in my table and not a Saturday or Sunday, I want the next three. I'd like to find an efficient way of doing this.

I need to return a List<DateTime>. The table is simple - ExcludedDates has an ID and a DateTime with the excluded date.

I'd like to have a single LINQ query expression but can't figure it out...thanks to all in advance and I apologize if this is trivial or obvious - it isn't to me.

I'm not sure if you can do this purely with linq because of the weekend exclusion requirement. You have might to run this in a limited loop checking each week until you have gathered three open days. — Brain2000, Jul 25 '11 at 18:38
Take a look at this post. It has the weekend part of what you are looking for. http://stackoverflow.com/questions/1617049/calculate-the-number-of-business-days-between-two-dates — Brian Dishaw, Jul 25 '11 at 18:38
And to extend on that, you could get your list of excluded dates and do another `.Where(d => !dateList.Contains(d))` (or something like that...) and then a `.Take(3)` — GalacticCowboy, Jul 25 '11 at 18:43

Chaim Zonnenberg · Accepted Answer · 2011-07-25T20:26:19.943

8

Try this...

  DateTime start = DateTime.Now.Date;
  var result = Enumerable.Range(1, 10) // make this '10' higher if necessary (I assume you only exclude non-workingdays like Christmas and Easter)
                    .Select(offset => start.AddDays(offset))
                    .Where(date => !( date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek== DayOfWeek.Sunday))
                    .Where(d=> !exceptionTable.Any(date => date == d))
                    .Take(3).ToList();

edited Jul 25 '11 at 20:26

answered Jul 25 '11 at 18:43

Chaim Zonnenberg

1,793
13
10

This looks like it may work for me. However, the second .Where() is throwing me. Any() uses the values from my exception table as input for the lambda, but I don't know how to access the dates generated toward the beginning in the predicate. The d var in the code does not reference anything (The name "d" does not exist in the current context). Sorry again for the noob questions, but I can't quite get this figured out :/ – CircusNinja Jul 25 '11 at 20:24
Sorry, I forgot to add the 'd =>' part – Chaim Zonnenberg Jul 25 '11 at 20:27

Joe · Answer 2 · 2011-07-25T18:48:41.430

This assumes that a month will be reasonable depending on your excluded dates.

        DateTime date = DateTime.Today;
        // first generate all dates in the month of 'date'
        var dates = Enumerable.Range(1, DateTime.DaysInMonth(date.Year, date.Month)).Select(n => new DateTime(date.Year, date.Month, n));
        // then filter the only the start of weeks
        var results = (from d in dates
                       where d.DayOfWeek != DayOfWeek.Saturday && d.DayOfWeek != DayOfWeek.Saturday && !excludes.Any(i => i.DateTime.Date == d.Date) && date < d
                       select d).Take(3);

score 0 · Answer 3 · answered Jul 25 '11 at 18:45

List<DateTime> result = (from i in Enumerable.Range(1, excludeTable.Rows.Count + 6)
        let date = inputDate.AddDays(i)
        where date.DayOfWeek != DayOfWeek.Saturday &&
              date.DayOfWeek != DayOfWeek.Sunday &&
              !excludeTable.Rows.Cast<DataRow>().Select(r => (DateTime) r["ExcludeDate"]).Contains(date)
        select date).Take(3).ToList();

excludeTable.Rows.Count + 6 is to cover the worst case where you skip over every thing in the excludeTable and then you have to skip over another weekend.

score 0 · Answer 4 · answered Jul 25 '11 at 19:42

var excludedList = new List<long>() { DateTime.Parse("2011-07-27").Ticks };

var week = new List<long>(){
                DateTime.Now.Date.Ticks, 
                DateTime.Now.Date.AddDays(1).Ticks,
                DateTime.Now.Date.AddDays(2).Ticks,
                DateTime.Now.Date.AddDays(3).Ticks,
                DateTime.Now.Date.AddDays(4).Ticks,
                DateTime.Now.Date.AddDays(5).Ticks,
                DateTime.Now.Date.AddDays(6).Ticks,
                DateTime.Now.Date.AddDays(7).Ticks,
                DateTime.Now.Date.AddDays(8).Ticks
            };

var available = (from d in week.Except(excludedList)
                 where new DateTime(d).DayOfWeek != DayOfWeek.Saturday && new DateTime(d).DayOfWeek != DayOfWeek.Sunday
                 select new DateTime(d)).Take(3);

foreach (var a in available)
     Console.WriteLine(a.ToString());

LINQ - how to get next three business days excluding dates in table?

4 Answers4