Alyanto
asked on
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("*.a rc")
'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.FileHelperEngi ne = New FileHelpers.FileHelperEngi ne(GetType (UPSMappin g))
Dim oMappings As UPSMapping() = CType(oEngine.ReadFile(ite m.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
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("*.a
'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
'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.FileHelperEngi
Dim oMappings As UPSMapping() = CType(oEngine.ReadFile(ite
'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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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