asked on

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

Fernando Soto

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

Joe Ruder

ASKER

Thank you Fernando;

I will attempt to get what you need together.

Best regards,

Joe

Fernando Soto

OK, have a great day. ;=)

Joe Ruder

ASKER

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

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernando Soto

Have you tried the solution?

Joe Ruder

ASKER

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

Fernando Soto

Not a problem, have a good evening ;=)

Joe Ruder

ASKER

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

Joe Ruder

ASKER

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?

Joe Ruder

ASKER

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

Joe Ruder

ASKER

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}

Fernando Soto

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

Joe Ruder

ASKER

Thanks for the help (again) -- everytime I come here with a problem I leave with a more complete understanding of .net!

Fernando Soto

Not a problem, glad I was able to help. ;=)