Optimise LINQ query

Question

I just want to optimise my LINQ, I couldn't figure out how to optimise it or do it better way... Basically I have got a client and incident tables.

Incident has a 3 status, NEW, VERIFIED and COMPLETED. I just want to get a list of each clients incidents with the number of each incident status.

Incident Pogress number if it is 0, it means a NEW incident, if it is 1 a VERIFIED and 2 if it is COMPLETED

This is my table

    IncidentID ClientID    IncidentProgress
    1             1             0
    2             1             0
    3             1             0
    4             1             1
    5             1             1
    6             1             2
    7             2             0
    8             2             1
    9             2             2
    10            2             2

What I need

    ClientID    total    New Confirmed  Completed
       1           6      3     2         1       
       2           4      1     1         2

I tried this`
First Group BY Each Client

            List<ReportIncidentList> list = (from incident in incidentRepository.IncidentModels
                                         join client in clientRepository.ClientModel on incident.ClientID equals client.ClientID
                                         where client.ClientStatus == true && incident.IncidentStatus == true
                                         group incident by new { client.ClientID, client.ClientName, incident.IncidentProgress } into newGroup
                                         orderby newGroup.Key.ClientID                     

                   select new ReportIncidentList
                   {
                       ClientID = newGroup.Key.ClientID,
                       ClientName = newGroup.Key.ClientName,
                       NumberOfIncidents = newGroup.Count(),
                       NewT = newGroup.Where(x=>x.IncidentProgress == Models.IncidentProgressStatus.New && x.ClientID == newGroup.Key.ClientID).Count(),
                       Completed = newGroup.Where(x => x.IncidentProgress == Models.IncidentProgressStatus.Completed && x.ClientID == newGroup.Key.ClientID).Count(),
                       Confirmed = newGroup.Where(x => x.IncidentProgress == Models.IncidentProgressStatus.Confirmed && x.ClientID == newGroup.Key.ClientID).Count(),
                       IncidentProgress =  newGroup.Key.IncidentProgress
                   }).ToList();

Then Group again

            List<ReportIncidentList> list2 = (from client in list
                                         group client by new { client.ClientID, client.ClientName } into newGroup
                                         orderby newGroup.Key.ClientID
                                         select new ReportIncidentList
                                         {
                                             ClientID = newGroup.Key.ClientID,
                                             ClientName = newGroup.Key.ClientName,
                                             NumberOfIncidents = list.Where(c=>c.ClientID==newGroup.Key.ClientID).Sum(s=>s.NumberOfIncidents),
                                             NewT = list.Where(x => x.IncidentProgress == Models.IncidentProgressStatus.New && x.ClientID == newGroup.Key.ClientID).Select(x=>x.NewT).SingleOrDefault(),
                                             Confirmed = list.Where(x => x.IncidentProgress == Models.IncidentProgressStatus.Confirmed && x.ClientID == newGroup.Key.ClientID).Select(x => x.Confirmed).SingleOrDefault(),
                                             Completed = list.Where(x => x.IncidentProgress == Models.IncidentProgressStatus.Completed && x.ClientID == newGroup.Key.ClientID).Select(x => x.Completed).SingleOrDefault(),
                                         }).ToList();

Answer 1

What about keeping things simple?

We can just query list of incidents as is and merge data in a single loop then.

var incidents = incidentRepository.IncidentModels.ToList();

var reportsIncidentList = new Dictionary<int,ReportIncidentList>();
foreach (var incident in incidents)
{
    if (!reportsIncidentList.ContainsKey(incident.ClientID))
        reportsIncidentList.Add(incident.ClientID, new ReportIncidentList(){ClientID = incident.ClientID});

    reportsIncidentList[incident.ClientID].Total++;

    switch (incident.IncidentProcess)
    {
        case 0:
            reportsIncidentList[incident.ClientID].New++;
            break;
        case 1:
            reportsIncidentList[incident.ClientID].Confirmed++;
            break;
        case 2:
            reportsIncidentList[incident.ClientID].Completed++;
            break;
    }
}

var result = reportsIncidentList.Values.ToList();

Answer 2

Here is the linq query that will get you the results:

var results = data
    .GroupBy(x => x.ClientID)
    .Select(g => new {
        ClientID = g.Key, 
        Total = g.Count(), 
        New = g.Count(i => i.IncidentProgress == 0), 
        Confirmed = g.Count(i => i.IncidentProgress == 1), 
        Verified = g.Count(i => i.IncidentProgress == 2)});