views:

2794

answers:

2

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.

+1  A: 

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).

David B
Ah, duh! That explains why t.Key.Count() > 7 (a test I was trying) was returning no results!
jerhinesmith
Also, counting the group doesn't seem to be working. Logically I'm not sure it makes sense either since by definition, the group by will only return one unique Key, SortOrder pair -- correct?
jerhinesmith
Groups in linq are hierarchical, unlike SQL. A group has a key and one or more child elements. These elements can be accessed by enumerating the group. http://msdn.microsoft.com/en-us/library/bb344977.aspx
David B
A: 

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

Try something like

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