Best way for multiple linq joins?
Here's what I'm trying to do:
I have three tables, manifest, details and billing log
there are details for each manifest and there may be a billing entry or not, if it does and it has a recordType = 1 field, then billingJournal.amount is the billing amount and I also need it in my grid.
I am currently doing a two step process:
-
Using a normal connection to assemble the manifest and its details, I return a simple datagrid view with attached code based on a user-selected option.
-
Then I can loop through the datagrid using a loop. I set up a loop to check each row of the datagrid, read the manifest number and then did a linq search on the billingJournal table. If there is a match, it reads the data from the billingJournal table and gets the value and stores it in the current datagrids row. IE after searching each manifest in the billing log, if there is a corresponding manifest with record_type of 1 (billing amount), then I can do a simple replacement like:
BillingReportDataGrid ("amount", 1) .Value = queryResult
What I would like to know is is there a way to do all this in my main linq query? Or is there just a better way to do it than the way I do it?
I am using a class called reportData so I can access columns by name.
I tried to play around with left joins but can't seem to get it.
Thanks -
- Joe
Dim query = From detail In db.details _
Where detail.InboundDate >= CType(MonthCalendar1.SelectionStart, DateTime) _
And detail.InboundDate <= CType(MonthCalendar1.SelectionEnd, DateTime).AddHours(23).AddMinutes(59) _
Join mainfest In db.Manifests On mainfest.ManifestID Equals detail.MainID _
Select New reportData With {.Amount = Nothing, .ManifestID = mainfest.ManifestID, .InboundDate = detail.InboundDate}
a source to share
Decision. Let's assign the variable along with the if statement seems to do the trick (thanks to Fernado from Expert Exchange for the helpful prod ....)
Dim query = From detail In db.details _
Where detail.InboundDate >= CType(MonthCalendar1.SelectionStart, DateTime) _
And detail.InboundDate <= CType(MonthCalendar1.SelectionEnd, DateTime).AddHours(23).AddMinutes(59) _
Join mainfest In db.Manifests On mainfest.ManifestID Equals detail.MainID _
Let BillingQuery = (From b In db.Billings _
Where b.ManifestID = mainfest.ManifestID _
Order By b.RecordCreationDate Descending _
Select b).First.Amount _
Let BillingAmount = If(BillingQuery IsNot Nothing, Convert.ToDecimal(BillingQuery), Convert.ToDecimal(0.0)) _
Select New reportData With {.olderDataExists = Nothing, _
.Amount = Convert.ToDecimal(BillingAmount), _
.ManifestID = mainfest.ManifestID, _
.InboundDate = detail.InboundDate}
a source to share
Here's an example of what Rick has to say with simulating an outer join in LINQ. My VB.Net is rusty, but I know it works very nicely in C # :)
Dim query = From detail In db.details _
Where detail.InboundDate >= CType(MonthCalendar1.SelectionStart, DateTime) _
And detail.InboundDate <= CType(MonthCalendar1.SelectionEnd, DateTime).AddHours(23).AddMinutes(59) _
Join mainfest In db.Manifests On mainfest.ManifestID Equals detail.MainID into mainfestjoin _
From submj in manifestjoin.DefaultIfEmpty()
Select New reportData With { _
.Amount = Nothing, _
.ManifestID = If submj <> Nothing Then mainfest.ManifestID Else 0 EndIf, _
.InboundDate = detail.InboundDate}
a source to share
It sounds like what you want is basically an outer join to your billing table. What you need is a statement called DefaultIfEmpty. I can't post links, but if you're looking for "Modeling LINQ outer join" on Google, you should come across an example pretty quickly (hookedonlinq one is laid out pretty clearly).
a source to share