Linq query with count (extension method)

Question

I expect a very simple solution, but I can't for the life of me figure this out...

I am trying to create the LINQ equivalent of this:

SELECT Group, COUNT(*) as GroupCount
FROM table1
WHERE Valid > 0
GROUP BY Group, Project

I have this so far:

var model = _db.table1
    .Where(r => r.Valid > 0)
    .GroupBy(r => new { r.GROUP, r.Project})
    .Select(r => new{ r.GROUP, GroupCount = r.count()};

What is wrong with my query? Visual studio throws and error stating that:

System.Linq.IGrouping' does not contain a definition for 'GROUP' and no extension method 'GROUP' accepting a first argument of type 'System.Linq.IGrouping' could be found (are you missing a using directive or an assembly reference?)

EDIT: Just a note: The above error is given for r.Group in the Select clause.

It also throws an error stating that the count extension method doesn't exist, but I've seen other examples done this way. Maybe I was looking at an example from an old version of LINQ?

EDIT2: Some example data

GroupName    ProjectID    Step    Other Info...
-----------------------------------------------
GroupA     |     1      |   1   | ..............
GroupA     |     1      |   2   |..............
GroupA     |     3      |   1   | ..............
GroupB     |     4      |   1   | ..............
GroupB     |     5      |   1   | ..............
GroupC     |     6      |   1   |..............

Desired result:

 GroupName    Group Count
--------------------------- 
GroupA      |     2
GroupB      |     2
GroupC      |     1

You probably want to select the key in the group: `.Select(r => new{ r.GROUP, GroupCount = r.count()};` should probably be `.Select(r => new{ r.Key, GroupCount = r.Count()};` — Mario S, Dec 12 '12 at 18:44
I did not know about the `Key` keyword. It seems like that creates a key based on the items in the `GroupBy` clause. Are these values concatenated? What if I really only want to show `Group`? Or is it a requirement to include `Key` in queries that use aggregate functions? — Jeff, Dec 12 '12 at 18:50
Well, you can use `r.Key.Group` if you only want to show `Group`. See my answer =) — Mario S, Dec 12 '12 at 18:56

Mario S · Accepted Answer · 2012-12-12T20:11:18.417

2

Well, you have following errors in the .Select statement:

.count should be .Count (C# is case sensitive)
r.GROUP should be r.Key or r.Key.GROUP (.GroupBy() returns an IGrouping<TKey, TElement>)
You forgot the last parenthesis in the Select method. (But maybe that was just a typo in the example)

Result:

var model = _db.table1
    .Where(r => r.Valid > 0)
    .GroupBy(r => new { r.GROUP, r.Project })
    .Select(r => new { r.Key.GROUP, GroupCount = r.Count() });

UPDATE:

After the comments and question update; it looks like you are only grouping by group, so that would turn in to something like this to yield the output you requested:

var model = _db.table1
    .Where(r => r.Valid > 0)
    .GroupBy(r => new { r.GROUP }) // First, group only on GROUP
    .Select(r => new 
    { 
        r.Key.GROUP, 
        // Second, group on unique ProjectId's
        GroupCount = r.GroupBy(g => g.Project).Count()
    });

Or simplified:

var model = _db.table1
    .Where(r => r.Valid > 0)
    .GroupBy(r => r.GROUP) // First, group only on GROUP
    .Select(r => new 
    { 
        Group = r.Key, 
        // Second, group on unique ProjectId's
        GroupCount = r.GroupBy(g => g.Project).Count() 
    });

As you asked in the comments, yes, you could say that the values are concatenated in the GroupBy clause. So new { r.GROUP, r.Project } would group all pairs with the same value.

edited Dec 12 '12 at 20:11

answered Dec 12 '12 at 18:53

Mario S

11,715
24
39
47

This works, but seems to yield unexpected results. My intent is to gather the number of unique `Project` assigned to each unique `Group`. My `GroupCount` ends up being 1 for each row. I wonder if it has something to do with `r.Key.Group`... EDIT: Looks like I should be using `r.Project.Count()` or `r.Key,Project.Count()`. – Jeff Dec 12 '12 at 19:17
@Jeff I have tested with some mock-up data and it works for me, but maybe I am misunderstanding you. Can you give an example of the data you have and what the output should be? – Mario S Dec 12 '12 at 19:23
I have added example data in the question. – Jeff Dec 12 '12 at 19:34
@Jeff I have updated my answer, this will yield the output you have given. – Mario S Dec 12 '12 at 19:43
I apologize as I submitted before I was able to add the Step column. Now you see, there can be multiple entries of a particular `ProjectID`, so it is necessary to also group by `ProJectID`. – Jeff Dec 12 '12 at 19:49
@Jeff Ok, but the desired result makes no sense right now... there should be 4 rows in the result, right? And even with 4 rows, do you really get that result in your SQL query? – Mario S Dec 12 '12 at 19:52
`GroupCount` should be the number of UNIQUE `ProjectID`. There should still be 3 rows as there are 3 unique `GroupName`. – Jeff Dec 12 '12 at 19:59
@Jeff I think I understand what you want, check the updated answer. – Mario S Dec 12 '12 at 20:11

Dennis Traub · Answer 2 · 2012-12-12T18:50:14.440

1

LINQ queries are case-sensitive. You're trying to access r.GROUP, which probably doesn't exist. Use r.Group instead. Also change r.count() to r.Count(). Here's the corrected code:

var model = _db.table1
    .Where(r => r.Valid > 0)
    .GroupBy(r => new { r.Group, r.Project})
    .Select(r => new{ r.Key, GroupCount = r.Count()};

edited Dec 12 '12 at 18:50

answered Dec 12 '12 at 18:44

Dennis Traub

50,557
7
93
108

@IronMan84 Doesn't it? Which one do you mean? I guess that if he replaces the references to `r.GROUP` with `r.Group` as I did in my example it will probably solve OP's problem – Dennis Traub Dec 12 '12 at 18:46
His group error is not because of case sensitivity. It's because he needs to use r.Key. – Corey Adler Dec 12 '12 at 18:48
@IronMan84 oh, yes you're right. I missed that one. Thanks for pointing it out. – Dennis Traub Dec 12 '12 at 18:50

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

When you're doing the Group By, you're creating a new anonymous type (not of the type you think it is). Thus, when you're doing the select statement, it has no idea what the heck GROUP is.

To mitigate this, follow the 2nd answer located here.

edited May 23 '17 at 12:07

Community

1
1

answered Dec 12 '12 at 18:46

Corey Adler

15,897
18
66
80

Linq query with count (extension method)

3 Answers3