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&lt;DateTime&gt;();
var start = Utilities.GetStartOfWeek(DateTime.Today);
for (var i = 0; i &lt; 12; i++)
{
    dates.Add(start.AddDays(i * 7));
}
var end = dates[11];

// Get resources
var resources = (from r in context.StaffingResourceDatas
                 where r.EmployeeId != null
                     &amp;&amp; !exclusionList.Contains(r.ResourceTitleId)
                 join f in context.StaffingForecasts.Where(x =&gt; x.Date &gt;= start &amp;&amp; x.Date &lt;= end) on r.ResourceId equals f.ResourceId into g1
                 from f in g1.DefaultIfEmpty()
                 group new { f.Date, f.Hours } by r into g2
                 select new ChartResourceModel
                 {
                     ResourceId = g2.Key.ResourceId,
                     Division = g2.Key.ResourceDivision,
                     Type = g2.Key.ResourceType,
                     Dates = dates.Select(d =&gt; new ChartDateModel
                     {
                         Date = d,
                         Available = (g2.Where(f =&gt; f.Date == d).Any() ? g2.Where(f =&gt; f.Date == d).Sum(f =&gt; f.Hours) : 0) &lt; 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 &amp;&amp; !exclusionList.Contains(r.ResourceTitleId)
let forecasts = context.StaffingForecasts
                       .Where(f =&gt; r.ResourceId == f.ResourceId
                                &amp;&amp; f.Date &gt;= start &amp;&amp; f.Date &lt;= end)
select new ChartResourceModel
{
    ResourceId = r.Key.ResourceId,
    Division = r.Key.ResourceDivision,
    Type = r.Key.ResourceType,
    Dates = (from fc in forecasts
             group fc by d.Date into fcg
             select new ChartDateModel
             {
                 Date = fcg.Key,
                 Available = fcg.Sum(f =&gt; f.Hours) &lt; 24
             })
}




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