Results 1 to 8 of 8

Thread: [RESOLVED] LINQ Troubles...

  1. #1

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Resolved [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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  2. #2
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    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?

  3. #3

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    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:
    1. Public Class Form1
    2.  
    3.     Public Sub New()
    4.         Me.InitializeComponent()
    5.  
    6.         Dim typedTable As New DataSet1.TypedTableDataTable()
    7.  
    8.         With typedTable.Rows
    9.             .Add("12345", 100, 0)
    10.             .Add("12345", 101, 0)
    11.             .Add("61234", 100, 0)
    12.             .Add("62000", 100, 0)
    13.             .Add("23434", 100, 1)
    14.             .Add("12345", 100, 2)
    15.             .Add("35351", 500, 3)
    16.             .Add("20347", 100, 0)
    17.             .Add("20347", 100, 0)
    18.             .Add("20347", 101, 0)
    19.             .Add("54321", 254, 0)
    20.             .Add("61236", 254, 0)
    21.             .Add("54321", 254, 5)
    22.             .Add("13353", 750, 0)
    23.             .Add("54321", 751, 0)
    24.         End With
    25.  
    26.         Dim results = typedTable.AsEnumerable().Where(Function(tRow) typedTable.AsEnumerable().GroupBy(Function(tRow2) New With {Key tRow2.strCode1, Key tRow2.intCode1}) _
    27.                                                                                               .Select(Function(grouping) New With {.strCode1 = grouping.First().strCode1, _
    28.                                                                                                                                    .intCode1 = grouping.First().intCode1, _
    29.                                                                                                                                    .Results = grouping.Select(Function(tRow3) tRow3)}) _
    30.                                                                                               .GroupBy(Function(aType) aType.strCode1) _
    31.                                                                                               .Where(Function(grouping2) grouping2.Count() > 1) _
    32.                                                                                               .Select(Function(aType2) aType2.First().strCode1).Contains(tRow.strCode1)).ToList()
    33.  
    34.         Me.DataGridView1.DataSource = results
    35.  
    36.     End Sub
    37.  
    38. End Class

    Let me know if that works...

  5. #5

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    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:
    1. Dim results = typedTable.AsEnumerable().Where(Function(tRow) typedTable.AsEnumerable().GroupBy(Function(tRow2) New With {Key tRow2.strCode1, _
    2.                                                                                                                          Key tRow2.intCode1}) _
    3.                                                                                       .Select(Function(grouping) grouping.First()) _
    4.                                                                                       .GroupBy(Function(tRow3) tRow3.strCode1) _
    5.                                                                                       .Where(Function(grouping2) grouping2.Count() > 1) _
    6.                                                                                       .Select(Function(grouping3) grouping3.First().strCode1).Contains(tRow.strCode1)).ToList()
    Last edited by ForumAccount; Jan 25th, 2010 at 02:12 PM.

  7. #7

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: LINQ Troubles...

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

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
  •  



Click Here to Expand Forum to Full Width