Solved

LINQ Subquery

Posted on 2012-03-12
3
616 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

943 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now