How do I get distinct rows along with the count of identical rows in Linq?

Question

In SQL this would be easy for me...

SELECT browser, browser_version, page_name, COUNT(*) as cnt 
FROM traffic
GROUP BY browser, browser_version, page_name

This would return one row for each unique combination of browser, browser_version, page_name, along with the count of duplicates.

I'd like to do the same thing in Linq, but I'm not sure how to do it.

By the way, I'm not using SQL in this case because the data didn't come from SQL. It came from Windows Azure Table Storage. And I'm working with the data in-memory.

Possibly related: http://stackoverflow.com/questions/706866/linq-to-dataset-distinct-by-multiple-columns — M.Babcock, Feb 10 '12 at 23:31
And: http://stackoverflow.com/questions/1869001/linq-group-by-multiple-fields-syntax-help — k.m, Feb 10 '12 at 23:33
@M.Babcock - That topic only shows how to get distinct rows... it doesn't get a count. — Steve Wortham, Feb 10 '12 at 23:33
@Tim - Currently I'm working with the data in IEnumerable form because that's how it comes out of my DataSource classes by default. — Steve Wortham, Feb 10 '12 at 23:36

score 2 · Accepted Answer · answered Feb 10 '12 at 23:33

2

var query =
    from t in traffic
    group t by new { t.browser, t.browser_version, t.page_name } into g
    select new
    {
        g.Key.browser,
        g.Key.browser_version,
        g.Key.page_name,
        Count = g.Count()
    }

answered Feb 10 '12 at 23:33

Thomas Levesque

286,951
70
623
758

Thanks man. My problem was a little more complex than the example I gave. I also had to get the latest date for every grouping. But your solution was perfect and helped me figure it all out. – Steve Wortham Feb 11 '12 at 21:10

score 1 · Answer 2 · answered Feb 10 '12 at 23:31

You'd use a similar method:

var results = traffic.GroupBy(t => new { t.browser, t.browser_version, t.page_name });

foreach(var group in results)
{
    Console.WriteLine("{0}/{1}/{2} has {3} items.", 
            group.Key.browser, 
            group.Key.browser_version, 
            group.Key.page_name, 
            group.Count());
}

How do I get distinct rows along with the count of identical rows in Linq?

2 Answers2