Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multi-Group Column Count Linq

Tags:

sql

linq

I have,

A list, MyList, of objects with fields:

string A;
string B;

Conceptually, this is similar to a two column SQL Table with columns A, B.

I'm trying to create a linq expression that would produce the three column result set of this T-SQL on such a conceptual table:

SELECT A, B, COUNT(B) 
FROM T1
GROUP BY A, B

That is, if I had a table such as:

A       B
----------
x       g
x       g
x       g
x       s
y       g
y       g

I would expect:

A       B       COUNT(B)
-------------------------
x       g         3
x       s         1
y       g         2

My best efforts were this:

var result = from MyObjs in MyList
group MyObjs by new { MyObjs.A, MyObjs.B } into g
select new { g.Key.A, g.Key.B, g.Key.B.Count() }

But the count appears to return the total number of B's not the number of B's per multiple column group. How can this be fixed?

like image 705
user17753 Avatar asked Jul 03 '13 14:07

user17753


1 Answers

Try this.... (off the top of my head)

var result = from MyObjs in MyList
group MyObjs by new { MyObjs.A, MyObjs.B } into g
select new { g.Key.A, g.Key.B, MyCount = g.Count() }

Or if you prefer...

var result = MyList.GroupBy(x => new {x.A, x.B})
                   .Select(g => new {g.Key.A, g.Key.B, MyCount = g.Count()});
like image 80
NinjaNye Avatar answered Oct 11 '22 13:10

NinjaNye