-
Jan 25th, 2010, 11:40 AM
#1
[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
-
Jan 25th, 2010, 01:47 PM
#2
Re: LINQ Troubles...
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?
-
Jan 25th, 2010, 01:55 PM
#3
Re: LINQ Troubles...
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
-
Jan 25th, 2010, 01:58 PM
#4
Re: LINQ Troubles...
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...
-
Jan 25th, 2010, 02:02 PM
#5
Re: LINQ Troubles...
hmmmm.... I think I follow what you did there... Let me take it around for a spin and see what happens...
-tg
-
Jan 25th, 2010, 02:06 PM
#6
Re: LINQ Troubles...
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()
Last edited by ForumAccount; Jan 25th, 2010 at 02:12 PM.
-
Jan 25th, 2010, 02:30 PM
#7
Re: LINQ Troubles...
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
-
Jan 25th, 2010, 02:38 PM
#8
Re: LINQ Troubles...
hehehe.... I guess I should mark it resolved.
-tg
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|