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 IDs 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. 
EF query for calculating monthly trends
performance;sql;linq;entity framework;sql server