[RESOLVED] LINQ Troubles...

I'm trying to do a LINQ Query against a typed datatable that will return me duplicated data.

With out going into too much detail, here's the basic setup:

strCode1 - string type
intCode1 - int32 type
intAdjustor - int32

There are more fields but these are the ones that are important.

I'm going to do my best to describe this, as it's kind of confusing.

In theory intCode1 is a key for strCode1 (hence the names). So each time intCode1 is duplicated, the same strCode1 should appear.
Example:

Code:

---

strCode1  intCode1  intAdjustor
12345      100          0
12345      100          0
12345      100          0
12345      100          0
54321      254          0
54321      254          0
54321      254          0

---

At the same time, there can be adjustments on the data... like so

Code:

---

strCode1  intCode1  intAdjustor
12345      100          0
12345      100          0
12345      100          0
12345      100          1
12345      100          2
12345      100          3
54321      254          0
54321      254          0
54321      254          5

---

But occasionally we will get the same strCode1, but for a DIFFERENT intCode1.

Code:

---

strCode1  intCode1  intAdjustor
12345      100          0
12345      100          0
12345      100          0
12345      100          1
12345      100          2
12345      100          3
12345      500          0
54321      254          0
54321      254          0
54321      254          5
54321      750          0

---

This causes a problem. The source of the data is an external system. Fortunately the point at which this causes a problem, is as it's coming in from this other system. What I'm trying to do is write a LINQ query, that will tell me when this situation occurs, so that we can warn the user, and then can then go to that external system, correct the data and perform the action again (it is for a conversion process that pulls out of one system and sends it into another.)

Ultimately I'd like to get a List(Of T) - where T is my typed data row.

In SQL, I would simply use a sub query, to get a distinct list of strCode1 and intCode1, then do a count, grouped by strCode1 where I get more than one row.

I'm not sure how to translate that into LINQ, AND get the original types data rows returned in a list.

-tg

What exactly do you want to see in the results? Are you wanting to see all the rows that have a strCode1 & intCode1 that is invalid?

For example, if you had:

Code:

---

strCode1  intCode1  intAdjustor
12345      100          0
12345      100          2
13353      750          0
20347      100          0
20347      101          0
23434      100          1
35351      500          3
54321      254          0
54321      254          5
54321      751          0
61234      100          0
61236      254          0
62000      100          0

---

You would expect to see:

Code:

---

strCode1  intCode1  intAdjustor
20347      100          0
20347      101          0
54321      254          0
54321      254          5
54321      751          0

---

in the results?

correct... that's exactly what I'd like to see.

ideally, if this were SQL, and I were to SELECT DISTINCT strCode1, intCode1 ... I would only get one row... it's when strCode1 repeats, for different intCode1 that I need to look for. If I could do it in SQL, I would, but this is part of a validation delegate that fires off on the screen, so that we can mark the invalid rows in the grid appropriately. I'm sure when the solution presents itself, it'll be obvious, but right now, I just can't wrap my head around it. wouldn't surprise me to realize later that I'm approaching it from the wrong angle.

-tg

Alright, so I made typed dataset with 1 table that had 3 columns: strCode1, intCode1, intAdjustor.

I'm not sure if the query can be simplified, but this is what I got:

vb.net Code:

Public Class Form1
 
    Public Sub New()
        Me.InitializeComponent()
 
        Dim typedTable As New DataSet1.TypedTableDataTable()
 
        With typedTable.Rows
            .Add("12345", 100, 0)
            .Add("12345", 101, 0)
            .Add("61234", 100, 0)
            .Add("62000", 100, 0)
            .Add("23434", 100, 1)
            .Add("12345", 100, 2)
            .Add("35351", 500, 3)
            .Add("20347", 100, 0)
            .Add("20347", 100, 0)
            .Add("20347", 101, 0)
            .Add("54321", 254, 0)
            .Add("61236", 254, 0)
            .Add("54321", 254, 5)
            .Add("13353", 750, 0)
            .Add("54321", 751, 0)
        End With
 
        Dim results = typedTable.AsEnumerable().Where(Function(tRow) typedTable.AsEnumerable().GroupBy(Function(tRow2) New With {Key tRow2.strCode1, Key tRow2.intCode1}) _
                                                                                              .Select(Function(grouping) New With {.strCode1 = grouping.First().strCode1, _
                                                                                                                                   .intCode1 = grouping.First().intCode1, _
                                                                                                                                   .Results = grouping.Select(Function(tRow3) tRow3)}) _
                                                                                              .GroupBy(Function(aType) aType.strCode1) _
                                                                                              .Where(Function(grouping2) grouping2.Count() > 1) _
                                                                                              .Select(Function(aType2) aType2.First().strCode1).Contains(tRow.strCode1)).ToList()
 
        Me.DataGridView1.DataSource = results
 
    End Sub
 
End Class

Let me know if that works...

hmmmm.... I think I follow what you did there... Let me take it around for a spin and see what happens...

-tg

I just noticed you don't actually need the .Results property in that anonymous type (that's from a different approach), I forgot to remove it.

Edit: ugh now that I look at it again it could look like this:

vb.net Code:

Dim results = typedTable.AsEnumerable().Where(Function(tRow) typedTable.AsEnumerable().GroupBy(Function(tRow2) New With {Key tRow2.strCode1, _
                                                                                                                         Key tRow2.intCode1}) _
                                                                                      .Select(Function(grouping) grouping.First()) _
                                                                                      .GroupBy(Function(tRow3) tRow3.strCode1) _
                                                                                      .Where(Function(grouping2) grouping2.Count() > 1) _
                                                                                      .Select(Function(grouping3) grouping3.First().strCode1).Contains(tRow.strCode1)).ToList()

Dude! You Rock! Both worked, the first one was closer to what I was thinking, but the second one is cleaner, so I went with that.

-tg

hehehe.... I guess I should mark it resolved. :D

-tg