Getting Count from Grouped DataTable in VB via Linq

Question

I'm running into a mental roadblock here and I'm hoping that I'm missing something obvious.

Anyway, assume I have a table that looks like this:

ID            LookupValue         SortOrder
============================================
1             A                   1000
2             B                   2000
3             B                   2000
4             C                   3000
5             C                   4000

I'm trying to find, using Linq, places where the LookupValue is the same, but the sort order is different (the ID is a PK on my Database table and is irrelevant to this exercise).

I thought the easiest way would be to group by the LookupValue and the SortOrder and then find places where the LookupValue appears more than twice in the result.

Right now, my code to get the grouped table looks like this:

Dim KeySortPairs = From d In MyDataTable _
      Group By Key = d(LookupValue).ToString(), SortOrder = d(SortOrder).ToString() _
      Into Group _
      Select Key, SortOrder

Looking in the debug output, the above code produces this result (which is correct):

Key    SortOrder
================
A      1000
B      2000
C      3000
C      4000

To get the duplicate Key's then, I'm looking through the results like this:

For Each Entry In KeySortPairs.Where(Function(t) t.Key.Count() > 1)
    'Multiple Sort Orders!!'
Next

In this code, however, every entry in the grouped result gets returned. Am I missing something, or shouldn't that count only give me the entries where the Key appears more than once? I assume I'm making a trivial mistake due to my low-level of comfort with VB.NET, but I can't figure it out -- I've tried moving the Count() into a WHERE clause on the Linq expression, but that gave me the same thing.

Answer 1

Key is a string.

Key.Count counts the characters in the string.

---

Change the select to include the group

Select Key, SortOrder, Group

and change the where clause to count the group

KeySortPairs.Where(Function(t) t.Group.Count() > 1)

Alternatively, counting the group might be overkill. "Any" can save time by doing a short enumeration.

KeySortPairs.Where(Function(t) t.Group.Skip(1).Any())

(syntax might not be perfect, I don't know vb.linq).

Answer 2

I think you need a groupby in your for each statement.

Try something like

For Each Entry in KeySortPairs.GroupBy(expression).Where(