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

Can you please provide the compiler error you get? – Simon Whitehead Oct 10 '13 at 01:10 — Simon Whitehead, Oct 10 '13 at 01:10

p.s.w.g · Accepted Answer · 2013-10-10T01:22:29.830

57

There are two issues here:

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.
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() }

edited Oct 10 '13 at 01:22

answered Oct 10 '13 at 01:11

p.s.w.g

146,324
30
291
331

Thanks! So it would be g.Key.Field (In my case, STATE) . Correct? – N8K8 Oct 10 '13 at 01:21
@user2865100 I'm a little confused as to whether you want to group by `State` or `StateId`. Are they supposed to be the same field (just a typo)? If not, how do they relate to each other? – p.s.w.g Oct 10 '13 at 01:24
I actually masked it a little bit from my original code so most likely a typo. Sorry about that. State is a description, I need to get the count of each description for all of those that match the Where clause. I need this in order to "feed" a PieChart using this : http://blog.platformular.com/2012/03/20/load-google-chart-by-ajax-using-asp-net-mvc-and-jquery/. This code is in my repository, and it does compile now with your help. thanks! I am now stuck at controller, but I need to take a look at it now that i've fixed this. – N8K8 Oct 10 '13 at 01:30
@user2865100 Well, glad I could help. Happy coding :) – p.s.w.g Oct 10 '13 at 01:41

score 3 · Answer 2 · answered Mar 25 '16 at 07:44

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() });
        }

Group by, Count and Lambda Expression

2 Answers2

Linked