Solved

LINQ Subquery

Posted on 2012-03-12
3
627 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

705 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