Group by, Count and Lambda Expression

c# entity-framework lambda linq

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

1
21
10/10/2013 1:18:02 AM

Accepted Answer

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() }
51
10/10/2013 1:22:29 AM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow