CODEWITHSUNDEEP
sqllinq
user17753
user17753

Reputation: 3161

Multi-Group Column Count 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?

Upvotes: 25

Views: 40464

Answers (1)

NinjaNye
NinjaNye

Reputation: 7126

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

Upvotes: 67

Related Questions