EF query for calculating monthly trends

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 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
{
Key = (IncidentTypes)it.Key.IncidentTypeId,

Native T-SQL

I don't know enough about linq to do a proper code review for that, however as far as performance is concerned I think T-SQL would take care of the expensive part of your query more efficiently.

The following query will not get you all the information from your entire query, but it would take care of at least this section:

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)),

Note that I made assumptions about your schema design and you may need to make some adjustments. Also please note that I used more descriptive table aliases as well to make it easier for Mr. Maintainer.

DECLARE @dateStart DATE = '2014-01-01'; -- or whichever date you want
DECLARE @dateEnd DATE = DATEADD(MONTH, 6, @dateStart); -- 6 months later e.g. July 1 2014
SELECT     
    DATEPART(yyyy, incd.date) AS Year,
    DATEPART(mm, incd.date) AS Month,
    COUNT(incd.IncidentId) AS AllIncidents,
    COUNT(incd.IsSerious) AS Flagged,
    (SELECT COUNT incd.IncidentId
        FROM incd
        WHERE treat.TreatmentType = 'SenttoHospital') AS SentToHospital,
    (SELECT COUNT incd.IncidentId
        FROM incd 
        WHERE incdType = 'Fall' 
        AND injuType.InjuryType <> 'None') AS FallsWithInjuries,
    (SELECT COUNT incd.IncidentId
        FROM incd 
        WHERE incdType = 'Fall' 
        AND injuType.InjuryType = 'Fracture') AS FallsWithFracture,
FROM Incidents AS incd
LEFT JOIN IncidentTypes AS incdType ON inc.IncidenTypeId = incdType.IncidentTypeId
LEFT JOIN InjuryTypes AS injuType ON inc.InjuryTypeId = injuType.InjuryTypeId
LEFT JOIN Treatments AS treat ON inc.TreatmentTypeId = treat.TreatmentTypeId
WHERE incd.date >= @dateStart
AND incd.date < @dateEnd;

StackExchange Code Review Q#54749, answer score: 3