Need help on developing LINQ Lambda expresssion to filter on a Schedule

Question

I am having a hard time finding the lambda expression to call on a list to correctly filter an object structure. I was hoping someone here could help out. I am using .NET 3.5, and LINQ and the object domain is set up from Linq to SQL DBML. I specifically want to use lambda expressions.

The object structure is one where persons have settings, and one setting is a Schedule name, which corresponds to another schedule class with a StartDate and End Date.

public class MyPerson
{
    public int ID { get; set; }
    public List<MySetting> Settings { get; set; }
}

public class MySetting
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
}

public class MySchedule
{
    public string ID { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

}

Schedules have Start and End Dates, and a person's State can be Active or Scheduled.

    List<MySchedule> schedules = new List<MySchedule>();
    MySchedule scheduleA = new MySchedule { ID = "ScheduleA", StartDate = Convert.ToDateTime("1/1/2008"), EndDate = Convert.ToDateTime("12/31/2008") };
    MySchedule scheduleB = new MySchedule { ID = "ScheduleB", StartDate = Convert.ToDateTime("1/1/2009"), EndDate = Convert.ToDateTime("12/31/2009") };
    schedules.Add(scheduleA);
    schedules.Add(scheduleB);

    List<MySetting> settingsJill = new List<MySetting>();
    MySetting settingFirstName = new MySetting { ID = 1, Name = "FirstName", Value = "Jill" };
    MySetting settingScheduleName = new MySetting { ID = 2, Name = "ScheduleName", Value = "ScheduleB" };
    MySetting settingState = new MySetting { ID = 3, Name = "State", Value = "Scheduled" };  // Jill uses ScheduleB
    settingsJill.Add(settingFirstName);
    settingsJill.Add(settingScheduleName);
    settingsJill.Add(settingState);

    List<MySetting> settingsBill = new List<MySetting>();
    settingFirstName = new MySetting { ID = 1, Name = "FirstName", Value = "Bill" };
    settingScheduleName = new MySetting { ID = 2, Name = "ScheduleName", Value = "ScheduleA" };
    settingState = new MySetting { ID = 3, Name = "State", Value = "Scheduled" };              // Bill is Scheduled last year
    settingsBill.Add(settingFirstName);
    settingsBill.Add(settingScheduleName);
    settingsBill.Add(settingState);

    List<MySetting> settingsJane = new List<MySetting>();
    settingFirstName = new MySetting { ID = 1, Name = "FirstName", Value = "Jane" };
    settingScheduleName = new MySetting { ID = 2, Name = "ScheduleName", Value = "ScheduleA" };
    settingState = new MySetting { ID = 3, Name = "State", Value = "Active" };              // Jane is Active
    settingsJane.Add(settingFirstName);
    settingsJane.Add(settingScheduleName);
    settingsJane.Add(settingState);

    List<MyPerson> persons = new List<MyPerson>();
    MyPerson Jane = new MyPerson { ID = 1, Settings = settingsJane };
    MyPerson Jill = new MyPerson { ID = 2, Settings = settingsJill };
    persons.Add(Jane);
    persons.Add(Jill);
    persons.Add(Bill);

I want to filter the persons List by creating a lambda expression that will return persons with State = "Active" OR with a ScheduleName containing a current Date between the Start and End Date. In other words, Jill should appear in the filtered list since she is Scheduled, and she uses ScheduleB, and ScheduleB is for 2009, and today is 6/17/2009.

I am starting with

List<MyPerson> filter = persons.Select (  // and this is where I get stuck.

Thanks in advance for any help.

Answer 1

One problem is that you're starting with a Select. That's for projection - you're just trying to filter, so you only need Where.

Another problem is that your MyPerson type doesn't include a ScheduleName or State property. It's really not clear how it's all meant to hang together. If you could correct your example, I'm sure we can come up with the filter.

Answer 2

You need to rethink how you are storing the information. .NET Collectionns aren't SQL tables, and you shouldn't use them like so. That being said, here's the query you wanted:

Persons.Where(person => 
  person.Settings.Any(setting=>
      (setting.Name == "State" && setting.Value=="Active")) ||
  person.Settings
      .Where(setting=> setting.Name == "ScheduleName")
      .Any(setting => { var sch = schedules
           .First(schedule=>schedule.ID == setting.Value);
           return sch.StartDate < DateTime.Now && sch.EndDate > DateTime.Now; 
      })   
)

Imagine if you had a data structure like:

class Person { 
    Dictionary<string, string> Settings;
    List<MySchedule> Schedules;
}

then your query would be

Persons.Where(person =>
   Person.Settings["State"] == "Active" ||
   Person.Schedules.Any(schedule => 
       Datetime.Now > schedule.StartTime &&
       DateTime.Now < schedule.EndTime
   )
)