Solved

LINQ Subquery

Posted on 2012-03-12
3
619 Views
Last Modified: 2012-06-27
I have an EDI feed to parse.  I have pasted the code so far below with my comment in

 Dim path As String = "\\Thefiles\"

        'Create a new directory info object.
        Dim di As New DirectoryInfo(path)
        'Within the directory info object get a list of all files that have the extension *.arc
        Dim files As FileSystemInfo() = di.GetFileSystemInfos("*.arc")
        'Using Linq order the list of files by their creation time property
        Dim orderedFiles = files.OrderBy(Function(f) f.CreationTime)

        'used to double check the number of rows added to the database
        Dim i As Integer

        'Open a connection to the database
        Using db As New DatabaseLibraries.Datasets("DEV", "Dev201201ABC")
           
            'Run throught the resultant Linq dataset
            For Each item In orderedFiles
                 'Filehelper is an opensource project that helps to parse delimited files
                Dim oEngine As FileHelpers.FileHelperEngine = New FileHelpers.FileHelperEngine(GetType(UPSMapping))
                Dim oMappings As UPSMapping() = CType(oEngine.ReadFile(item.FullName), UPSMapping())
                'For each file found open it and convert it to the file definition USPMapping
                'Imports FileHelpers is required to enable this is the URL http://www.filehelpers.com/quick_start.html

'The file to be processed has 3 imporant columns.  Our Ref, The suppliers Ref,
'and the record type being processed.  There are several rules in play with the file.  When a line is a record type G1 it will have a type M2.  This M2 is a superceeded record and needs to be excluded as does it G1 partner.  A M2 record on its own needs to be processed.  The approach I have tried to use is to create a list G1 records and then use that list to filter the
end result by it, much as I might do in SQL.  I am struggling to get the right syntax in VB.  I would really prefer the answer as VB.  My lack of familiarity of LINQ is the key factor here.

Example of the file in short
Record Type      Supplier Ref                                Our Ref
G1                        1Y8V938634139563            A200594371
M2                        1Y8V938634139563            A200594371
M2                        1Y8V938634331765            A200594371
G1                        1Y8V938634127974            A200594371
M2                        1Y8V938634127974            A200594371
M2                        1Y8V938634528951            A200594371



                Dim G1Query = From oMap In oMappings
                            Where oMap.mDocumentType = "G1"
                            Select oMap
                           
                Dim Rc = From oMap In oMappings
                         Join G1 In G1Query On G1.mSuppRef Equals oMap.mSupRef
0
Comment
Question by:Alyanto
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
ID: 37709021
These queries should get the M2's that have no partnering G1's
        Dim G1Query = (From oMap In oMappings
                            Where oMap.mDocumentType = "G1"
                            Select oMap.mSupRef).ToList.Distinct

        Dim Rc = From oMap In oMappings
                 Where oMap.mDocumentType = "M2" AndAlso Not G1Query.contains(oMap.mSupRef)
                 Select oMap

Open in new window

0
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 37709341
Thank you Sir Spot-on answer.

if you happen to know of a really good reference site other than that provided in msdn I would be grateful.  I am a little new to Linq and any help is welcome
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37709399
Sorry I have not got a reference site to recomend (just looked through my bookmarks and none is LINQ related!) but your best bet is Google.
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question