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?

Improve company productivity with a Business Account.Sign Up

x
 
nepaluzConnect With a Mentor Commented:
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
 
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
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.