better way of linq multiple joins?

Question

Hello,

Here is what I am trying to do:

I have three tables, manifest, details and billingJournal

for each manifest there are details and may be a billingJournal entry or not, if there is and it has the field recordType = 1 then billingJournal.amount is the billing amount and I need it in my grid also.

I currently am doing a two step process:

1. Using a normal join to pull together a manifest and its detail, I am returning a simple datagrid view with the attached code based on a user selected daterange.

2. I can then step through the datagrid with a loop. I set the loop to check each row of the datagrid, read the manifest number then perform a linq lookup in 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 datagrids current row. IE after looking up each manifest in the billingJournal, if there is a matching manifest with a record_type of 1 (A billing amount) then I can do a simple replace like:

BillingReportDataGrid("amount", 1).Value = queryResult

What I would like to know, is there a way to do all this in my main linq query? Or is there simply a better way of doing it than the way I am going about it?

I am using a class called reportData so I can access the columns by name.

I have tried to play around with left joins but can't quite 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}

Answer 1

What it sounds like you want is basically an outer join to your billingJournal table. What you are after is an operator called DefaultIfEmpty. I can't post links, but if you search for 'Simulating an Outer Join LINQ' on Google, you should come across an example pretty quickly (the hookedonlinq one is pretty clearly layed out).

Answer 2

Here is a sample of what Rick is talking about with simulating an outer join in LINQ. My VB.Net is rusty but I know this works in C# very nicely :)

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}

Answer 3

Solution: Using Let to assign a variable along with a enbeded if statement seems to do the trick (Thanks to Fernado from Experts 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}