Linq Group By multiple fields and Flatten the list

Question

I've got the following data

title | useful

ttitle1 | Yes

ttitle1 | Yes

ttitle1 | No

ttitle2 | Yes

I would like to group the above data and flatten it so I get the following result:

Title | Useful Count | Not Useful Count

tttitle1 | 2 | 1

tttitle2 | 1 | 0

Tried this, but it does not produce the correct result:

 var query = (from r in ratings
                    group r by new { r.ArticleTitle, r.Useful } into results
                    group results by new { results.Key.ArticleTitle } into results2
                    from result in results2
                    select new
                    {
                        Title = result.Key.ArticleTitle,
                        Yes = result.Select(i => i.Useful).Count(),
                        No = result.Select(i => i.Useful == false).Count()
                    });

Any help?

Answer 1

It seems to me that the only problem is that you're grouping twice. I'd expect this to work:

var query = from rating in ratings
            group rating by rating.ArticleTitle into g
            select new
            {
                Title = g.Key,
                Yes = g.Count(r => r.Useful),
                No = g.Count(r => !r.Useful)
            };

Or not in query expression form:

var query = ratings.GroupBy(r => r.ArticleTitle,
                            (key, rs) => new
                            {
                                Title = key,
                                Yes = rs.Count(r => r.Useful),
                                No = rs.Count(r => !r.Useful)
                            });

Answer 2

You don't need to group twice to get the desired result. One Grouping would be fine:

var query = (from r in ratings
             group r by new { r.ArticleTitle } into g
             from result in groups
             select new
             {
                 Title = result.Key,
                 Yes = result.Select(i => i.Useful).Count(),
                 No = result.Select(i => !i.Useful).Count()
             });