how to perform a linq join on multiple tables...

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, look up each manifest in the billingJournal and 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}

Open in new window

Joe RuderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi joeyruder;

You should be able to do what you need with one query. If you can upload a test database to the site I will attempt to develop a query to do what you need.

Fernando
0
Joe RuderAuthor Commented:
Thank you Fernando;

I will attempt to get what you need together.

Best regards,

Joe
0
Fernando SotoRetiredCommented:
OK, have a great day.  ;=)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Joe RuderAuthor Commented:
Hello,

I have attached a zip file of a sample database with 3 records in it - 2 have amounts in the billing database.

The attached code snippet works - what I am trying to do is eliminate the for-next loop - any ideas?

Thanks in advance,

Joe

 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim db As New mydataDataContext
 
        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 {.olderDataExists = Nothing, .Amount = Nothing, .ManifestID = mainfest.ManifestID, .InboundDate = detail.InboundDate}
 
        BillingReportDataGrid.DataSource = query
        Dim rowManifest As String
        Dim rowAmount As Decimal
 
        For currentRow = 0 To (BillingReportDataGrid.RowCount - 1)
            rowManifest = BillingReportDataGrid("ManifestID", currentRow).Value
 
            Dim search = From b In db.Billings _
                         Where b.ManifestID = rowManifest _
                         Order By b.RecordCreationDate Descending _
                         Select b
            Try
                rowAmount = search.First.Amount
                BillingReportDataGrid("Amount", currentRow).Value = rowAmount
 
            Catch ex As System.Exception
                BillingReportDataGrid("Amount", currentRow).Value = Convert.ToDecimal(0)
            End Try
        Next
 
    End Sub
End Class
Public Class reportData
 
    Private _InboundDate
    Private _ManifestID
    Private _Amount
    Private _olderDataExists
 
    Public Property olderDataExists() As Boolean
        Get
            Return _olderDataExists
        End Get
        Set(ByVal value As Boolean)
            _olderDataExists = value
        End Set
    End Property
 
 
    Public Property ManifestID() As String
        Get
            Return _ManifestID
        End Get
        Set(ByVal value As String)
            _ManifestID = value
        End Set
    End Property
 
    Public Property Amount() As Decimal
        Get
            Return _Amount
        End Get
        Set(ByVal value As Decimal)
            _Amount = value
        End Set
    End Property
 
    Public Property InboundDate() As Date
        Get
            Return _InboundDate
        End Get
        Set(ByVal value As Date)
            _InboundDate = value
        End Set
    End Property
 
End Class

Open in new window

package-test-log.zip
0
Fernando SotoRetiredCommented:
Hi Joe;

You can do the same thing with one query, see the code snippet.

Fernando
Dim db As New mydataDataContext
 
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, BillingQuery, 0.0) _
           Select New reportData With {.olderDataExists = Nothing, _
                                       .Amount = Decimal.Parse(BillingAmount), _
                                       .ManifestID = mainfest.ManifestID, _
                                       .InboundDate = detail.InboundDate}
 
BillingReportDataGrid.DataSource = query

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
Have you tried the solution?
0
Joe RuderAuthor Commented:
Hello,

I have been on the road since 5:30 this morning - I just got an internet connection and connected back to check my email etc... - I will try it out when I hit a hotel this evening...

Thank you for your time --

Joe
0
Fernando SotoRetiredCommented:
Not a problem, have a good evening  ;=)
0
Joe RuderAuthor Commented:
Hello,

On the line Let BillingAmount = if(BillingQuery...

I get the following error:
Error      1      Cannot infer a common type for the second and third operands of the 'If' operator. One must have a widening conversion to the other.

I am sorry, you are over my head here - I am not sure what the let statements are even doing -- I am going to do some research on range varaiables (Is that what this is?)  -- I may be a little smarter in an hour or so!

:)

--Joe
0
Joe RuderAuthor Commented:
Hello,

the following query *seems* to work except I cannot get it to round to only 2 decimals points (for money)
  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.ToDouble(0)) _
                Select New reportData With {.olderDataExists = Nothing, _
                                            .Amount = Math.Round(Convert.ToDecimal(BillingAmount), 1, MidpointRounding.AwayFromZero), _
                                            .ManifestID = mainfest.ManifestID, _
                                            .InboundDate = detail.InboundDate}

Does this look correct?
0
Joe RuderAuthor Commented:
Alright,

It looks like this is working:

  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.ToDouble(0)) _
                Select New reportData With {.olderDataExists = Nothing, _
                                            .Amount = Math.Round(Convert.ToDecimal(BillingAmount), 1, MidpointRounding.AwayFromZero), _
                                            .ManifestID = mainfest.ManifestID, _
                                            .InboundDate = detail.InboundDate}

Then to get it to 2 decimals places changed my property to:
 Public Property Amount() As Decimal
        Get
            Return Math.Round(_Amount, 2)
        End Get
        Set(ByVal value As Decimal)
            _Amount = value
        End Set
    End Property

Is this the correct way to solve this?

Thanks,

Joe
0
Joe RuderAuthor Commented:
Sorry....my cut and paste did not work correctly -- this is the code I am using....      

  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}
0
Fernando SotoRetiredCommented:
Hi joeyruder;

Yes, what you did is fine. The let keyword assigns a local variable within the query allowing you to make other queries and calculations.

Fernando
0
Joe RuderAuthor Commented:
Thanks for the help (again) -- everytime I come here with a problem I leave with a more complete understanding of .net!
0
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.