My application has a fairly important query that is used in a lot of places. Unfortunately it takes about 14 seconds to run, so I'd like to find a way of possibly improving it.

The application receives incidents (right now there's about 70,000 total incidents, and about 16,000 incidents in this query), with each incident having a range of different flags. The purpose of the query is to get a monthly trend of the incidents and their flags. For example:

Month |  Flagged  |  Falls  |  FallsWithInjury  | .... 
-------------------------------------------------------
Jan        10          3            1 
Feb        15          6            0 
Mar        14          2            2 
....

which I can then graph client side.

In the following query, FacilityIds are a list of facilities I want to load incidents for (about 80 id's in the array). The start and end filters are 6 months apart.

var query = from i in Db.Incidents.Where(x => FacilityIds.Contains(x.FacilityId))
            where i.IncidentDate >= start && i.IncidentDate <= end
            group i by new { i.IncidentDate.Year, i.IncidentDate.Month } into incidentsByMonth
            let date = incidentsByMonth.Key
            select new MonthlyStat
                        {
                            Year = date.Year,
                            Month = date.Month,
                            AllIncidents = incidentsByMonth.Count(),
                            Flagged = incidentsByMonth.Count(ibm => ibm.IsSerious),
                            SentToHospital = incidentsByMonth.Count(x => x.Treatments.Any(it => it.TreatmentTypeId == (int)TreatmentTypes.Senttohospital)),
                            FallsWithInjuries = incidentsByMonth.Count(ibm => ibm.IncidentTypes.Any(it => it.IncidentTypeId == (int)IncidentTypes.Fall) && ibm.InjuryTypes.Any(inj => inj.InjuryTypeId != (int)InjuryTypes.None)),
                            FallsWithFracture = incidentsByMonth.Count(ibm => ibm.IncidentTypes.Any(it => it.IncidentTypeId == (int)IncidentTypes.Fall) && ibm.InjuryTypes.Any(inj => inj.InjuryTypeId == (int)InjuryTypes.Fracture)),
                            IncidentTypes = incidentsByMonth.SelectMany(ibm => ibm.IncidentTypes).GroupBy(it => new { it.IncidentTypeId, it.Description }).Select(it => new IncidentProperty<IncidentTypes>
                            {
                                Key = (IncidentTypes)it.Key.IncidentTypeId,
                                Count = it.Count()
                            }),
                            InjuryTypes = incidentsByMonth.SelectMany(ibm => ibm.InjuryTypes).GroupBy(it => new { it.InjuryTypeId, it.Description }).Select(it => new IncidentProperty<InjuryTypes>
                            {
                                Key = (InjuryTypes)it.Key.InjuryTypeId,
                                Count = it.Count()
                            }),
                            DayOfWeek = incidentsByMonth.GroupBy(ibm => SqlFunctions.DatePart("weekday", ibm.IncidentDate)).Select(ibm => new Aggregate<DayOfWeek> { Key = (DayOfWeek)ibm.Key.Value, Count = ibm.Count() }),
                            IncidentsByHour = incidentsByMonth.GroupBy(ibm => ibm.IncidentDate.Hour).Select(h => new Aggregate<int> { Key = h.Key, Count = h.Count() }),
                            PotentialIncurredMedian = incidentsByMonth.Select(x => x.PotentialIncurredMedian * .4).DefaultIfEmpty(0).Sum(),
                        };

Profiling different components, it looks like the IncidentTypes and InjuryTypes properties account for about 6 seconds of the 14 seconds totals. I've tried rewriting it a few different ways, but all seem to be worse in terms of performance.

I've started writing the query in native T-SQL, but I think it's a bit complex for my abilities right now.

Any suggestions would be greatly appreciated.