Using group by and count lambda expression

Question

I need to obtain the following output in a List , I am using MVC4 and C#. Basically the query I need to execute is :

SELECT ESTADO, COUNT(*)
FROM VISITAS
WHERE CICLOID=ID
GROUP BY ESTADO;

In order to achieve this I wrote the following procedure in my Repository:

  public List<object> PorcentajeVisitasCiclo(Guid id)
  {
      return new List<object> {_context.Visitas
          .Where(a => a.CicloId == id)
          .GroupBy(a => a.Estado)
          .Select(n => new { Text = n.Key.Descripcion , Value = n.Count() })};
  }

Could you point me where I am going wrong? It doesnt give any compilation error, however it doesnt return anything

Thankd in advance

Answer 1

This could be an option. I had same problem and returning a list of object wasnt a solution (some LINQ error i cant remember). I went for easier solution.

public List<DummyModel> Method(int id)
    {
        return _context.Visitas.Where(a => a.CicloId == id).GroupBy(a => a.Estado).
            Select(n => new DummyModel { Name = n.Key.Descripcion, Value = n.Count() }).ToList();
    }

Answer 2

You create a new List<object> containing one element, which is your LINQ query. That's probably not what you want.

This executes your LINQ query and then converts the result to a list:

return _context.Visitas.Where(a => a.CicloId == id)
    .GroupBy(a => a.Estado)
    .Select(n => new { Text = n.Key.Descripcion , Value = n.Count() }).ToList();

This, however, yields the problem that a List<yourAnonymousType> is not a subtype of List<object>. Hence, you need to cast your anonymous type to object first:

return _context.Visitas.Where(a => a.CicloId == id)
    .GroupBy(a => a.Estado)
    .Select(n => new { Text = n.Key.Descripcion , Value = n.Count() })
    .Cast<object>().ToList();

Of course, a better solution would be to drop the anonymous type and use an explicit class YourDataType with Text and Value fields/properties. Then you can simply return a List<YourDataType>.