Databases
StackOverflow2013
Postsdbo
PObetter way of linq multiple joins?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PObetter way of linq multiple joins?
    primarykey
    data
    text
    <p>Here is what I am trying to do:</p> <p>I have three tables, manifest, details and billingJournal</p> <p>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.</p> <p>I currently am doing a two step process:</p> <ol> <li><p>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.</p></li> <li><p>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:</p></li> </ol> <p>BillingReportDataGrid("amount", 1).Value = queryResult</p> <p>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?</p> <p>I am using a class called reportData so I can access the columns by name.</p> <p>I have tried to play around with left joins but can't quite seem to get it.</p> <p>Thanks --</p> <p>--Joe</p> <pre><code> Dim query = From detail In db.details _ Where detail.InboundDate &gt;= CType(MonthCalendar1.SelectionStart, DateTime) _ And detail.InboundDate &lt;= 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} </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJoe Ruder
    1. USJoe Ruder
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CO"can then step through the datagrid with a loop, look up each manifest in the billingJournal..." This doesn't make sense with respect to the query, as it's unclear how you get the billingjournal data / where it is, as your query doesn't touch it. You should better describe what your model is as your intro talks about 2 tables while there are clearly 3: details is also involved.
      singulars
      1. PObetter way of linq multiple joins?
      1. USFrans Bouma
    2. COThanks Frans - I have edited for additional content and clarity... Joe
      singulars
      1. PObetter way of linq multiple joins?
      1. USJoe Ruder
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload