Adding where clause to nested Linq selects

Question

I'm still new to Linq so if you see something I really shouldn't be doing, please feel free to suggest a change.

I am working on a new system to allow officers to sign up for overtime. Part of the data is displayed on a map with search criteria filtering unwanted positions. In order to make the data easier to work with, it is read into a hierarchy object structure using Linq. In this example, a job can contain multiple shifts and each shift can have multiple positions available. The Linq statement to read them in looks like the following.

        var jobs = (from j in db.Job
                    join s in db.Shift on j.Id equals s.JobId into shifts
                    select new JobSearchResult
                    {
                        JobNumber = j.Id,
                        Name = j.JobName,
                        Latitude = j.LocationLatitude,
                        Longitude = j.LocationLongitude,
                        Address = j.AddressLine1,
                        Shifts = (from shift in shifts
                                  join p in db.Position on shift.Id equals p.ShiftId into positions
                                  select new ShiftSearchResult
                                  {
                                      Id = shift.Id,
                                      Title = shift.ShiftTitle,
                                      StartTime = shift.StartTime,
                                      EndTime = shift.EndTime,
                                      Positions = (from position in positions
                                                   select new PositionSearchResult
                                                   {
                                                       Id = position.Id,
                                                       Status = position.Status
                                                   }).ToList()
                                  }).ToList()
                    });

That works fine and has been tested. There may be a better way to do it and if you know of a way, feel free to suggest. My problem is this. After the query is created, search criteria will be added. I know that I could add it when the query is created but for this its easier to do it after. Now, I can easy add criteria that looks like this.

jobs = jobs.Where(j => j.JobNumber == 1234);

However, I am having trouble figuring out how to do the same for Shifts or Positions. In other words, how would I could it to add the condition that a shift starts after a particular time? The following example is what I am trying to accomplish but will not (obviously) work.

jobs = jobs.Shifts.Where(s = s.StartTime > JobSearch.StartTime)      //JobSearch.StartTime is a form variable.

Anyone have any suggestions?

Answer 1

Step 1: create associations so you can have the joins hidden behind EntitySet properties. http://msdn.microsoft.com/en-us/library/bb629295.aspx

Step 2: construct your filters. You have 3 queryables and the possibility of filter interaction. Specify the innermost filter first so that the outer filters may make use of them.

Here are all jobs (unfiltered). Each job has only the shifts with 3 open positions. Each shift has those open positions.

Expression<Func<Position, bool>> PositionFilterExpression =
  p => p.Status == "Open";

Expression<Func<Shift, bool>> ShiftFilterExpression =
  s => s.Positions.Where(PositionFilterExpression).Count == 3  

Expression<Func<Job, bool>> JobFilterExpression =
  j => true

Step 3: put it all together:

   List<JobSearchResult> jobs = db.Jobs
     .Where(JobFilterExpression)
     .Select(j => new JobSearchResult
     { 
       JobNumber = j.Id, 
       Name = j.JobName, 
       Latitude = j.LocationLatitude, 
       Longitude = j.LocationLongitude, 
       Address = j.AddressLine1, 
       Shifts = j.Shifts
         .Where(ShiftFilterExpression)
         .Select(s => new ShiftSearchResult
         {
           Id = s.Id,
           Title = s.ShiftTitle,
           StartTime = s.StartTime,
           EndTime = s.EndTime,
           Positions = s.Positions
             .Where(PositionFilterExpression)
             .Select(p => new PositionSearchResult
             {
               Id = position.Id,
               Status = position.Status
             })
             .ToList() 
         })
         .ToList()
     })
     .ToList();