LINQ Subquery

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
LVL 1
AlyantoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nepaluzCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlyantoAuthor Commented:
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
nepaluzCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.