Count how many id you have in the table based on other data

Question

Hello I’m trying to make a chart in aspnetcore using EF, I have a table Events in it contains a FK Idarea that determines the area of the event I wanted to count how many events you have of each Idarea, for example how many events you have in the IT area, The graph itself is functional the problem is in the logic that I am not able to understand! Any help is welcome!

Follow the attempts ! OBS.: Total Event is are the tests I did the SUM works the graph but of course it adds up what has inside the field so it is not the case! No build error but no information appears on the chart

public IEnumerable<GraficoViewModel> ListaGrafico(int IdArea, int IdEvento)
    {
        var lista = _contexto.Evento.Include(a => a.Area)
                  .GroupBy(x => x.Id)
                  .Select(y =>  new GraficoViewModel
                  {
                      IdEvento = y.First().Id,
                      Descricao = y.First().Area.Descricao,
                      //TotalEvento = y.Sum(a =>a.IdArea)
                      //TotalEvento = y.Count(c =>c.IdArea == IdArea )
                      TotalEvento = y.Where(c =>c.IdArea == IdArea ).Count()
                  }).ToList();


                  }).ToList();

        return lista;
    }

Answer 1

It is necessary to understand what makes the GroupBy. What you were doing was grouping the events by your id, which created a group of events with the same id. In this case, a group would be created for each event, since the id must be unique. By changing the GroupBy for idArea (I suppose that’s the property in Evento that identifies the event area), we create groups with events from the same area.

Next, we need to understand the outcome of GroupBy. What is obtained from this method are groups, each with the events of the same area. Thus, the Select is by such groups and not by events. Iterating through the groups, we can take the area of the 1st item of each group (since they all have the same area) to identify its name and do the Count of events in the group.

public IEnumerable<GraficoViewModel> ListaGrafico(int IdArea, int IdEvento) // IdEvento não é relevante para o seu método
{
    var lista = _contexto.Evento
                         .Include(a => a.Area)
                         .GroupBy(x => x.idArea)
                         .Select(y =>  new GraficoViewModel
                         {
                             Descricao = y.First().Area.Descricao,
                             TotalEvento = y.First().Count()
                         })
                         .ToList();

    return lista;
}