Group by, Count and Lambda Expression

Question

I am trying to translate the following query:

SELECT STATE, COUNT(*) FROM MYTABLE GROUP BY STATE; 

Into a lambda expression. I am using C# and EntityFramework, however it doesnt seem I can make it work. Here is what I have on my respository so far:

public IEnumerable<object> PorcentajeState(Guid id) {     return _context.Sates.Where(a => a.Id == id)                          .GroupBy(a => a.State)                          .Select(n => new { n.StateId , n.Count() }); } 

Of course it doesnt compile and I am lost after googling for 2 hours . Could you please help me?

thanks in advance

Answer 1

There are two issues here:

1. The result of GroupBy will will be an enumerable of type IEnumerable<IGrouping<TKey, TSource>>. The IGrouping interface only has one property you can access, Key which is the key you specified in the GroupBy expression, and implements IEnumerable<T> so you can do other Linq operations on the result.
2. You need to specify a property name for the anonymous type if it cannot be inferred from a property or field expression. In this case, you're calling Count on the IGrouping, so you need to specify a name for that property.

Try this:

public IEnumerable<object> PorcentajeState(Guid id) {     return _context.Sates.Where(a => a.Id == id)                          .GroupBy(a => a.StateId)                          .Select(g => new { g.Key, Count = g.Count() }); } 

The equivalent in query syntax would be

public IEnumerable<object> PorcentajeState(Guid id) {     return from a in _context.Sates            where a.Id == id            group a by a.StateId into g            select new { a.Key, Count = g.Count() }; } 

In either case, if you want the first property to be named StateId instead of Key, just change that to

new { StateId = g.Key, Count = g.Count() } 

Answer 2

This one is good

public IEnumerable<object> PorcentajeState(Guid id)     {         return _context.Sates.Where(a => a.Id == id)                              .GroupBy(a => a.StateId)                              .Select(g => new { g.Key, Count = g.Count() });     } 

But try this.

public IEnumerable<object> PorcentajeState(Guid id)         {             return _context.Sates.Where(a => a.Id == id)                                  .GroupBy(a => a.StateId)                                  .Select(g => new { g.Key.StateId, Count = g.Count() });         }