12-week staffing forecast LINQ query

I have a LINQ query that I am having trouble optimizing and takes about 5.5 seconds to run. I am using a view called StaffingResourceData and a table called StaffingForecasts.

Each StaffingResource has a ResourceId, a Division, and a Type. A StaffingForecast has a ResourceId, Project, Date (represents a Monday of a week), Hours. A StaffingResource can have 0-many StaffingForecasts.

For each StaffingResource, I need a list of their total forecasted hours for the next 12 weeks. Here is what I have right now:

// Get list of dates
var dates = new List();
var start = Utilities.GetStartOfWeek(DateTime.Today);
for (var i = 0; i  x.Date >= start && x.Date  new ChartDateModel
                     {
                         Date = d,
                         Available = (g2.Where(f => f.Date == d).Any() ? g2.Where(f => f.Date == d).Sum(f => f.Hours) : 0) < 24
                     }).ToList()
                 })
               .ToList();

Any ideas on how I could speed this up?

You are combining an IQueryable with a local sequence, dates. It is possible to do that, but Entity Framework1 needs a lot of code to convert the sequence into something that behaves like a SQL table. I'm sure this is a major performance killer.

From what I see, you can do without this local sequence. You know the begin and end date. So it's possible to get the forecasts between those dates. If you group those forecasts by their Date, you achieve the same grouping:

from r in context.StaffingResourceDatas 
where r.EmployeeId != null && !exclusionList.Contains(r.ResourceTitleId)
let forecasts = context.StaffingForecasts
                       .Where(f => r.ResourceId == f.ResourceId
                                && f.Date >= start && f.Date  f.Hours) < 24
             })
}

1 I assume this is EF, because Linq-to-Sql doesn't allow local sequences in a LINQ query.

StackExchange Code Review Q#109633, answer score: 2